Oracle SQL Query rewrite: VIEW expansion

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
       AND odh.organization_id = odp.organization_id
       AND odh.plan_batch_id = odp.plan_batch_id
       AND odh.delivery_id = :1 

or

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,
       (SELECT xdh.organization_id            AS organization_id,
               xdpp.plan_batch_id             AS plan_batch_id,
               'PALLET'                       AS packing_div,
               xdpp.pallet_id                 AS pallet_id,
               NULL                           AS box_id,
               1                              AS packing_qty,
               xdpp.packing_pallet_net_weight AS packing_nw,
               xdpp.total_weight              AS packing_gw,
               xdpp.pallet_qty                AS pallet_qty,
               xdpp.attribute1                AS part_no,
               xdpp.attribute2                AS part_cnt
        FROM   xxopsm_delivery_headers xdh,
               xxopsm_dv_plt_packings xdpp
        WHERE  xdh.plan_batch_id = xdpp.plan_batch_id
        UNION
        SELECT xdh.organization_id        AS organization_id,
               xdb.plan_batch_id          AS plan_batch_id,
               'BOX'                      AS packing_div,
               NULL                       AS pallet_id,
               xdb.to_box_no              AS box_id,
               1                          AS packing_qty,
               xdb.packing_box_net_weight AS packing_nw,
               xdb.total_weight           AS packing_gw,
               xdb.box_packing_qty        AS pallet_qty,
               xdb.attribute1             AS part_no,
               xdb.attribute2             AS part_cnt
        FROM   xxopsm_delivery_headers xdh,
               xxopsm_delivery_boxes xdb
        WHERE  xdh.plan_batch_id = xdb.plan_batch_id
               AND NOT EXISTS (SELECT 'x'
                               FROM   xxopsm_delivery_headers xdh,
                                      xxopsm_dv_plt_packings xdpp
                               WHERE  xdh.plan_batch_id = xdpp.plan_batch_id
                                      AND xdpp.plan_batch_id = xdb.plan_batch_id)) 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 

Download this demo: C# version