Home » SQL & PL/SQL » SQL & PL/SQL » merge statement problem (10g)
merge statement problem [message #657531] Sun, 13 November 2016 07:21 Go to next message
emadnabil
Messages: 171
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 #657532 is a reply to message #657531] Sun, 13 November 2016 07:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
which USER owns the procedure?

where is "HR" alias established?
Re: merge statement problem [message #657533 is a reply to message #657532] Sun, 13 November 2016 07:46 Go to previous messageGo to next message
emadnabil
Messages: 171
Registered: August 2007
Senior Member
which USER owns the procedure?
CLINIC_2016

where is "HR" alias established?
EMP_TEMP2 Table (2nd line in the merge statement)
Re: merge statement problem [message #657534 is a reply to message #657533] Sun, 13 November 2016 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Is there any company in EMP_TEMP2?
Post DESC EMP_TEMP2.

Better post CREATE TABLE statements for all tables and result of SELECT * FROM V$VERSION so we can reproduce what you have.

Re: merge statement problem [message #657535 is a reply to message #657534] Sun, 13 November 2016 08:12 Go to previous messageGo to next message
emadnabil
Messages: 171
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 #657537 is a reply to message #657535] Sun, 13 November 2016 08:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"company" table is missing.

Re: merge statement problem [message #657538 is a reply to message #657537] Sun, 13 November 2016 08:16 Go to previous messageGo to next message
emadnabil
Messages: 171
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 #657545 is a reply to message #657544] Sun, 13 November 2016 14:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It works for me in 11.2.0.4 but I completely agree just waiting for OP's answer to suggest this option.

Re: merge statement problem [message #657547 is a reply to message #657545] Sun, 13 November 2016 18:42 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
It fails on 10.2.0.5.0, 11.2.0.3.0 and 12.1.0.1.0. Works on 12.1.0.2.0.

SY.
Re: merge statement problem [message #657561 is a reply to message #657544] Mon, 14 November 2016 05:51 Go to previous messageGo to next message
emadnabil
Messages: 171
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 #657562 is a reply to message #657545] Mon, 14 November 2016 05:53 Go to previous messageGo to next message
emadnabil
Messages: 171
Registered: August 2007
Senior Member
Dear Mr. Michel Cadot
Thanks for your effort

i tried your merge statement as it is but gives me the same error
ver 10.2.0.5.0
Re: merge statement problem [message #657563 is a reply to message #657561] Mon, 14 November 2016 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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.

Re: merge statement problem [message #657566 is a reply to message #657563] Mon, 14 November 2016 07:56 Go to previous message
emadnabil
Messages: 171
Registered: August 2007
Senior Member
Thanks Mr.Michel Cadot
Previous Topic: Filling a table of object in function loop
Next Topic: Calling procedure from trigger
Goto Forum:
  


Current Time: Thu Mar 28 23:54:18 CDT 2024