Skip to content
No results
We use cookies to ensure that we give you the best experience on our website.

# 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