Hi I need to translate code used at work from old Oracle syntax to ANSI standard.
I have a long scripts I have joins seven tables, but I will only show three tables here for simplicity.
SELECT count(*)
FROM business_action_events baecom
,parameter_values comta
,parameter_values comtd
WHERE
baecom.bae_reusable_refno = comta.pva_reusable_refno (+)
AND baecom.bae_aet_code = 'CCOMMIT'
AND comta.pva_pdu_pdf_name (+) = 'CUS_COMMIT_YN'
AND baecom.bae_reusable_refno = comtd.pva_reusable_refno (+)
AND comtd.pva_pdu_pdf_name (+) = 'CUS_COMMIT_TDATE'
I have come up with the following code. however I am not able to get the same result as above. My code would work up to the joining two tables and would get different result if I join three tables.
SELECT count(*)
FROM business_action_events baecom
left join parameter_values comta on baecom.bae_reusable_refno = comta.pva_reusable_refno
left join parameter_values comtd on baecom.bae_reusable_refno = comtd.pva_reusable_refno
Where
baecom.bae_aet_code = 'CCOMMIT'
and nvl(comta.pva_pdu_pdf_name, 'CUS_COMMIT_YN') = 'CUS_COMMIT_YN'
and nvl(comtd.pva_pdu_pdf_name, 'CUS_COMMIT_TDATE') = 'CUS_COMMIT_TDATE'
Any help would be great?