ORA-00904 with the Merge statement

[w860098]

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' ?!

 

[MacLeod72]

Hi-

It appears that the subquery you have aliased as CF1:

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)

does not contain the field "LOCAL_CLAIM_NO" and therefore that field cannot be used in the join.

Hope this helps!

--Rob

 

[w860098]

Well spotted - when I reduced the amount of code, I removed too much !
I have now resolved that difficulty and in fact have got it working with significantly more data items.
I have now restored the full select and join code (see below) but have had to limit the number of data items declared in the 'matched' and 'not matched' lists to avoid this error condition. The initial code reads :-

merge into scram_mart.claim_facts cft
using (
select nvl(dlr.Dealer_id,0) as dealer_id
, nvl(cviv.Vehicle_id,0) as vehicle_id
, csg.cl_no as local_claim_no
, cst.Claim_Status_ID as Claim_Status_ID
, case csg.cl_type when 'I' then 'Invoice'
when 'C' then 'Claim'
else 'Unknown'
end as Claim_Type
, nvl(csv.client_schedule_id,0) as Claim_Against_Schedule_Id
, sched_no as Claim_Against_Local_Schedule
-- , nvl(pcsv.client_schedule_id, null) as Paid_Against_Schedule_Id
, null as Paid_Against_Schedule_Id
, csg.cl_sch_pay as Paid_Against_Local_Schedule
, case when csg.sched_no <> csg.cl_sch_pay and trim(csg.cl_sch_pay) <> '' then 'Y'
else 'N'
end as Claim_Diff_To_Pay_Schedule
, csg.in_no as Dealer_Invoice_No
, csg.cl_Job as Dealer_Job_No
, csg.cl_date as Claim_Entered_Date
, csg.cl_work_dt as Work_Done_Date
, csg.cl_complt as Claim_Completion_Date
, csg.cl_selfbil as Finance_Ref
, csg.cl_claim as Claimed_total_Value
, csg.cl_70 as claimed_total_exchange_unit_va
, csg.cl_lab as Claimed_total_labour_value
, csg.cl_other as Claimed_total_other_value
, csg.cl_par as Claimed_total_parts_value
, csg.cl_std_7 as standard_exchange_unit_value
, csg.cl_std_l as standard_labour_value
, csg.cl_std_o as standard_other_value
, csg.cl_std_p as standard_parts_value
, csg.cl_pay_7 as Paid_exchange_unit_value
, csg.cl_pay_l as Paid_labour_value
, csg.cl_pay_o as Paid_other_value
, csg.cl_pay_p as Paid_parts_value
, csg.cl_pay as Paid_total_value
, csg.cl_rate_s as labour_standard_rate
, csg.cl_rate as labour_claimed_rate
, case when csv.Dealer_Schedule_Rate is null then 'N'
when csv.Dealer_Schedule_Rate <> csg.cl_rate then 'N'
else 'Y'
end as Nominated_Dealer_Rate_Used
, case when csv.nominated_dealer_id is null then 'N'
when csv.nominated_dealer_id <> dlr.Dealer_Id then 'N'
else 'Y'
end as nominated_dealer_used
from scram_stage.claim_stage csg
left outer join scram_mart.dealers dlr
on (csg.wo_no=dlr.local_dealer_no)
left outer join scram_mart.current_schedule_v csv
on (csg.sched_no=csv.local_schedule_no)
left outer join scram_mart.current_vehicle_info_v cviv
on (csv.vehicle_chass_no=cviv.vehicle_chass_no)
join scram_mart.claim_statuses cst
on (csg.cl_status=cst.local_claim_status)
left outer join scram_mart.current_schedule_v pcsv
on (csg.cl_sch_pay=pcsv.local_schedule_no)) cf1
on (cf1.local_claim_no = cft.local_claim_no)

Note that when I try to run the full code, I have to 'execute' the file, i.e. using "@ filename", because when entering the code directly it cannot be fully accepted (i.e. it appeasrs to run out of 'buffer' space)