How to rewrite the query using with Clause

From: balu <krishna000_at_gmail.com>
Date: Tue, 23 Dec 2008 03:39:05 -0800 (PST)
Message-ID: <b23968c0-4e22-43d9-bf42-cc10c745307a@g1g2000pra.googlegroups.com>

Hi,

Can any body help me out in rewriting the query using with clause where ever necessary or any method which suits for better performance.

SELECT distinct (SELECT ood.organization_name

          FROM org_organization_definitions ood
         WHERE ood.operating_unit = ood.organization_id
           AND ood.operating_unit = oeh.org_id) "OPERATING_UNIT",


       (SELECT organization_name

          FROM org_organization_definitions
         WHERE organization_id = wnd.organization_id) "ORGANIZATION",


       (SELECT location_code

          FROM hr_locations
         WHERE location_id = wsh_loc_hdr.location_id) "LOCATION",
       oeh.order_number "ORDER NUMBER", wnd.delivery_id "DELIVERY NO",
       TO_CHAR (wnd.confirm_date) "CONFIRM DATE",


       (SELECT NAME

          FROM oe_transaction_types_tl
         WHERE transaction_type_id = oeh.order_type_id) "ORDER TYPE",
       ott.attribute1 "NATURE OF REMOVAL", msik.concatenated_segments,
       wdd.subinventory,
       DECODE ((SELECT COUNT (1)
                  FROM jai_om_oe_bond_reg_hdrs jai_hd,
                       jai_om_oe_bond_reg_dtls jai_dt
                 WHERE jai_hd.organization_id = wdd.organization_id
                   AND jai_hd.location_id = :b1
                   AND jai_hd.register_id = jai_dt.register_id
                   AND jai_dt.order_type_id = oeh.order_type_id),
               0, 'ORDER
TYPE NOT ATTACHED',
               1, 'ORDER TYPE ATTACHED',
               'COUNT > 1'
              ) exception1,
       DECODE ((SELECT COUNT (1)
                  FROM jai_inv_itm_setups jai_itm
                 WHERE jai_itm.inventory_item_id =
msik.inventory_item_id
                   AND jai_itm.organization_id =
msik.organization_id),
               0, 'TEMPLATE NOT ASSIGNED',
               1, 'TEMPLATE
ASSIGNED',
               'COUNT > 1'
              ) exception2,


       (SELECT jai_itm.item_class

          FROM jai_inv_itm_setups jai_itm
         WHERE jai_itm.inventory_item_id = msik.inventory_item_id
           AND jai_itm.organization_id = msik.organization_id)
exception3,


       (SELECT jai_itm.excise_flag

          FROM jai_inv_itm_setups jai_itm
         WHERE jai_itm.inventory_item_id = msik.inventory_item_id
           AND jai_itm.organization_id = msik.organization_id)
exception4,
       DECODE ((SELECT COUNT (1)
                  FROM jai_inv_subinv_dtls loc_sub
                 WHERE 1 = 1
                   AND loc_sub.organization_id = wdd.organization_id
                   AND loc_sub.location_id = :location_id
                   AND loc_sub.sub_inventory_name = wdd.subinventory
                   AND loc_sub.bonded = 'Y'),
               0, 'SUN INV NOT ATTACHED/NOT
BONDED',
               1, 'SUB INV ATTACHED',
               'COUNT > 1'
              ) exception5
  FROM wsh_new_deliveries wnd,
       wsh_delivery_assignments wda,
       wsh_delivery_details wdd,
       oe_order_headers_all oeh,
       oe_transaction_types_all ott,
       mtl_system_items_kfv msik,
       jai_om_wsh_lines_all wsh_loc_hdr,
       jai_om_wsh_line_taxes wsh_loc_lin,
       jai_cmn_taxes_all btax

 WHERE 1 = 1
   AND UPPER (btax.tax_type) LIKE '%EXCISE%'    AND btax.tax_id = wsh_loc_lin.tax_id

   AND wsh_loc_lin.delivery_detail_id = wsh_loc_hdr.delivery_detail_id
   AND wsh_loc_hdr.location_id = :b2
   AND wsh_loc_hdr.delivery_detail_id = wdd.delivery_detail_id

   AND msik.organization_id = wdd.organization_id    AND msik.inventory_item_id = wdd.inventory_item_id

   AND ott.transaction_type_id = oeh.order_type_id
   AND oeh.transactional_curr_code = 'INR'
   AND oeh.org_id = :org_id
   AND oeh.ship_from_org_id = wdd.organization_id
   AND oeh.header_id = wdd.source_header_id
   AND wdd.organization_id = wnd.organization_id
   AND wdd.org_id = :b3
   AND wdd.delivery_detail_id = wda.delivery_detail_id
   AND wnd.delivery_id = wda.delivery_id
   AND wnd.status_code = 'CL'

   AND TRUNC (wnd.confirm_date) >= :b4
   AND TRUNC (wnd.confirm_date) <= :b5

Regards

Bala Received on Tue Dec 23 2008 - 05:39:05 CST