{"id":1478,"date":"2012-07-09T21:53:53","date_gmt":"2012-07-10T02:53:53","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=1478"},"modified":"2012-07-10T02:10:24","modified_gmt":"2012-07-10T07:10:24","slug":"oracle-sql-query-rewrite-nvl-function-to-union-all-statement","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/oracle-sql-query-rewrite-nvl-function-to-union-all-statement\/","title":{"rendered":"Oracle SQL Query rewrite: NVL function to UNION ALL statement"},"content":{"rendered":"

NVL function to UNION ALL statement<\/p>\n

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.<\/p>\n

This is an article of Oracle SQL Query rewrite serials<\/a>.<\/p>\n

Take this sql for example:<\/p>\n

    \r\nSELECT *\r\nFROM   fnd_concurrent_requests\r\nWHERE  NVL(request_date, closed_date) = :b1\r\nAND    status = 'E' ;\r\n<\/pre>\nSQL will be translated into:<\/p>\n\r\nSELECT *\r\nFROM   fnd_concurrent_requests\r\nWHERE  request_date = :b1\r\n       AND request_date IS NOT NULL\r\n       AND status = 'E'\r\nUNION ALL\r\nSELECT *\r\nFROM   fnd_concurrent_requests\r\nWHERE  closed_date = :b1\r\n       AND request_date IS NULL\r\n       AND status = 'E' \r\n<\/pre>\nDownload this demo: C# version<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"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’\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1445,"menu_order":0,"comment_status":"closed","ping_status":"open","template":"gsp_feature_page_tt.php","meta":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nHelp you to make better use of General SQL Parser<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n