Skip to content
No results
We use cookies to ensure that we give you the best experience on our website.

# Oracle SQL Query rewrite: NVL function to UNION ALL statement

NVL function to UNION ALL statement

NVL(A, B) means that If A is null then B or A. So, we can re-write NVL using UNION ALL and IS NOT NULL condition.

This is an article of Oracle SQL Query rewrite serials.

Take this sql for example:

    SELECT *
    FROM   fnd_concurrent_requests
    WHERE  NVL(request_date, closed_date) = :b1
    AND    status = 'E' ;

SQL will be translated into:

    SELECT *
    FROM   fnd_concurrent_requests
    WHERE  request_date = :b1
           AND request_date IS NOT NULL
           AND status = 'E'
    UNION ALL
    SELECT *
    FROM   fnd_concurrent_requests
    WHERE  closed_date = :b1
           AND request_date IS NULL
           AND status = 'E'

Download this demo: C# version