Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate Column error on MERGE statement (10.2.0.3.0 - 64bit)
Duplicate Column error on MERGE statement [message #280152] Mon, 12 November 2007 11:38 Go to next message
welchaz
Messages: 23
Registered: April 2005
Location: Tucson, AZ
Junior Member
I'm getting "duplicate column name" error on the PL/SQL block below. I have 4 other similar code blocks for other tables that work fine; I'm not seeing the duplicate column here. Any help would be appreciated.

begin
MERGE into cm_gis_con_stg CON
USING (select reg_read_id, rs_cd, badge_nbr, mr_rte_cd, entity_name, acct_id, prem_id, cm_prem_addr, reg_reading, read_dttm, read_type_flg, mtr_status_flg
from cisuesg.cm_gis_con_stg@CISQ
where reg_read_id in ('000007997105','000027844043','000038850064','008533537671') -- 1st two should be inserts
minus
select reg_read_id, rs_cd, badge_nbr, mr_rte_cd, entity_name, acct_id, prem_id, cm_prem_addr, reg_reading, read_dttm, read_type_flg, mtr_status_flg
from ues.cm_gis_con_stg
where reg_read_id in ('000007997105','000027844043','000038850064','008533537671')) CCB
ON (CON.reg_read_id = CCB.reg_read_id)
WHEN MATCHED THEN UPDATE SET CON.rs_cd = CCB.rs_cd,
CON.badge_nbr = CCB.badge_nbr,
CON.mr_rte_cd = CCB.mr_rte_cd,
CON.entity_name = CCB.entity_name,
CON.acct_id = CCB.acct_id,
CON.prem_id = CCB.prem_id,
CON.cm_prem_addr = CCB.cm_prem_addr,
CON.mr_rte_cd = CCB.mr_rte_cd,
CON.reg_reading = CCB.reg_reading,
CON.read_dttm = CCB.read_dttm,
CON.read_type_flg = CCB.read_type_flg,
CON.mtr_status_flg = CCB.mtr_status_flg,
CON.cm_cis_updt_dttm = sysdate,
CON.version = 999
WHEN NOT MATCHED THEN INSERT (CON.oid, CON.reg_read_id, CON.rs_cd, CON.badge_nbr, CON.mr_rte_cd, CON.entity_name, CON.acct_id, CON.prem_id,
CON.cm_prem_addr, CON.reg_reading, CON.read_dttm, CON.read_type_flg, CON.mtr_status_flg, CON.cm_cis_updt_dttm, CON.version)
VALUES (oid_sequence.nextval, CCB.reg_read_id, CCB.rs_cd, CCB.badge_nbr, CCB.mr_rte_cd, CCB.entity_name, CCB.acct_id, CCB.prem_id,
CCB.cm_prem_addr, CCB.reg_reading, CCB.read_dttm, CCB.read_type_flg, CCB.mtr_status_flg, sysdate, 999);
dbms_output.put_line('Inserted/updated CON rows: ' || sql%rowcount);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
/
ORA-00957: duplicate column name


Remote table description (CISQ):

CISQ.WORLD>desc cm_gis_con_stg;
Name
------------------------------------------
REG_READ_ID NOT NULL CHAR(12)
RS_CD NOT NULL CHAR(8)
BADGE_NBR NOT NULL CHAR(30)
MR_RTE_CD NOT NULL CHAR(8)
ENTITY_NAME NOT NULL VARCHAR2(64)
ACCT_ID NOT NULL CHAR(10)
PREM_ID NOT NULL CHAR(10)
CM_PREM_ADDR NOT NULL VARCHAR2(256)
REG_READING NOT NULL NUMBER(15,6)
READ_DTTM NOT NULL DATE
READ_TYPE_FLG NOT NULL CHAR(2)
MTR_STATUS_FLG NOT NULL CHAR(2)
CM_CIS_UPDT_DTTM NOT NULL DATE
VERSION NOT NULL NUMBER(5)

Local table desc:

ESRD.WORLD>desc cm_gis_con_stg;
Name
--------------------------------------------
OID NOT NULL NUMBER(38)
REG_READ_ID NOT NULL CHAR(12)
RS_CD NOT NULL CHAR(8)
BADGE_NBR NOT NULL CHAR(30)
MR_RTE_CD NOT NULL CHAR(8)
ENTITY_NAME NOT NULL VARCHAR2(64)
ACCT_ID NOT NULL CHAR(10)
PREM_ID NOT NULL CHAR(10)
CM_PREM_ADDR NOT NULL VARCHAR2(256)
REG_READING NOT NULL NUMBER(15,6)
READ_DTTM NOT NULL DATE
READ_TYPE_FLG NOT NULL CHAR(2)
MTR_STATUS_FLG NOT NULL CHAR(2)
CM_CIS_UPDT_DTTM NOT NULL DATE
VERSION NOT NULL NUMBER(5)


Re: Duplicate Column error on MERGE statement [message #280154 is a reply to message #280152] Mon, 12 November 2007 11:42 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
You can start by using code tags
Re: Duplicate Column error on MERGE statement [message #280157 is a reply to message #280152] Mon, 12 November 2007 11:47 Go to previous messageGo to next message
welchaz
Messages: 23
Registered: April 2005
Location: Tucson, AZ
Junior Member
Sorry, haven't posted anything for so long, I forgot how. With code tags:


begin
  MERGE into cm_gis_con_stg CON
  USING (select reg_read_id, rs_cd, badge_nbr, mr_rte_cd, entity_name, acct_id, prem_id, cm_prem_addr, reg_reading, read_dttm, read_type_flg, mtr_status_flg
           from cisuesg.cm_gis_con_stg@CISQ
          where reg_read_id in ('000007997105','000027844043','000038850064','008533537671')  -- 1st two should be inserts
          minus
          select reg_read_id, rs_cd, badge_nbr, mr_rte_cd, entity_name, acct_id, prem_id, cm_prem_addr, reg_reading, read_dttm, read_type_flg, mtr_status_flg
           from ues.cm_gis_con_stg
          where reg_read_id in ('000007997105','000027844043','000038850064','008533537671')) CCB
     ON (CON.reg_read_id = CCB.reg_read_id)
   WHEN MATCHED THEN UPDATE SET CON.rs_cd             = CCB.rs_cd,
                                CON.badge_nbr         = CCB.badge_nbr,
                                CON.mr_rte_cd         = CCB.mr_rte_cd,
                                CON.entity_name       = CCB.entity_name,
                                CON.acct_id           = CCB.acct_id,
                                CON.prem_id           = CCB.prem_id,
                                CON.cm_prem_addr      = CCB.cm_prem_addr,
                                CON.mr_rte_cd         = CCB.mr_rte_cd,
                                CON.reg_reading       = CCB.reg_reading,
                                CON.read_dttm         = CCB.read_dttm,
                                CON.read_type_flg     = CCB.read_type_flg,
                                CON.mtr_status_flg    = CCB.mtr_status_flg,
                                CON.cm_cis_updt_dttm  = sysdate,
                                CON.version           = 999
   WHEN NOT MATCHED THEN INSERT (CON.oid, CON.reg_read_id, CON.rs_cd, CON.badge_nbr, CON.mr_rte_cd, CON.entity_name, CON.acct_id, CON.prem_id, 
                                 CON.cm_prem_addr, CON.reg_reading, CON.read_dttm, CON.read_type_flg, CON.mtr_status_flg, CON.cm_cis_updt_dttm, CON.version)
   VALUES (oid_sequence.nextval, CCB.reg_read_id, CCB.rs_cd, CCB.badge_nbr, CCB.mr_rte_cd, CCB.entity_name, CCB.acct_id, CCB.prem_id, 
                                 CCB.cm_prem_addr, CCB.reg_reading, CCB.read_dttm, CCB.read_type_flg, CCB.mtr_status_flg, sysdate, 999);
  dbms_output.put_line('Inserted/updated CON rows: ' || sql%rowcount);       
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END;
/
ORA-00957: duplicate column name

PL/SQL procedure successfully completed.



Re: Duplicate Column error on MERGE statement [message #280162 is a reply to message #280157] Mon, 12 November 2007 12:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now limit your lines to 80 characters.

At least, you gave "CON.mr_rte_cd" twice.
Check other columns.

Regards
Michel
Re: Duplicate Column error on MERGE statement [message #280172 is a reply to message #280152] Mon, 12 November 2007 12:42 Go to previous message
welchaz
Messages: 23
Registered: April 2005
Location: Tucson, AZ
Junior Member
Yes, you are right; there was a duplicate in the UPDATE portion...I was concentrating too much on the INSERT portion, as the Oracle error documentation said you'd get this error in a CREATE or INSERT.

Problem thus solved; thank you.
Previous Topic: User privilege for another user's specific schema
Next Topic: External Table on tab delimited data not returning any rows
Goto Forum:
  


Current Time: Sun Dec 11 04:06:20 CST 2016

Total time taken to generate the page: 0.21808 seconds