{"id":1482,"date":"2012-07-09T21:56:05","date_gmt":"2012-07-10T02:56:05","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=1482"},"modified":"2012-07-10T02:13:12","modified_gmt":"2012-07-10T07:13:12","slug":"oracle-sql-query-rewrite-view-expansion","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-view-expansion\/","title":{"rendered":"Oracle SQL Query rewrite: VIEW expansion"},"content":{"rendered":"<p>VIEW expansion<\/p>\n<p>If object used in FROM clause is object VIEW, then extract view text from database and replace view name with view text. <\/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 COUNT(odp.pallet_id) pallet_qty,\r\n       COUNT(odp.box_id) box_qty,\r\n       SUM(packing_nw) sum_packing_nw,\r\n       SUM(packing_gw) sum_packing_gw,\r\n       SUM(pallet_qty) sum_packing_qty\r\n  FROM xxopsm_delivery_headers odh,\r\n       xxopsm_delivery_pack_v odp\r\n WHERE odh.organization_id = :1\r\n   AND odh.organization_id = odp.organization_id\r\n   AND odh.plan_batch_id = odp.plan_batch_id\r\n   AND odh.delivery_id = :1;\r\n<\/pre>\n<p>SQL will be translated into:<\/p>\n<pre>\r\nSELECT Count(odp.pallet_id) pallet_qty,\r\n       Count(odp.box_id)    box_qty,\r\n       SUM(packing_nw)      sum_packing_nw,\r\n       SUM(packing_gw)      sum_packing_gw,\r\n       SUM(pallet_qty)      sum_packing_qty\r\nFROM   (SELECT f1,\r\n               f2\r\n        FROM   t1) odh,\r\n       xxopsm_delivery_pack_v odp\r\nWHERE  odh.organization_id = :1\r\n       AND odh.organization_id = odp.organization_id\r\n       AND odh.plan_batch_id = odp.plan_batch_id\r\n       AND odh.delivery_id = :1 \r\n<\/pre>\n<p>or <\/p>\n<pre>\r\nSELECT Count(odp.pallet_id) pallet_qty,\r\n       Count(odp.box_id)    box_qty,\r\n       SUM(packing_nw)      sum_packing_nw,\r\n       SUM(packing_gw)      sum_packing_gw,\r\n       SUM(pallet_qty)      sum_packing_qty\r\nFROM   xxopsm_delivery_headers odh,\r\n       (SELECT xdh.organization_id            AS organization_id,\r\n               xdpp.plan_batch_id             AS plan_batch_id,\r\n               'PALLET'                       AS packing_div,\r\n               xdpp.pallet_id                 AS pallet_id,\r\n               NULL                           AS box_id,\r\n               1                              AS packing_qty,\r\n               xdpp.packing_pallet_net_weight AS packing_nw,\r\n               xdpp.total_weight              AS packing_gw,\r\n               xdpp.pallet_qty                AS pallet_qty,\r\n               xdpp.attribute1                AS part_no,\r\n               xdpp.attribute2                AS part_cnt\r\n        FROM   xxopsm_delivery_headers xdh,\r\n               xxopsm_dv_plt_packings xdpp\r\n        WHERE  xdh.plan_batch_id = xdpp.plan_batch_id\r\n        UNION\r\n        SELECT xdh.organization_id        AS organization_id,\r\n               xdb.plan_batch_id          AS plan_batch_id,\r\n               'BOX'                      AS packing_div,\r\n               NULL                       AS pallet_id,\r\n               xdb.to_box_no              AS box_id,\r\n               1                          AS packing_qty,\r\n               xdb.packing_box_net_weight AS packing_nw,\r\n               xdb.total_weight           AS packing_gw,\r\n               xdb.box_packing_qty        AS pallet_qty,\r\n               xdb.attribute1             AS part_no,\r\n               xdb.attribute2             AS part_cnt\r\n        FROM   xxopsm_delivery_headers xdh,\r\n               xxopsm_delivery_boxes xdb\r\n        WHERE  xdh.plan_batch_id = xdb.plan_batch_id\r\n               AND NOT EXISTS (SELECT 'x'\r\n                               FROM   xxopsm_delivery_headers xdh,\r\n                                      xxopsm_dv_plt_packings xdpp\r\n                               WHERE  xdh.plan_batch_id = xdpp.plan_batch_id\r\n                                      AND xdpp.plan_batch_id = xdb.plan_batch_id)) odp\r\nWHERE  odh.organization_id = :1\r\n       AND odh.organization_id = odp.organization_id\r\n       AND odh.plan_batch_id = odp.plan_batch_id\r\n       AND odh.delivery_id = :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>VIEW expansion If object used in FROM clause is object VIEW, then extract view text from database and replace view name with view text. This is an article of Oracle SQL Query rewrite serials. Take this sql for example: SELECT COUNT(odp.pallet_id) pallet_qty, COUNT(odp.box_id) box_qty, SUM(packing_nw) sum_packing_nw, SUM(packing_gw) sum_packing_gw, SUM(pallet_qty) sum_packing_qty FROM xxopsm_delivery_headers odh, xxopsm_delivery_pack_v odp WHERE odh.organization_id = :1 AND odh.organization_id = odp.organization_id AND odh.plan_batch_id = odp.plan_batch_id AND odh.delivery_id = :1; SQL will be translated into: SELECT Count(odp.pallet_id) pallet_qty, Count(odp.box_id) box_qty, SUM(packing_nw) sum_packing_nw, SUM(packing_gw) sum_packing_gw, SUM(pallet_qty) sum_packing_qty FROM (SELECT f1, f2 FROM t1) odh, xxopsm_delivery_pack_v odp WHERE odh.organization_id = :1\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: VIEW expansion\" \/>\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-view-expansion\/\" \/>\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: VIEW expansion\" \/>\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-view-expansion\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and Data Blog\" \/>\n<meta property=\"article:modified_time\" content=\"2012-07-10T07:13:12+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=\"2 minutes\" \/>\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-view-expansion\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/oracle-sql-query-rewrite-view-expansion\/\",\"name\":\"Help you to make better use of General SQL Parser\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\"},\"datePublished\":\"2012-07-10T02:56:05+00:00\",\"dateModified\":\"2012-07-10T07:13:12+00:00\",\"description\":\"Oracle SQL Query rewrite: VIEW expansion\",\"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-view-expansion\/#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-view-expansion\/\"]}]},{\"@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-view-expansion\/#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: VIEW expansion\"}]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Help you to make better use of General SQL Parser","description":"Oracle SQL Query rewrite: VIEW expansion","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-view-expansion\/","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: VIEW expansion","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-view-expansion\/","og_site_name":"SQL and Data Blog","article_modified_time":"2012-07-10T07:13:12+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"2 minutes"},"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-view-expansion\/","url":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/oracle-sql-query-rewrite-view-expansion\/","name":"Help you to make better use of General SQL Parser","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/#website"},"datePublished":"2012-07-10T02:56:05+00:00","dateModified":"2012-07-10T07:13:12+00:00","description":"Oracle SQL Query rewrite: VIEW expansion","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-view-expansion\/#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-view-expansion\/"]}]},{"@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-view-expansion\/#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: VIEW expansion"}]}]}},"_links":{"self":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/1482"}],"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=1482"}],"version-history":[{"count":4,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/1482\/revisions"}],"predecessor-version":[{"id":1516,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/1482\/revisions\/1516"}],"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=1482"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}