Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Translating older join syntax to ANSI standard

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?

Comments
Post Details
Added on Jun 19 2023
19 comments
728 views