I have created a number of different (simple) merge instructions without problem but I now have hit a difficulty which is affecting several others.
To isolate the problem I have simplified one of those but still have the same error condition (i.e. ERROR at line 7:
ORA-00904: "CF1"."LOCAL_CLAIM_NO": invalid identifier).
The SQL code I submitted to PL/SQL was :-
merge into scram_mart.claim_facts cft
using (
select nvl(dlr.Dealer_id,0) as dealer_id
from scram_stage.claim_stage csg
left outer join scram_mart.dealers dlr
on (csg.wo_no=dlr.local_dealer_no)) cf1
on (cf1.local_claim_no = cft.local_claim_no)
when matched then
update set
cft.Dealer_ID = cf1.Dealer_ID
when not matched then
insert (cft.Dealer_ID)
values (cf1.Dealer_ID);
Can anyone advise me the 'error of my ways' ?!
To isolate the problem I have simplified one of those but still have the same error condition (i.e. ERROR at line 7:
ORA-00904: "CF1"."LOCAL_CLAIM_NO": invalid identifier).
The SQL code I submitted to PL/SQL was :-
merge into scram_mart.claim_facts cft
using (
select nvl(dlr.Dealer_id,0) as dealer_id
from scram_stage.claim_stage csg
left outer join scram_mart.dealers dlr
on (csg.wo_no=dlr.local_dealer_no)) cf1
on (cf1.local_claim_no = cft.local_claim_no)
when matched then
update set
cft.Dealer_ID = cf1.Dealer_ID
when not matched then
insert (cft.Dealer_ID)
values (cf1.Dealer_ID);
Can anyone advise me the 'error of my ways' ?!