Home » SQL & PL/SQL » SQL & PL/SQL » merge statement problem (10g)
merge statement problem [message #657531] |
Sun, 13 November 2016 07:21 |
emadnabil
Messages: 177 Registered: August 2007
|
Senior Member |
|
|
[size=1]Dear all,
i have a problem in the next merge statment
MERGE INTO RA_CUSTOMER_DETAILS_ALL TR
USING EMP_TEMP2 HR
ON (TR.customer_id=hr.emp_id)
WHEN MATCHED THEN
UPDATE SET
TR.Customer_Name=hr.EMP_NAME,
tr.company=(select com_id from company where com_desc= [b][color=green]hr.company[/color][/b]),
tr.location=hr.REGION,
tr.date_to=hr.END_DATE,
tr.dept=hr.EMP_DEPT,
tr.job_desc=hr.JOB_DESC,
tr.birth_date=hr.BIRTH_DATE,
tr.job_date=hr.START_DATE,
tr.created_by=USERID,
tr.creation_date=hr.created_date,
tr.last_updated_by=USERID,
tr.last_updated_date=sysdate,
tr.process='Merge Update - '||SHEET_MONTH||' - '||DATE_VAL,
TR.MEDICAL_PROGRAM=HR.MEDICAL_PROGRAM,
TR.JOB_DGREE=HR.JOB_DGREE
WHEN NOT MATCHED THEN
INSERT (tr.customer_id,TR.CUSTOMER_NAME,tr.company,tr.location,tr.date_to,tr.dept,tr.job_desc,tr.birth_date,tr.job_date
,tr.created_by,tr.creation_date,tr.last_updateD_date,tr.process,TR.MEDICAL_PROGRAM,TR.JOB_DGREE)
VALUES
(hr.EMP_ID,hr.EMP_NAME,(select com_id from company where com_desc =[color=red][b]hr.company[/b][/color]),hr.region, hr.END_DATE,hr.EMP_DEPT,hr.JOB_DESC,hr.BIRTH_DATE,hr.START_DATE,hr.created_by,hr.created_date,sysdate,'Merge Add||-3-2012 '||DATE_VAL,HR.MEDICAL_PROGRAM,HR.JOB_DGREE);
it gives me error of (red marked)
Compilation errors for PROCEDURE CLINIC_2016.MERGE_EMP_LIVE_2012
Error: PL/SQL: ORA-00904: "HR"."COMPANY": invalid identifier
Line: 57
altough the same identifier exist in the first part of the merge statment (green marked)
i don't know why
|
|
|
|
|
|
Re: merge statement problem [message #657535 is a reply to message #657534] |
Sun, 13 November 2016 08:12 |
emadnabil
Messages: 177 Registered: August 2007
|
Senior Member |
|
|
EMP_TEMP2
-- Create table
create table EMP_TEMP2
(
EMP_ID VARCHAR2(500),
EMP_NAME VARCHAR2(500),
EMP_DEPT VARCHAR2(500),
START_DATE DATE,
JOB_DESC VARCHAR2(500),
REGION VARCHAR2(500),
BIRTH_DATE DATE,
END_DATE DATE,
COMPANY VARCHAR2(500),
JOB_DGREE VARCHAR2(500),
MEDICAL_PROGRAM VARCHAR2(500),
CREATED_BY NUMBER,
CREATED_DATE DATE,
SHEET_MONTH VARCHAR2(100)
)
tablespace CLINIC_2016
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 768K
next 1M
minextents 1
maxextents unlimited
);
RA_CUSTOMER_DETAILS_ALL
-- Create table
create table RA_CUSTOMER_DETAILS_ALL
(
CUSTOMER_ID VARCHAR2(50) not null,
CUSTOMER_NAME VARCHAR2(50),
LAST_UPDATED_DATE DATE,
LAST_UPDATED_BY NUMBER,
CREATION_DATE DATE,
CREATED_BY NUMBER,
COMPANY VARCHAR2(50) not null,
LOCATION VARCHAR2(50),
PHONE VARCHAR2(50),
EMAIL VARCHAR2(50),
ADDRESS VARCHAR2(250),
DESTINATION_LIMIT NUMBER(15),
DOCTOR_LIMIT NUMBER(15),
MEDICINE_LIMIT NUMBER(15),
MONTHLY_ALLOWED NUMBER(15,2) default 59.5,
OVER_FLOW CHAR(1),
COST_CENTER VARCHAR2(50),
DATE_TO DATE,
DEPT VARCHAR2(50),
JOB_DESC VARCHAR2(100),
BIRTH_DATE DATE,
JOB_DATE DATE not null,
MEDICINE_DATE DATE,
SYSTEM_DATE DATE,
PROCESS VARCHAR2(100),
JOB_DGREE VARCHAR2(200) not null,
MEDICAL_PROGRAM VARCHAR2(200) not null
)
tablespace CLINIC_2016
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 3M
next 1M
minextents 1
maxextents unlimited
);
SELECT * FROM v$VERSION;
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Solaris: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
|
|
|
|
Re: merge statement problem [message #657538 is a reply to message #657537] |
Sun, 13 November 2016 08:16 |
emadnabil
Messages: 177 Registered: August 2007
|
Senior Member |
|
|
COMPANY
-- Create table
create table COMPANY
(
COM_ID VARCHAR2(20) not null,
NAME VARCHAR2(400),
COM_DESC VARCHAR2(100),
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
CREATION_DATE DATE,
CREATED_BY NUMBER,
ORG_ID NUMBER,
SERVICE_TYPE VARCHAR2(30)
)
tablespace CLINIC_2016
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table COMPANY
add primary key (COM_ID)
using index
tablespace CLINIC_2016
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 1M
minextents 1
maxextents unlimited
);
alter table COMPANY
add constraint COMP_DESC_UQ unique (COM_DESC)
using index
tablespace CLINIC_2016
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
alter table COMPANY
add constraint COMP_NAME_UQ unique (NAME)
using index
tablespace CLINIC_2016
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
|
|
|
Re: merge statement problem [message #657540 is a reply to message #657538] |
Sun, 13 November 2016 10:11 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Something missing in your CREATE statements:
SQL> merge
2 INTO ra_customer_details_all tr
3 USING emp_temp2 hr
4 ON (
5 tr.customer_id=hr.emp_id)
6 WHEN matched THEN
7 UPDATE
8 SET tr.customer_name=hr.emp_name,
9 tr.company=
10 (
11 SELECT com_id
12 FROM company
13 WHERE com_desc=hr.company),
14 tr.location=hr.region,
15 tr.date_to=hr.end_date,
16 tr.dept=hr.emp_dept,
17 tr.job_desc=hr.job_desc,
18 tr.birth_date=hr.birth_date,
19 tr.job_date=hr.start_date,
20 tr.created_by=userid,
21 tr.creation_date=hr.created_date,
22 tr.last_updated_by=userid,
23 tr.last_updated_date=SYSDATE,
24 tr.process='Merge Update - '
25 ||sheet_month
26 ||' - '
27 ||date_val,
28 tr.medical_program=hr.medical_program,
29 tr.job_dgree=hr.job_dgree
30 WHEN NOT matched THEN
31 INSERT
32 (
33 tr.customer_id,
34 tr.customer_name,
35 tr.company,
36 tr.location,
37 tr.date_to,
38 tr.dept,
39 tr.job_desc,
40 tr.birth_date,
41 tr.job_date ,
42 tr.created_by,
43 tr.creation_date,
44 tr.last_updated_date,
45 tr.process,
46 tr.medical_program,
47 tr.job_dgree
48 )
49 VALUES
50 (
51 hr.emp_id,
52 hr.emp_name,
53 (
54 SELECT com_id
55 FROM company
56 WHERE com_desc =hr.company),
57 hr.region,
58 hr.end_date,
59 hr.emp_dept,
60 hr.job_desc,
61 hr.birth_date,
62 hr.start_date,
63 hr.created_by,
64 hr.created_date,
65 SYSDATE,
66 'Merge Add||-3-2012 '
67 ||date_val,
68 hr.medical_program,
69 hr.job_dgree
70 );
||date_val,
*
ERROR at line 67:
ORA-00904: "DATE_VAL": invalid identifier
|
|
|
Re: merge statement problem [message #657541 is a reply to message #657540] |
Sun, 13 November 2016 11:54 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Commenting userid and date_val (maybe procedure variables?), I have no problem in 10.2..0.4:
SQL> merge
2 INTO ra_customer_details_all tr
3 USING emp_temp2 hr
4 ON (
5 tr.customer_id=hr.emp_id)
6 WHEN matched THEN
7 UPDATE
8 SET tr.customer_name=hr.emp_name,
9 tr.company=
10 (
11 SELECT com_id
12 FROM company
13 WHERE com_desc=hr.company),
14 tr.location=hr.region,
15 tr.date_to=hr.end_date,
16 tr.dept=hr.emp_dept,
17 tr.job_desc=hr.job_desc,
18 tr.birth_date=hr.birth_date,
19 tr.job_date=hr.start_date,
20 tr.created_by=1, --userid,
21 tr.creation_date=hr.created_date,
22 tr.last_updated_by=1, --userid,
23 tr.last_updated_date=SYSDATE,
24 tr.process='Merge Update - '
25 ||sheet_month
26 ||' - '
27 /*||date_val*/,
28 tr.medical_program=hr.medical_program,
29 tr.job_dgree=hr.job_dgree
30 WHEN NOT matched THEN
31 INSERT
32 (
33 tr.customer_id,
34 tr.customer_name,
35 tr.company,
36 tr.location,
37 tr.date_to,
38 tr.dept,
39 tr.job_desc,
40 tr.birth_date,
41 tr.job_date ,
42 tr.created_by,
43 tr.creation_date,
44 tr.last_updated_date,
45 tr.process,
46 tr.medical_program,
47 tr.job_dgree
48 )
49 VALUES
50 (
51 hr.emp_id,
52 hr.emp_name,
53 (
54 SELECT com_id
55 FROM company
56 WHERE com_desc =hr.company),
57 hr.region,
58 hr.end_date,
59 hr.emp_dept,
60 hr.job_desc,
61 hr.birth_date,
62 hr.start_date,
63 hr.created_by,
64 hr.created_date,
65 SYSDATE,
66 'Merge Add||-3-2012 '
67 /*||date_val*/,
68 hr.medical_program,
69 hr.job_dgree
70 );
0 rows merged.
SQL> @v
Oracle version: 10.2.0.4.0
|
|
|
Re: merge statement problem [message #657544 is a reply to message #657541] |
Sun, 13 November 2016 14:41 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Looks like a bug. Fails on higher versions. In any case, I don't like subquery in when [not] matched clause. OP can simply left join emp_temp2 to company in using clause.
SY.
|
|
|
|
|
Re: merge statement problem [message #657561 is a reply to message #657544] |
Mon, 14 November 2016 05:51 |
emadnabil
Messages: 177 Registered: August 2007
|
Senior Member |
|
|
Dear Mr. Solomon Yakobson
Thanks for your reply
i think that is the right opinion
"this query may not work on some version"
because it was working fine on another server but we are try now to reach it for getting its database version
any way
what do you mean by this
OP can simply left join emp_temp2 to company in using clause
or what is your opinion for making this merge statement work fine
Thanks
|
|
|
|
Re: merge statement problem [message #657563 is a reply to message #657561] |
Mon, 14 November 2016 06:27 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
emadnabil wrote on Mon, 14 November 2016 12:51
...
any way
what do you mean by this
OP can simply left join emp_temp2 to company in using clause
...
Just move you SELECT into "hr" like:
SQL> merge
2 INTO ra_customer_details_all tr
3 USING ( select emp_temp2.*, company.com_id
4 from emp_temp2 left outer join company
5 on emp_temp2.company = company.com_desc ) hr
6 ON (
7 tr.customer_id=hr.emp_id)
8 WHEN matched THEN
9 UPDATE
10 SET tr.customer_name=hr.emp_name,
11 tr.company=hr.com_id,
12 tr.location=hr.region,
13 tr.date_to=hr.end_date,
14 tr.dept=hr.emp_dept,
15 tr.job_desc=hr.job_desc,
16 tr.birth_date=hr.birth_date,
17 tr.job_date=hr.start_date,
18 tr.created_by=1, --userid,
19 tr.creation_date=hr.created_date,
20 tr.last_updated_by=1, --userid,
21 tr.last_updated_date=SYSDATE,
22 tr.process='Merge Update - '
23 ||sheet_month
24 ||' - '
25 /*||date_val*/,
26 tr.medical_program=hr.medical_program,
27 tr.job_dgree=hr.job_dgree
28 WHEN NOT matched THEN
29 INSERT
30 (
31 tr.customer_id,
32 tr.customer_name,
33 tr.company,
34 tr.location,
35 tr.date_to,
36 tr.dept,
37 tr.job_desc,
38 tr.birth_date,
39 tr.job_date ,
40 tr.created_by,
41 tr.creation_date,
42 tr.last_updated_date,
43 tr.process,
44 tr.medical_program,
45 tr.job_dgree
46 )
47 VALUES
48 (
49 hr.emp_id,
50 hr.emp_name,
51 hr.com_id,
52 hr.region,
53 hr.end_date,
54 hr.emp_dept,
55 hr.job_desc,
56 hr.birth_date,
57 hr.start_date,
58 hr.created_by,
59 hr.created_date,
60 SYSDATE,
61 'Merge Add||-3-2012 '
62 /*||date_val*/,
63 hr.medical_program,
64 hr.job_dgree
65 );
0 rows merged.
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:03:13 CDT 2024
|