{"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":"

VIEW expansion<\/p>\n

If object used in FROM clause is object VIEW, then extract view text from database and replace view name with view text. <\/p>\n

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

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

    \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>\nSQL will be translated into:<\/p>\n\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>\nor <\/p>\n\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>\nDownload this demo: C# version<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"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":"\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