{"id":1474,"date":"2012-07-09T21:51:41","date_gmt":"2012-07-10T02:51:41","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=1474"},"modified":"2012-07-10T02:09:32","modified_gmt":"2012-07-10T07:09:32","slug":"oracle-sql-query-rewrite-in-clause-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-in-clause-to-union-all-statement\/","title":{"rendered":"Oracle SQL Query rewrite: IN clause to UNION ALL statement"},"content":{"rendered":"

IN clause to UNION ALL statement<\/p>\n

COL1 IN (\u2018A\u2019, \u2018B\u2019, \u2018C\u2019) means that COL1 is \u2018A\u2019 or \u2018B\u2019 or \u2018C\u2019 and or can be replaced with UNION ALL.
\nSo. we can re-write IN list values using UNION ALL.
\nAlso, we can re-write IN list values using ROWID, inline view and UNION ALL.<\/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  status IN ('R','E')\r\nAND    request_date >= SYSDATE - 1 ;\r\n<\/pre>\nSQL will be translated into:<\/p>\n\r\nSELECT *\r\nFROM   fnd_concurrent_requests\r\nWHERE  status = 'R'\r\n       AND request_date >= SYSDATE - 1\r\nUNION ALL\r\nSELECT *\r\nFROM   fnd_concurrent_requests\r\nWHERE  status = 'E'\r\n       AND request_date >= SYSDATE - 1 \r\n<\/pre>\nDownload this demo: C# version<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"IN clause to UNION ALL statement COL1 IN (\u2018A\u2019, \u2018B\u2019, \u2018C\u2019) means that COL1 is \u2018A\u2019 or \u2018B\u2019 or \u2018C\u2019 and or can be replaced with UNION ALL. So. we can re-write IN list values using UNION ALL. Also, we can re-write IN list values using ROWID, inline view and UNION ALL. This is an article of Oracle SQL Query rewrite serials. Take this sql for example: SELECT * FROM fnd_concurrent_requests WHERE status IN (‘R’,’E’) AND request_date >= SYSDATE – 1 ; SQL will be translated into: SELECT * FROM fnd_concurrent_requests WHERE status = ‘R’ AND request_date >= SYSDATE -\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