oracle merge statement
The following statement is in a Stored Procedure MERGE INTO table1 a
USING (SELECT a.ROWID row_id, RTRIM(NVL(c.address_line1_text, '')) || ' ' ||
RTRIM(NVL(b.unit_number, '')) address_line1_text,
RTRIM(c.city_name) city_name, RTRIM(c.state_code) 开发者_运维百科state_code,
RTRIM(c.basic_zip_code) basic_zip_code,
NVL(b.unit_number, ' ') unit_number
FROM table1 a ,table2 b ,table3 c
WHERE
a.program_type_cd = 'P'
AND a.development_code = b.development_number
) src
ON ( a.ROWID = src.row_id )
--(ERROR IN LINE BELOW)
WHEN MATCHED THEN UPDATE SET street = RTRIM(NVL(src.address_line1_text, ''))
|| ' ' || RTRIM(NVL(b.unit_number, '')),
city = RTRIM(src.city_name),
STATE = RTRIM(src.state_code),
zip = RTRIM(src.basic_zip_code),
unit_number = NVL(src.unit_number, ' ');
I am getting an error saying :
"B"."UNIT_NUMBER": INVALID IDENTIFIER
I think you should replace b.unit_number
by src.unit_number
here:
|| ' ' || RTRIM(NVL(src.unit_number, '')), /* <-- */
city = RTRIM(src.city_name),
STATE = RTRIM(src.state_code),
zip = RTRIM(src.basic_zip_code),
unit_number = NVL(src.unit_number, ' ');
精彩评论