{"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":"<p>IN clause to UNION ALL statement<\/p>\n<p>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.<br \/>\nSo. we can re-write IN list values using UNION ALL.<br \/>\nAlso, we can re-write IN list values using ROWID, inline view and UNION ALL.<\/p>\n<p>This is an article of <a href=\"http:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/\">Oracle SQL Query rewrite serials<\/a>.<\/p>\n<p>Take this sql for example:<\/p>\n<pre>\r\nSELECT *\r\nFROM   fnd_concurrent_requests\r\nWHERE  status IN ('R','E')\r\nAND    request_date >= SYSDATE - 1 ;\r\n<\/pre>\n<p>SQL will be translated into:<\/p>\n<pre>\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>\n<p>Download this demo: <a href=\"http:\/\/www.dpriver.com\/gsp\/demos\/csharp\/sqlrewrite\/sqlrewrite.zip\">C# version<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 (&#8216;R&#8217;,&#8217;E&#8217;) AND request_date >= SYSDATE &#8211; 1 ; SQL will be translated into: SELECT * FROM fnd_concurrent_requests WHERE status = &#8216;R&#8217; 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":"<!-- This site is optimized with the Yoast SEO plugin v19.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Help you to make better use of General SQL Parser<\/title>\n<meta name=\"description\" content=\"Oracle SQL Query rewrite: IN clause to UNION ALL statement\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"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\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Help you to make better use of General SQL Parser\" \/>\n<meta property=\"og:description\" content=\"Oracle SQL Query rewrite: IN clause to UNION ALL statement\" \/>\n<meta property=\"og:url\" content=\"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\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and Data Blog\" \/>\n<meta property=\"article:modified_time\" content=\"2012-07-10T07:09:32+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\",\"name\":\"SQL and Data Blog\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"sameAs\":[],\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"contentUrl\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"width\":251,\"height\":72,\"caption\":\"SQL and Data Blog\"},\"image\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"name\":\"SQL and Data Blog\",\"description\":\"SQL related blog for database professional\",\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"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\/\",\"url\":\"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\/\",\"name\":\"Help you to make better use of General SQL Parser\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\"},\"datePublished\":\"2012-07-10T02:51:41+00:00\",\"dateModified\":\"2012-07-10T07:09:32+00:00\",\"description\":\"Oracle SQL Query rewrite: IN clause to UNION ALL statement\",\"breadcrumb\":{\"@id\":\"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\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"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\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"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\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.dpriver.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"List of demos illustrate how to use general sql parser\",\"item\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Oracle SQL Query rewrite\",\"item\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/\"},{\"@type\":\"ListItem\",\"position\":4,\"name\":\"Oracle SQL Query rewrite: IN clause to UNION ALL statement\"}]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Help you to make better use of General SQL Parser","description":"Oracle SQL Query rewrite: IN clause to UNION ALL statement","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"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\/","og_locale":"en_US","og_type":"article","og_title":"Help you to make better use of General SQL Parser","og_description":"Oracle SQL Query rewrite: IN clause to UNION ALL statement","og_url":"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\/","og_site_name":"SQL and Data Blog","article_modified_time":"2012-07-10T07:09:32+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Organization","@id":"https:\/\/www.dpriver.com\/blog\/#organization","name":"SQL and Data Blog","url":"https:\/\/www.dpriver.com\/blog\/","sameAs":[],"logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","contentUrl":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","width":251,"height":72,"caption":"SQL and Data Blog"},"image":{"@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"WebSite","@id":"https:\/\/www.dpriver.com\/blog\/#website","url":"https:\/\/www.dpriver.com\/blog\/","name":"SQL and Data Blog","description":"SQL related blog for database professional","publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"WebPage","@id":"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\/","url":"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\/","name":"Help you to make better use of General SQL Parser","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/#website"},"datePublished":"2012-07-10T02:51:41+00:00","dateModified":"2012-07-10T07:09:32+00:00","description":"Oracle SQL Query rewrite: IN clause to UNION ALL statement","breadcrumb":{"@id":"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\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["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\/"]}]},{"@type":"BreadcrumbList","@id":"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\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dpriver.com\/blog\/"},{"@type":"ListItem","position":2,"name":"List of demos illustrate how to use general sql parser","item":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/"},{"@type":"ListItem","position":3,"name":"Oracle SQL Query rewrite","item":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/"},{"@type":"ListItem","position":4,"name":"Oracle SQL Query rewrite: IN clause to UNION ALL statement"}]}]}},"_links":{"self":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/1474"}],"collection":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/comments?post=1474"}],"version-history":[{"count":3,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/1474\/revisions"}],"predecessor-version":[{"id":1513,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/1474\/revisions\/1513"}],"up":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/1445"}],"wp:attachment":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/media?parent=1474"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}