CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle-sqldeveloper
bys0202
bys0202

Reputation: 41

SQL MERGE INTO( has issue when trying to insert into database by using subquery)

MERGE INTO DWCUST dc 
USING A2CUSTMELB cm
ON (dc.firstname=cm.fname AND  dc.surname=cm.sname and dc.postcode=cm.postcode)
WHEN MATCHED THEN UPDATE SET dc.DWSOURCEIDMELB=cm.custid
WHEN NOT MATCHED THEN 
INSERT (DWCUSTID, DWSOURCEIDMELB, FIRSTNAME, SURNAME, GENDER, PHONE, POSTCODE, CITY, STATE, CUSTCATNAME)
VALUES (DWCUST_SEQ.NEXTVAL, cm.custid, cm.fname, cm.sname, upper(cm.gender), cm.phone, cm.postcode, cm.city, cm.state,
(select cc.custcatname from a2custcategory cc  where  cm.custcatcode = cc.custcatcode ) );

Error report -

SQL Error: ORA-00904: "CM"."CUSTCATCODE": invalid identifier

  1. 00000 - "%s: invalid identifier"

How can I fix this error? There is a column called CUSTCATCODE in my A2CUSTMELB database.

Thanks.

Upvotes: 1

Views: 242

Answers (1)

dnoeth
dnoeth

Reputation: 60472

You can't use a SELECT with a MERGE INSERT, move the join into the USING:

MERGE INTO DWCUST dc 
USING
  (SELECT A2CUSTMELB.*, cc.custcatname 
   FROM A2CUSTMELB JOIN a2custcategory cc  
   ON CM.custcatcode = cc.custcatcode
  ) CM
ON (dc.firstname=CM.fname AND  dc.surname=CM.sname AND dc.postcode=CM.postcode)
WHEN MATCHED THEN UPDATE SET dc.DWSOURCEIDMELB=CM.custid
WHEN NOT MATCHED THEN 
INSERT (DWCUSTID, DWSOURCEIDMELB, FIRSTNAME, SURNAME, GENDER, PHONE, POSTCODE, CITY, STATE, CUSTCATNAME)
VALUES (DWCUST_SEQ.NEXTVAL, CM.custid, CM.fname, CM.sname, UPPER(CM.gender), CM.phone, CM.postcode, CM.city, CM.state,
        CM.custcatname );

Upvotes: 2

Related Questions