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