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!

WHEN NOT EXIST, NVL, and NOT EQUAL TO in a case statement.

2794511Dec 11 2014 — edited Dec 11 2014

Hi All - I am extremely confused about the SQL below. Can some one please help me and reading this SQL below. Does the WHEN NOT EXIST and NOT EQUAL TO cancel each other? I have a "validation status" column and my case statement doesn't yield the correct results that I am trying to pull based on the match_status_flag column.

    CASE

      WHEN NOT EXISTS

        (SELECT match_status_flag

        FROM apps.ap_invoice_distributions_all aida

        WHERE aida.org_id               =81

        AND aida.invoice_id             = aia.invoice_id

        AND NVL(match_status_flag,'N') != 'A'

        )

      THEN 'Validated'

      WHEN NOT EXISTS

        (SELECT match_status_flag

        FROM apps.ap_invoice_distributions_all aida

        WHERE aida.org_id               =81

        AND aida.invoice_id             = aia.invoice_id

        AND NVL(match_status_flag,'N') != 'N'

        )

      THEN 'Never Validated'

      ELSE 'Needs Revalidation'

    END validation_status

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 8 2015
Added on Dec 11 2014
15 comments
5,368 views