Re: How to rewrite the query using with Clause


"balu" <krishna000@xxxxxxxxx> wrote in message
news:c4c5b770-5969-44f1-b25d-8d526305a1a2@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Dec 23, 6:58 pm, ddf <orat...@xxxxxxx> wrote:
Comments embedded.

On Dec 23, 5:39 am, balu <krishna...@xxxxxxxxx> wrote:

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.

What leads you to believe the performance of the current query is
bad? What evidence can you present to prove that claim?
And what brought you to the conclusion that using the WITH clause
would help with this?

If you know enough to ask about the WITH clause you know enough to
rewrite the query yourself using it.





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

David Fitzjarrell

Hi,

If you observe query we have used 2 multiple times the same query on
the tables , i just want to avoid where every necessary . i Can paste
the explain plan for your better understanding.

Regards

Bala

If you want to use the with clause go to http://www.psoug.org/library.html
some nice examples. Give it a whirl first. The first think I notice though
is TRUNC (wnd.confirm_date) >= :b4 might not be very effecient. I believe
it has to look at each row to decide that (function on a column).
Jim


.

Relevant Pages

  • Re: Using Flattened table COSTLIER than using JOINS with the SETUP tables.
    ... tables and also attaching the Explain plan statements for the query ... Next Execution plan without PSOPRALIAS:- is 13135 ... DECODE (TRIM (c.jp_draft_to_status), ...
    (comp.databases.oracle.tools)
  • Re: Need help with one query--ASAP
    ... If I use the 'in' clause it returns only 1 ... Now what this query returns is one row per emp_no i.e. ... Placing the counter into an inline view, and then using DECODE to ... specify the maximum value for the counter based on the value of the ...
    (comp.databases.oracle.misc)
  • I need to compare a value from one table and return a result from another ie Decode
    ... I am trying to use the design view in Access XP to construct a query and ... each column of the query with the code_decode table and then returning ... Table name decode ...
    (microsoft.public.access.gettingstarted)
  • I need to compare a value from one table and return a result from another ie Decode
    ... I am trying to use the design view in Access XP to construct a query and ... each column of the query with the code_decode table and then returning ... Table name decode ...
    (microsoft.public.access.gettingstarted)
  • Re: Query Assistance...
    ... I think john is saying that if the single letter code is 'u' then ... Code Status (aka "Text Decode Value") ... need to bring in this new table in the query along with your other ... two tables on the single-letter code fields, ...
    (microsoft.public.access.gettingstarted)
Loading