hello,
Sql Developer Version: 11g
Two tables : nav_vendors(source),vendor(destination)
Both having 16 "Same" columns(data will differ for some records..for some records data remains same to check DML process is working as per need).
i am trying to do insert update and delete using Merge Operation
i m getting below error when ever i tried to Merge them.All Column Names are same in table.
SQL Error: ORA-00904: "S"."COMPANY_CODE": invalid identifier
SQL:
MERGE INTO source s
USING ( select nvl(d.VENDOR,s.VENDOR) VENDOR,
nvl(d.NAME,s.NAME) NAME,
nvl(d.NAME3,s.NAME3) NAME3,
nvl(d.ADDRESS1,s.ADDRESS1) ADDRESS1,
nvl(d.DISTRICT,s.DISTRICT)DISTRICT,
nvl(d.CITY,s.CITY)CITY,
nvl(d.COUNTRY,s.COUNTRY) COUNTRY,
nvl(d.POSTCODE,s.POSTCODE) POSTCODE,
nvl(d.TELEPHONE,s.TELEPHONE)TELEPHONE,
nvl(d.FAX,s.FAX) FAX,
nvl(d.CONTACT,s.CONTACT) CONTACT,
nvl(d.URL,s.URL) URL,
nvl(d.EMAIL,s.EMAIL) EMAIL,
nvl(d.DELIVERY_TOLERANCE,s.DELIVERY_TOLERANCE) DELIVERY_TOLERANCE,
nvl(d.EXCLUDE_REWORK_TEXT,s.EXCLUDE_REWORK_TEXT) EXCLUDE_REWORK_TEXT,
nvl(d.COMPANY_CODE,s.COMPANY_CODE) COMPANY_CODE,
(case
when ((d.VENDOR=s.VENDOR)
and (d.NAME=s.NAME)
and (d.NAME3=s.NAME3)
and (d.ADDRESS1=s.ADDRESS1)
and (d.DISTRICT=s.DISTRICT)
and (d.CITY=s.CITY)
and (d.COUNTRY=s.COUNTRY)
and (d.POSTCODE=s.POSTCODE)
and (d.TELEPHONE=s.TELEPHONE)
and (d.FAX=s.FAX)
and (d.CONTACT=s.CONTACT)
and (d.URL=s.URL)
and (d.EMAIL=s.EMAIL)
and (d.DELIVERY_TOLERANCE=s.DELIVERY_TOLERANCE)
and (d.EXCLUDE_REWORK_TEXT=s.EXCLUDE_REWORK_TEXT)
and (d.COMPANY_CODE=s.COMPANY_CODE))
then 'matched'
when d.COMPANY_CODE is null
then 'DELETE'
when s.COMPANY_CODE is null
then 'INSERT'
else
'not matched'
end ) chck
from nav_vendorz s
full outer join vendor d
on(d.COMPANY_CODE=s.COMPANY_CODE)
) d
ON (s.COMPANY_CODE=d.COMPANY_CODE and (d.chck in ('not matched','matched','DELETE')))
WHEN MATCHED THEN
------------->UPDATE SET s.company_code = d.company_code <----------------Error Line
WHERE d.chck in ('not matched','DELETE')
DELETE WHERE d.chck = 'DELETE'
WHEN NOT MATCHED THEN
INSERT (VENDOR,NAME,NAME3,ADDRESS1,DISTRICT,CITY,COUNTRY,POSTCODE,TELEPHONE,FAX,CONTACT,URL,EMAIL,DELIVERY_TOLERANCE,EXCLUDE_REWORK_TEXT,COMPANY_CODE)
VALUES
(d.VENDOR, d.NAME,d.NAME3,d.ADDRESS1,d.DISTRICT,d.CITY,d.COUNTRY,d.POSTCODE,d.TELEPHONE,d.FAX,d.CONTACT,d.URL,d.EMAIL, d.DELIVERY_TOLERANCE,d.EXCLUDE_REWORK_TEXT,d.COMPANY_CODE);/