Home » RDBMS Server » Performance Tuning » How to remove left join and ON JOIN from query and use better things in place of that (11i)
How to remove left join and ON JOIN from query and use better things in place of that [message #619255] |
Mon, 21 July 2014 01:15 |
|
nishantranjan00787
Messages: 36 Registered: July 2014 Location: india
|
Member |
|
|
There is one query and it using Left Join and ON JOIN and it's a old model of writing code.Now as it's causing performance issue.So we need to rewrite the code.The main problem is how to remove that left join and ON join and use better query in place of that.I am unable to pull out the trace file as when i was taking session level trace,after 2 hr automatically it got terminated.
SELECT *
FROM (WITH a AS
(SELECT c.LOCATION, c.site_use_code, p.party_site_number
FROM apps.hz_cust_site_uses_all c JOIN apps.hz_cust_acct_sites_all s
ON s.cust_acct_site_id = c.cust_acct_site_id
AND s.org_id = c.org_id
JOIN apps.hz_party_sites p
ON p.party_site_id = s.party_site_id
WHERE c.org_id = 4680 AND c.status = 'A'),
b AS
(SELECT h.org_id, h.header_id, h.line_id,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END
) AS h2011,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END
) AS h0003,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END
) AS h2021,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END
) AS h0060,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END
) AS h2035,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END
) AS h2421,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END
) AS h2422,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END
) AS h2423
FROM apps.oe_order_holds_all h LEFT JOIN apps.oe_hold_sources_all s
ON s.org_id = h.org_id
AND s.hold_source_id = h.hold_source_id
AND s.hold_id IN
(2011,
3,
2021,
60,
2035,
2421,
2422,
2423
)
WHERE h.org_id = 4680 AND h.released_flag =
'N'
GROUP BY h.org_id, h.header_id, h.line_id),
v AS
(SELECT l.org_id, l.header_id, l.line_number,
MAX (n.last_update_date) AS ddu,
COUNT (DISTINCT (c.meaning)) AS ddc,
MAX (c.meaning) AS dds,
COUNT (DISTINCT (h.meaning)) AS dlc,
MAX (h.meaning) AS dls
FROM apps.oe_order_lines_all l JOIN apps.wsh_delivery_details d
ON d.org_id = l.org_id
AND d.source_header_id = l.header_id
AND d.source_line_id = l.line_id
JOIN apps.wsh_delivery_assignments m
ON m.delivery_detail_id = d.delivery_detail_id
JOIN apps.wsh_new_deliveries n
ON n.organization_id = l.ship_from_org_id
AND n.delivery_id = m.delivery_id
JOIN apps.fnd_lookup_values c
ON c.lookup_code = d.released_status
AND c.lookup_type = 'PICK_STATUS'
AND c.LANGUAGE = 'US'
JOIN apps.fnd_lookup_values h
ON h.lookup_code = n.status_code
AND h.lookup_type = 'DELIVERY_STATUS'
AND h.LANGUAGE = 'US'
WHERE l.org_id = 4680
AND l.item_type_code IN ('STANDARD', 'CONFIG')
GROUP BY l.org_id, l.header_id, l.line_number
ORDER BY l.org_id, l.header_id, l.line_number)
SELECT h.order_number AS o_order_nr, i.NAME AS o_order_type,
l.line_number AS o_ln_nr,
l.orig_sys_document_ref AS o_orig_sys_document_ref,
l.orig_sys_line_ref AS o_ln_orig_sys_line_ref,
p.orig_sys_line_ref AS o_sato_orig_sys_line_ref,
NVL (p.orig_sys_line_ref,
l.orig_sys_line_ref
) AS o_ln_full_line_ref,
l.ordered_item AS o_ln_ordered_item,
p.model_string AS o_sato_model,
NVL (NVL (t.model_string, p.model_string),
l.ordered_item
) AS o_ln_full_model,
p.pato_parent_item AS o_sato_parent_item,
p.component_item AS o_sato_component_item,
h.flow_status_code AS o_hd_flow_status,
l.flow_status_code AS o_ln_flow_status,
DECODE (v.ddc,
NULL, 'NULL',
NULL, v.dds,
'NULL'
) AS o_ln_pick_status,
DECODE (v.dlc,
NULL, 'NULL',
NULL, v.dls,
'NULL'
) AS o_ln_delivery_status,
b.h2011 AS o_ln_configuration_hold,
b.h0003 AS o_ln_conf_validation_hold,
b.h2021 AS o_ln_calibration_hold,
b.h0060 AS o_ln_conf_exception_hold,
b.h2035 AS o_ln_auto_conf_hold,
b.h2421 AS o_ln_engineering_hold,
b.h2422 AS o_ln_scheduling_hold,
b.h2423 AS o_ln_invoicing_hold,
CASE
WHEN TRUNC (l.promise_date) =
TRUNC (l.creation_date)
+ NULL
THEN 'NULL'
ELSE 'NULL'
END AS o_ln_hub_promised,
CASE
WHEN TRUNC (l.schedule_ship_date) =
TRUNC (l.creation_date)
+ NULL
THEN 'NULL'
ELSE 'NULL'
END AS o_ln_hub_scheduled,
h.creation_date AS o_hd_creation_dt,
p.creation_date AS o_sato_creation_dt,
l.creation_date AS o_ln_creation_dt,
v.ddu AS o_ln_delivery_update_dt,
s.subscriber_name AS o_hd_subscriber_name,
r.q_tot AS o_ln_quantity, r.q_split AS o_ln_split_count,
l.order_quantity_uom AS o_ln_uom,
h.transactional_curr_code AS o_hd_trans_currency,
ROUND
(CASE
WHEN l.top_model_line_id IS NULL
THEN NVL (l.unit_selling_price, NULL)
* r.q_tot
WHEN p.component_item IS NULL
THEN (SELECT SUM ( NVL (q.unit_selling_price,
NULL
)
* r.q_tot
)
FROM apps.oe_order_lines_all q
WHERE q.org_id = l.org_id
AND q.header_id = l.header_id
AND q.top_model_line_id = l.line_id)
ELSE (SELECT NVL (q.unit_selling_price, NULL)
* r.q_tot
FROM apps.oe_order_lines_all q
WHERE q.org_id = l.org_id
AND q.header_id = l.header_id
AND q.top_model_line_id = l.top_model_line_id
AND q.ordered_item = p.component_item
AND ROWNUM = 1)
END,
NULL
) AS o_ln_extended_price,
ROUND
(CASE
WHEN l.item_type_code = 'NULL'
THEN NVL (c.item_cost, NULL) * r.q_tot
WHEN l.item_type_code = 'NULL'
THEN (SELECT NVL (v.item_cost, NULL) * r.q_tot
FROM apps.oe_order_lines_all q JOIN apps.cst_item_cost_type_v v
ON v.organization_id =
q.ship_from_org_id
AND v.inventory_item_id =
q.inventory_item_id
AND v.cost_type = 'Frozen'
WHERE q.org_id = l.org_id
AND q.header_id = l.header_id
AND q.top_model_line_id = l.line_id
AND q.item_type_code = 'CONFIG')
END,
NULL
) AS o_ln_extended_cost,
h.cust_po_number AS o_hd_customer_po,
h.fob_point_code AS o_hd_shipping_terms,
u.rep_order_nbr AS o_hd_representative_order,
u.project_number AS o_hd_project_nr, u.sic_code AS o_hd_sic,
u.ultimate_dest AS o_hd_ultimate_dest,
h.end_customer_site_use_id AS o_hd_end_user_nr,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION =
h.end_customer_site_use_id)
AS o_hd_end_user_psn,
h.ship_to_org_id AS o_hd_ship_to_org_id,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION = h.ship_to_org_id
AND a.site_use_code = 'SHIP_TO') AS o_hd_ship_to_psn,
h.invoice_to_org_id AS o_hd_bill_to_org_id,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION = h.invoice_to_org_id
AND a.site_use_code = 'BILL_TO') AS o_hd_bill_to_psn,
u.named_place AS o_hd_named_place,
t.shiptoaddr AS o_ln_ship_to_nr,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION = t.shiptoaddr
AND a.site_use_code = 'SHIP_TO') AS o_ln_ship_to_psn,
u.po_originated AS o_hd_cust_po_dt,
u.poreceiptdate AS o_hd_branch_po_received_dt,
u.requested_delivery_date AS o_hd_cust_req_delivery_dt,
t.requested_delivery_date AS o_ln_cust_req_delivery_dt,
l.request_date AS o_ln_branch_req_ship_dt,
GREATEST (l.promise_date,
TO_DATE ('NULL', 'NULL')
) AS o_ln_plant_prom_ship_dt,
l.schedule_ship_date AS o_ln_plant_sched_ship_dt,
t.creation_date AS o_ln_3lp_creation_dt,
t.model_string AS o_ln_3lp_model,
t.mse_config_status AS o_ln_3lp_mse_config_status
FROM apps.oe_order_lines_all l
JOIN
(SELECT org_id, header_id, line_number,
SUM (ordered_quantity) AS q_tot, COUNT
(*) AS q_split
FROM apps.oe_order_lines_all
WHERE org_id = 4680
AND item_type_code IN ('MODEL', 'STANDARD')
GROUP BY org_id, header_id, line_number) r
ON r.org_id = l.org_id
AND r.header_id = l.header_id
AND r.line_number = l.line_number
JOIN apps.oe_order_headers_all h
ON h.org_id = l.org_id AND h.header_id = l.header_id
LEFT JOIN
(SELECT DISTINCT *
FROM xxom.xxom_xml1_pato_stg) p
ON p.config_hdr_id = l.config_header_id
LEFT JOIN xxom.xxom_3lp_sym_ora_order_lines t
ON t.org_id = l.org_id
AND t.header_id = l.header_id
AND t.line_id = l.line_id
LEFT JOIN xxom.xxom_3lp_sym_ora_order_hdr u
ON u.org_id = l.org_id AND u.header_id = l.header_id
LEFT JOIN apps.xxont_som_scheduler s
ON s.subscriber_id = u.order_admin
LEFT JOIN apps.oe_transaction_types_tl i
ON i.transaction_type_id = h.order_type_id
AND i.LANGUAGE = 'US'
LEFT JOIN apps.cst_item_cost_type_v c
ON c.organization_id = l.ship_from_org_id
AND c.inventory_item_id = l.inventory_item_id
AND c.cost_type = 'Frozen'
LEFT JOIN b
ON b.org_id = l.org_id
AND b.header_id = l.header_id
AND b.line_id = l.line_id
LEFT JOIN v
ON v.org_id = l.org_id
AND v.header_id = l.header_id
AND v.line_number = l.line_number
WHERE l.org_id = 4680
AND l.item_type_code IN ('MODEL', 'STANDARD')
AND SUBSTR (l.orig_sys_line_ref, 1, 18) != 'OE_ORDER_LINES_ALL')
|
|
|
|
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619260 is a reply to message #619256] |
Mon, 21 July 2014 01:45 |
|
nishantranjan00787
Messages: 36 Registered: July 2014 Location: india
|
Member |
|
|
SELECT *
FROM (WITH a AS
(SELECT c.LOCATION, c.site_use_code, p.party_site_number
FROM apps.hz_cust_site_uses_all c JOIN apps.hz_cust_acct_sites_all s
ON s.cust_acct_site_id = c.cust_acct_site_id
AND s.org_id = c.org_id
JOIN apps.hz_party_sites p
ON p.party_site_id = s.party_site_id
WHERE c.org_id = 4680 AND c.status = 'A'),
b AS
(SELECT h.org_id, h.header_id, h.line_id,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END
) AS h2011,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END
) AS h0003,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END
) AS h2021,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END
) AS h0060,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END
) AS h2035,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END
) AS h2421,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END
) AS h2422,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END
) AS h2423
FROM apps.oe_order_holds_all h LEFT JOIN apps.oe_hold_sources_all s
ON s.org_id = h.org_id
AND s.hold_source_id = h.hold_source_id
AND s.hold_id IN
(2011,
3,
2021,
60,
2035,
2421,
2422,
2423
)
WHERE h.org_id = 4680 AND h.released_flag =
'N'
GROUP BY h.org_id, h.header_id, h.line_id),
v AS
(SELECT l.org_id, l.header_id, l.line_number,
MAX (n.last_update_date) AS ddu,
COUNT (DISTINCT (c.meaning)) AS ddc,
MAX (c.meaning) AS dds,
COUNT (DISTINCT (h.meaning)) AS dlc,
MAX (h.meaning) AS dls
FROM apps.oe_order_lines_all l JOIN apps.wsh_delivery_details d
ON d.org_id = l.org_id
AND d.source_header_id = l.header_id
AND d.source_line_id = l.line_id
JOIN apps.wsh_delivery_assignments m
ON m.delivery_detail_id = d.delivery_detail_id
JOIN apps.wsh_new_deliveries n
ON n.organization_id = l.ship_from_org_id
AND n.delivery_id = m.delivery_id
JOIN apps.fnd_lookup_values c
ON c.lookup_code = d.released_status
AND c.lookup_type = 'PICK_STATUS'
AND c.LANGUAGE = 'US'
JOIN apps.fnd_lookup_values h
ON h.lookup_code = n.status_code
AND h.lookup_type = 'DELIVERY_STATUS'
AND h.LANGUAGE = 'US'
WHERE l.org_id = 4680
AND l.item_type_code IN ('STANDARD', 'CONFIG')
GROUP BY l.org_id, l.header_id, l.line_number
ORDER BY l.org_id, l.header_id, l.line_number)
SELECT h.order_number AS o_order_nr, i.NAME AS o_order_type,
l.line_number AS o_ln_nr,
l.orig_sys_document_ref AS o_orig_sys_document_ref,
l.orig_sys_line_ref AS o_ln_orig_sys_line_ref,
p.orig_sys_line_ref AS o_sato_orig_sys_line_ref,
NVL (p.orig_sys_line_ref,
l.orig_sys_line_ref
) AS o_ln_full_line_ref,
l.ordered_item AS o_ln_ordered_item,
p.model_string AS o_sato_model,
NVL (NVL (t.model_string, p.model_string),
l.ordered_item
) AS o_ln_full_model,
p.pato_parent_item AS o_sato_parent_item,
p.component_item AS o_sato_component_item,
h.flow_status_code AS o_hd_flow_status,
l.flow_status_code AS o_ln_flow_status,
DECODE (v.ddc,
NULL, 'NULL',
NULL, v.dds,
'NULL'
) AS o_ln_pick_status,
DECODE (v.dlc,
NULL, 'NULL',
NULL, v.dls,
'NULL'
) AS o_ln_delivery_status,
b.h2011 AS o_ln_configuration_hold,
b.h0003 AS o_ln_conf_validation_hold,
b.h2021 AS o_ln_calibration_hold,
b.h0060 AS o_ln_conf_exception_hold,
b.h2035 AS o_ln_auto_conf_hold,
b.h2421 AS o_ln_engineering_hold,
b.h2422 AS o_ln_scheduling_hold,
b.h2423 AS o_ln_invoicing_hold,
CASE
WHEN TRUNC (l.promise_date) =
TRUNC (l.creation_date)
+ NULL
THEN 'NULL'
ELSE 'NULL'
END AS o_ln_hub_promised,
CASE
WHEN TRUNC (l.schedule_ship_date) =
TRUNC (l.creation_date)
+ NULL
THEN 'NULL'
ELSE 'NULL'
END AS o_ln_hub_scheduled,
h.creation_date AS o_hd_creation_dt,
p.creation_date AS o_sato_creation_dt,
l.creation_date AS o_ln_creation_dt,
v.ddu AS o_ln_delivery_update_dt,
s.subscriber_name AS o_hd_subscriber_name,
r.q_tot AS o_ln_quantity, r.q_split AS o_ln_split_count,
l.order_quantity_uom AS o_ln_uom,
h.transactional_curr_code AS o_hd_trans_currency,
ROUND
(CASE
WHEN l.top_model_line_id IS NULL
THEN NVL (l.unit_selling_price, NULL)
* r.q_tot
WHEN p.component_item IS NULL
THEN (SELECT SUM ( NVL (q.unit_selling_price,
NULL
)
* r.q_tot
)
FROM apps.oe_order_lines_all q
WHERE q.org_id = l.org_id
AND q.header_id = l.header_id
AND q.top_model_line_id = l.line_id)
ELSE (SELECT NVL (q.unit_selling_price, NULL)
* r.q_tot
FROM apps.oe_order_lines_all q
WHERE q.org_id = l.org_id
AND q.header_id = l.header_id
AND q.top_model_line_id = l.top_model_line_id
AND q.ordered_item = p.component_item
AND ROWNUM = 1)
END,
NULL
) AS o_ln_extended_price,
ROUND
(CASE
WHEN l.item_type_code = 'NULL'
THEN NVL (c.item_cost, NULL) * r.q_tot
WHEN l.item_type_code = 'NULL'
THEN (SELECT NVL (v.item_cost, NULL) * r.q_tot
FROM apps.oe_order_lines_all q JOIN apps.cst_item_cost_type_v v
ON v.organization_id =
q.ship_from_org_id
AND v.inventory_item_id =
q.inventory_item_id
AND v.cost_type = 'Frozen'
WHERE q.org_id = l.org_id
AND q.header_id = l.header_id
AND q.top_model_line_id = l.line_id
AND q.item_type_code = 'CONFIG')
END,
NULL
) AS o_ln_extended_cost,
h.cust_po_number AS o_hd_customer_po,
h.fob_point_code AS o_hd_shipping_terms,
u.rep_order_nbr AS o_hd_representative_order,
u.project_number AS o_hd_project_nr, u.sic_code AS o_hd_sic,
u.ultimate_dest AS o_hd_ultimate_dest,
h.end_customer_site_use_id AS o_hd_end_user_nr,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION =
h.end_customer_site_use_id)
AS o_hd_end_user_psn,
h.ship_to_org_id AS o_hd_ship_to_org_id,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION = h.ship_to_org_id
AND a.site_use_code = 'SHIP_TO') AS o_hd_ship_to_psn,
h.invoice_to_org_id AS o_hd_bill_to_org_id,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION = h.invoice_to_org_id
AND a.site_use_code = 'BILL_TO') AS o_hd_bill_to_psn,
u.named_place AS o_hd_named_place,
t.shiptoaddr AS o_ln_ship_to_nr,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION = t.shiptoaddr
AND a.site_use_code = 'SHIP_TO') AS o_ln_ship_to_psn,
u.po_originated AS o_hd_cust_po_dt,
u.poreceiptdate AS o_hd_branch_po_received_dt,
u.requested_delivery_date AS o_hd_cust_req_delivery_dt,
t.requested_delivery_date AS o_ln_cust_req_delivery_dt,
l.request_date AS o_ln_branch_req_ship_dt,
GREATEST (l.promise_date,
TO_DATE ('NULL', 'NULL')
) AS o_ln_plant_prom_ship_dt,
l.schedule_ship_date AS o_ln_plant_sched_ship_dt,
t.creation_date AS o_ln_3lp_creation_dt,
t.model_string AS o_ln_3lp_model,
t.mse_config_status AS o_ln_3lp_mse_config_status
FROM apps.oe_order_lines_all l
JOIN
(SELECT org_id, header_id, line_number,
SUM (ordered_quantity) AS q_tot, COUNT
(*) AS q_split
FROM apps.oe_order_lines_all
WHERE org_id = 4680
AND item_type_code IN ('MODEL', 'STANDARD')
GROUP BY org_id, header_id, line_number) r
ON r.org_id = l.org_id
AND r.header_id = l.header_id
AND r.line_number = l.line_number
JOIN apps.oe_order_headers_all h
ON h.org_id = l.org_id AND h.header_id = l.header_id
LEFT JOIN
(SELECT DISTINCT *
FROM xxom.xxom_xml1_pato_stg) p
ON p.config_hdr_id = l.config_header_id
LEFT JOIN xxom.xxom_3lp_sym_ora_order_lines t
ON t.org_id = l.org_id
AND t.header_id = l.header_id
AND t.line_id = l.line_id
LEFT JOIN xxom.xxom_3lp_sym_ora_order_hdr u
ON u.org_id = l.org_id AND u.header_id = l.header_id
LEFT JOIN apps.xxont_som_scheduler s
ON s.subscriber_id = u.order_admin
LEFT JOIN apps.oe_transaction_types_tl i
ON i.transaction_type_id = h.order_type_id
AND i.LANGUAGE = 'US'
LEFT JOIN apps.cst_item_cost_type_v c
ON c.organization_id = l.ship_from_org_id
AND c.inventory_item_id = l.inventory_item_id
AND c.cost_type = 'Frozen'
LEFT JOIN b
ON b.org_id = l.org_id
AND b.header_id = l.header_id
AND b.line_id = l.line_id
LEFT JOIN v
ON v.org_id = l.org_id
AND v.header_id = l.header_id
AND v.line_number = l.line_number
WHERE l.org_id = 4680
AND l.item_type_code IN ('MODEL', 'STANDARD')
AND SUBSTR (l.orig_sys_line_ref, 1, 18) != 'OE_ORDER_LINES_ALL')
|
|
|
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619261 is a reply to message #619260] |
Mon, 21 July 2014 01:59 |
John Watson
Messages: 8962 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
This construct that you use several times is a bug:
CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END
It will always return a null. That will mess up the rest of the query considerably.
You also have thisWHEN l.item_type_code = 'NULL' and thisCASE
WHEN TRUNC (l.promise_date) =
TRUNC (l.creation_date)
+ NULL
THEN 'NULL'
These are basic programming errors that may render the entire query meaningless. Fix them, and then see if you have a performance problem.
--update: my second example above is not necessarily a bug - sorry about that. The others, most definitely, are.
[Updated on: Mon, 21 July 2014 02:04] Report message to a moderator
|
|
|
|
|
|
|
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619277 is a reply to message #619276] |
Mon, 21 July 2014 03:39 |
|
nishantranjan00787
Messages: 36 Registered: July 2014 Location: india
|
Member |
|
|
can i completely remove that case statement from query?Like:
SELECT h.org_id, h.header_id, h.line_id,
FROM apps.oe_order_holds_all h LEFT JOIN apps.oe_hold_sources_all s
ON s.org_id = h.org_id
AND s.hold_source_id = h.hold_source_id
AND s.hold_id IN
(2011,
3,
2021,
60,
2035,
2421,
2422,
2423
)
WHERE h.org_id = 4680 AND h.released_flag =
'N'
GROUP BY h.org_id, h.header_id, h.line_id
|
|
|
|
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619281 is a reply to message #619278] |
Mon, 21 July 2014 03:54 |
|
nishantranjan00787
Messages: 36 Registered: July 2014 Location: india
|
Member |
|
|
select * from
(
with a as (
select c.location, c.site_use_code, p.party_site_number
from apps.hz_cust_site_uses_all c
join apps.hz_cust_acct_sites_all s on s.cust_acct_site_id = c.cust_acct_site_id and s.org_id = c.org_id
join apps.hz_party_sites p on p.party_site_id = s.party_site_id
where c.org_id = :"SYS_B_00" and c.status = :"SYS_B_01"
), b as (
select
h.org_id, h.header_id, h.line_id,
max(case when s.hold_id = :"SYS_B_02" Then :"SYS_B_03" end) as h2011,
max(case when s.hold_id = :"SYS_B_04" Then :"SYS_B_05" end) as h0003,
max(case when s.hold_id = :"SYS_B_06" Then :"SYS_B_07" end) as h2021,
max(case when s.hold_id = :"SYS_B_08" Then :"SYS_B_09" end) as h0060,
max(case when s.hold_id = :"SYS_B_10" Then :"SYS_B_11" end) as h2035,
max(case when s.hold_id = :"SYS_B_12" Then :"SYS_B_13" end) as h2421,
max(case when s.hold_id = :"SYS_B_14" Then :"SYS_B_15" end) as h2422,
max(case when s.hold_id = :"SYS_B_16" Then :"SYS_B_17" end) as h2423
from apps.oe_order_holds_all h
left join apps.oe_hold_sources_all s on s.org_id = h.org_id and s.hold_source_id = h.hold_source_id and s.hold_id in (:"SYS_B_18", :"SYS_B_19", :"SYS_B_20", :"SYS_B_21", :"SYS_B_22", :"SYS_B_23", :"SYS_B_24", :"SYS_B_25")
where h.org_id = :"SYS_B_26" and h.released_flag = :"SYS_B_27"
group by h.org_id, h.header_id, h.line_id
), v as (
select l.org_id, l.header_id, l.line_number,
max(n.last_update_date) as ddu,
count(distinct(c.meaning)) as ddc,
max(c.meaning) as dds,
count(distinct(h.meaning)) as dlc,
max(h.meaning) as dls
from apps.oe_order_lines_all l
join apps.wsh_delivery_details d on d.org_id = l.org_id and d.source_header_id = l.header_id and d.source_line_id = l.line_id
join apps.wsh_delivery_assignments m on m.delivery_detail_id = d.delivery_detail_id
join apps.wsh_new_deliveries n on n.organization_id = l.ship_from_org_id and n.delivery_id = m.delivery_id
join apps.fnd_lookup_values c on c.lookup_code = d.released_status and c.lookup_type = :"SYS_B_28" and c.language = :"SYS_B_29"
join apps.fnd_lookup_values h on h.lookup_code = n.status_code and h.lookup_type = :"SYS_B_30" and h.language = :"SYS_B_31"
where l.org_id = :"SYS_B_32" and l.item_type_code in (:"SYS_B_33", :"SYS_B_34")
group by l.org_id, l.header_id, l.line_number
order by l.org_id, l.header_id, l.line_number
)
select h.order_number as o_order_nr,
i.name as o_order_type,
l.line_number as o_ln_nr,
l.orig_sys_document_ref as o_orig_sys_document_ref,
l.orig_sys_line_ref as o_ln_orig_sys_line_ref,
p.orig_sys_line_ref as o_sato_orig_sys_line_ref,
nvl(p.orig_sys_line_ref, l.orig_sys_line_ref) as o_ln_full_line_ref,
l.ordered_item as o_ln_ordered_item,
p.model_string as o_sato_model,
nvl(nvl(t.model_string, p.model_string), l.ordered_item) as o_ln_full_model,
p.pato_parent_item as o_sato_parent_item,
p.component_item as o_sato_component_item,
h.flow_status_code as o_hd_flow_status,
l.flow_status_code as o_ln_flow_status,
decode(v.ddc,null,:"SYS_B_35",:"SYS_B_36",v.dds,:"SYS_B_37") as o_ln_pick_status,
decode(v.dlc,null,:"SYS_B_38",:"SYS_B_39",v.dls,:"SYS_B_40") as o_ln_delivery_status,
b.h2011 as o_ln_configuration_hold,
b.h0003 as o_ln_conf_validation_hold,
b.h2021 as o_ln_calibration_hold,
b.h0060 as o_ln_conf_exception_hold,
b.h2035 as o_ln_auto_conf_hold,
b.h2421 as o_ln_engineering_hold,
b.h2422 as o_ln_scheduling_hold,
b.h2423 as o_ln_invoicing_hold,
case when trunc(l.promise_date) = trunc(l.creation_date) + :"SYS_B_41" then :"SYS_B_42" else :"SYS_B_43" end as o_ln_hub_promised,
case when trunc(l.schedule_ship_date) = trunc(l.creation_date) + :"SYS_B_44" then :"SYS_B_45" else :"SYS_B_46" end as o_ln_hub_scheduled,
h.creation_date as o_hd_creation_dt,
p.creation_date as o_sato_creation_dt,
l.creation_date as o_ln_creation_dt,
v.ddu as o_ln_delivery_update_dt,
s.subscriber_name as o_hd_subscriber_name,
r.q_tot as o_ln_quantity,
r.q_split as o_ln_split_count,
l.order_quantity_uom as o_ln_uom,
h.transactional_curr_code as o_hd_trans_currency,
round(case
when l.top_model_line_id is null then nvl(l.unit_selling_price, :"SYS_B_47") * r.q_tot
when p.component_item is null
then (select sum(nvl(q.unit_selling_price, :"SYS_B_48") * r.q_tot) from apps.oe_order_lines_all q
where q.org_id = l.org_id and q.header_id = l.header_id and q.top_model_line_id = l.line_id)
else (select nvl(q.unit_selling_price, :"SYS_B_49") * r.q_tot from apps.oe_order_lines_all q
where q.org_id = l.org_id and q.header_id = l.header_id and q.top_model_line_id = l.top_model_line_id and q.ordered_item = p.component_item and rownum = :"SYS_B_50")
end , :"SYS_B_51") as o_ln_extended_price,
round(case
when l.item_type_code = :"SYS_B_52" then nvl(c.item_cost, :"SYS_B_53") * r.q_tot
when l.item_type_code = :"SYS_B_54"
then (select nvl(v.item_cost, :"SYS_B_55") * r.q_tot from apps.oe_order_lines_all q
join apps.cst_item_cost_type_v v on v.organization_id = q.ship_from_org_id and v.inventory_item_id = q.inventory_item_id and v.cost_type = :"SYS_B_56"
where q.org_id = l.org_id and q.header_id = l.header_id and q.top_model_line_id = l.line_id and q.item_type_code = :"SYS_B_57")
end , :"SYS_B_58") as o_ln_extended_cost,
h.cust_po_number as o_hd_customer_po,
h.fob_point_code as o_hd_shipping_terms,
u.rep_order_nbr as o_hd_representative_order,
u.project_number as o_hd_project_nr,
u.sic_code as o_hd_sic,
u.ultimate_dest as o_hd_ultimate_dest,
h.end_customer_site_use_id as o_hd_end_user_nr,
(select a.party_site_number from a where a.location = h.end_customer_site_use_id) as o_hd_end_user_psn,
h.ship_to_org_id as o_hd_ship_to_org_id,
(select a.party_site_number from a where a.location = h.ship_to_org_id and a.site_use_code = :"SYS_B_59") as o_hd_ship_to_psn,
h.invoice_to_org_id as o_hd_bill_to_org_id,
(select a.party_site_number from a where a.location = h.invoice_to_org_id and a.site_use_code = :"SYS_B_60") as o_hd_bill_to_psn,
u.named_place as o_hd_named_place,
t.shiptoaddr as o_ln_ship_to_nr,
(select a.party_site_number from a where a.location = t.shiptoaddr and a.site_use_code = :"SYS_B_61") as o_ln_ship_to_psn,
u.po_originated as o_hd_cust_po_dt,
u.poreceiptdate as o_hd_branch_po_received_dt,
u.requested_delivery_date as o_hd_cust_req_delivery_dt,
t.requested_delivery_date as o_ln_cust_req_delivery_dt,
l.request_date as o_ln_branch_req_ship_dt,
greatest(l.promise_date, TO_DATE(:"SYS_B_62", :"SYS_B_63")) as o_ln_plant_prom_ship_dt,
l.schedule_ship_date as o_ln_plant_sched_ship_dt,
t.creation_date as o_ln_3lp_creation_dt,
t.model_string as o_ln_3lp_model,
t.mse_config_status as o_ln_3lp_mse_config_status
from
apps.oe_order_lines_all l
join ( select org_id, header_id, line_number, sum(ordered_quantity) as q_tot, count(*) as q_split
from apps.oe_order_lines_all
where org_id = :"SYS_B_64" and item_type_code in (:"SYS_B_65", :"SYS_B_66")
group by org_id, header_id, line_number) r on r.org_id = l.org_id and r.header_id = l.header_id and r.line_number = l.line_number
join apps.oe_order_headers_all h on h.org_id = l.org_id and h.header_id = l.header_id
left join (select distinct * from xxom.xxom_xml1_pato_stg) p on p.config_hdr_id = l.config_header_id
left join xxom.xxom_3lp_sym_ora_order_lines t on t.org_id = l.org_id and t.header_id = l.header_id and t.line_id = l.line_id
left join xxom.xxom_3lp_sym_ora_order_hdr u on u.org_id = l.org_id and u.header_id = l.header_id
left join apps.xxont_som_scheduler s on s.subscriber_id = u.order_admin
left join apps.oe_transaction_types_tl i on i.transaction_type_id = h.order_type_id and i.language = :"SYS_B_67"
left join apps.cst_item_cost_type_v c on c.organization_id = l.ship_from_org_id and c.inventory_item_id = l.inventory_item_id and c.cost_type = :"SYS_B_68"
left join b on b.org_id = l.org_id and b.header_id = l.header_id and b.line_id = l.line_id
left join v on v.org_id = l.org_id and v.header_id = l.header_id and v.line_number = l.line_number
where
l.org_id = :"SYS_B_69"
and l.item_type_code in (:"SYS_B_70", :"SYS_B_71")
and substr(l.orig_sys_line_ref, :"SYS_B_72", :"SYS_B_73") != :"SYS_B_74"
)
|
|
|
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619283 is a reply to message #619281] |
Mon, 21 July 2014 03:55 |
|
nishantranjan00787
Messages: 36 Registered: July 2014 Location: india
|
Member |
|
|
SELECT *
FROM (WITH a AS
(SELECT c.LOCATION, c.site_use_code, p.party_site_number
FROM apps.hz_cust_site_uses_all c JOIN apps.hz_cust_acct_sites_all s
ON s.cust_acct_site_id = c.cust_acct_site_id
AND s.org_id = c.org_id
JOIN apps.hz_party_sites p
ON p.party_site_id = s.party_site_id
WHERE c.org_id = :"SYS_B_00" AND c.status = :"SYS_B_01"),
b AS
(SELECT h.org_id, h.header_id, h.line_id,
MAX (CASE
WHEN s.hold_id = :"SYS_B_02"
THEN :"SYS_B_03"
END
) AS h2011,
MAX (CASE
WHEN s.hold_id = :"SYS_B_04"
THEN :"SYS_B_05"
END
) AS h0003,
MAX (CASE
WHEN s.hold_id = :"SYS_B_06"
THEN :"SYS_B_07"
END
) AS h2021,
MAX (CASE
WHEN s.hold_id = :"SYS_B_08"
THEN :"SYS_B_09"
END
) AS h0060,
MAX (CASE
WHEN s.hold_id = :"SYS_B_10"
THEN :"SYS_B_11"
END
) AS h2035,
MAX (CASE
WHEN s.hold_id = :"SYS_B_12"
THEN :"SYS_B_13"
END
) AS h2421,
MAX (CASE
WHEN s.hold_id = :"SYS_B_14"
THEN :"SYS_B_15"
END
) AS h2422,
MAX (CASE
WHEN s.hold_id = :"SYS_B_16"
THEN :"SYS_B_17"
END
) AS h2423
FROM apps.oe_order_holds_all h LEFT JOIN apps.oe_hold_sources_all s
ON s.org_id = h.org_id
AND s.hold_source_id = h.hold_source_id
AND s.hold_id IN
(:"SYS_B_18",
:"SYS_B_19",
:"SYS_B_20",
:"SYS_B_21",
:"SYS_B_22",
:"SYS_B_23",
:"SYS_B_24",
:"SYS_B_25"
)
WHERE h.org_id = :"SYS_B_26" AND h.released_flag =
:"SYS_B_27"
GROUP BY h.org_id, h.header_id, h.line_id),
v AS
(SELECT l.org_id, l.header_id, l.line_number,
MAX (n.last_update_date) AS ddu,
COUNT (DISTINCT (c.meaning)) AS ddc,
MAX (c.meaning) AS dds,
COUNT (DISTINCT (h.meaning)) AS dlc,
MAX (h.meaning) AS dls
FROM apps.oe_order_lines_all l JOIN apps.wsh_delivery_details d
ON d.org_id = l.org_id
AND d.source_header_id = l.header_id
AND d.source_line_id = l.line_id
JOIN apps.wsh_delivery_assignments m
ON m.delivery_detail_id = d.delivery_detail_id
JOIN apps.wsh_new_deliveries n
ON n.organization_id = l.ship_from_org_id
AND n.delivery_id = m.delivery_id
JOIN apps.fnd_lookup_values c
ON c.lookup_code = d.released_status
AND c.lookup_type = :"SYS_B_28"
AND c.LANGUAGE = :"SYS_B_29"
JOIN apps.fnd_lookup_values h
ON h.lookup_code = n.status_code
AND h.lookup_type = :"SYS_B_30"
AND h.LANGUAGE = :"SYS_B_31"
WHERE l.org_id = :"SYS_B_32"
AND l.item_type_code IN (:"SYS_B_33", :"SYS_B_34")
GROUP BY l.org_id, l.header_id, l.line_number
ORDER BY l.org_id, l.header_id, l.line_number)
SELECT h.order_number AS o_order_nr, i.NAME AS o_order_type,
l.line_number AS o_ln_nr,
l.orig_sys_document_ref AS o_orig_sys_document_ref,
l.orig_sys_line_ref AS o_ln_orig_sys_line_ref,
p.orig_sys_line_ref AS o_sato_orig_sys_line_ref,
NVL (p.orig_sys_line_ref,
l.orig_sys_line_ref
) AS o_ln_full_line_ref,
l.ordered_item AS o_ln_ordered_item,
p.model_string AS o_sato_model,
NVL (NVL (t.model_string, p.model_string),
l.ordered_item
) AS o_ln_full_model,
p.pato_parent_item AS o_sato_parent_item,
p.component_item AS o_sato_component_item,
h.flow_status_code AS o_hd_flow_status,
l.flow_status_code AS o_ln_flow_status,
DECODE (v.ddc,
NULL, :"SYS_B_35",
:"SYS_B_36", v.dds,
:"SYS_B_37"
) AS o_ln_pick_status,
DECODE (v.dlc,
NULL, :"SYS_B_38",
:"SYS_B_39", v.dls,
:"SYS_B_40"
) AS o_ln_delivery_status,
b.h2011 AS o_ln_configuration_hold,
b.h0003 AS o_ln_conf_validation_hold,
b.h2021 AS o_ln_calibration_hold,
b.h0060 AS o_ln_conf_exception_hold,
b.h2035 AS o_ln_auto_conf_hold,
b.h2421 AS o_ln_engineering_hold,
b.h2422 AS o_ln_scheduling_hold,
b.h2423 AS o_ln_invoicing_hold,
CASE
WHEN TRUNC (l.promise_date) =
TRUNC (l.creation_date)
+ :"SYS_B_41"
THEN :"SYS_B_42"
ELSE :"SYS_B_43"
END AS o_ln_hub_promised,
CASE
WHEN TRUNC (l.schedule_ship_date) =
TRUNC (l.creation_date)
+ :"SYS_B_44"
THEN :"SYS_B_45"
ELSE :"SYS_B_46"
END AS o_ln_hub_scheduled,
h.creation_date AS o_hd_creation_dt,
p.creation_date AS o_sato_creation_dt,
l.creation_date AS o_ln_creation_dt,
v.ddu AS o_ln_delivery_update_dt,
s.subscriber_name AS o_hd_subscriber_name,
r.q_tot AS o_ln_quantity, r.q_split AS o_ln_split_count,
l.order_quantity_uom AS o_ln_uom,
h.transactional_curr_code AS o_hd_trans_currency,
ROUND
(CASE
WHEN l.top_model_line_id IS NULL
THEN NVL (l.unit_selling_price, :"SYS_B_47")
* r.q_tot
WHEN p.component_item IS NULL
THEN (SELECT SUM ( NVL (q.unit_selling_price,
:"SYS_B_48"
)
* r.q_tot
)
FROM apps.oe_order_lines_all q
WHERE q.org_id = l.org_id
AND q.header_id = l.header_id
AND q.top_model_line_id = l.line_id)
ELSE (SELECT NVL (q.unit_selling_price, :"SYS_B_49")
* r.q_tot
FROM apps.oe_order_lines_all q
WHERE q.org_id = l.org_id
AND q.header_id = l.header_id
AND q.top_model_line_id = l.top_model_line_id
AND q.ordered_item = p.component_item
AND ROWNUM = :"SYS_B_50")
END,
:"SYS_B_51"
) AS o_ln_extended_price,
ROUND
(CASE
WHEN l.item_type_code = :"SYS_B_52"
THEN NVL (c.item_cost, :"SYS_B_53") * r.q_tot
WHEN l.item_type_code = :"SYS_B_54"
THEN (SELECT NVL (v.item_cost, :"SYS_B_55") * r.q_tot
FROM apps.oe_order_lines_all q JOIN apps.cst_item_cost_type_v v
ON v.organization_id =
q.ship_from_org_id
AND v.inventory_item_id =
q.inventory_item_id
AND v.cost_type = :"SYS_B_56"
WHERE q.org_id = l.org_id
AND q.header_id = l.header_id
AND q.top_model_line_id = l.line_id
AND q.item_type_code = :"SYS_B_57")
END,
:"SYS_B_58"
) AS o_ln_extended_cost,
h.cust_po_number AS o_hd_customer_po,
h.fob_point_code AS o_hd_shipping_terms,
u.rep_order_nbr AS o_hd_representative_order,
u.project_number AS o_hd_project_nr, u.sic_code AS o_hd_sic,
u.ultimate_dest AS o_hd_ultimate_dest,
h.end_customer_site_use_id AS o_hd_end_user_nr,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION =
h.end_customer_site_use_id)
AS o_hd_end_user_psn,
h.ship_to_org_id AS o_hd_ship_to_org_id,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION = h.ship_to_org_id
AND a.site_use_code = :"SYS_B_59") AS o_hd_ship_to_psn,
h.invoice_to_org_id AS o_hd_bill_to_org_id,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION = h.invoice_to_org_id
AND a.site_use_code = :"SYS_B_60") AS o_hd_bill_to_psn,
u.named_place AS o_hd_named_place,
t.shiptoaddr AS o_ln_ship_to_nr,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION = t.shiptoaddr
AND a.site_use_code = :"SYS_B_61") AS o_ln_ship_to_psn,
u.po_originated AS o_hd_cust_po_dt,
u.poreceiptdate AS o_hd_branch_po_received_dt,
u.requested_delivery_date AS o_hd_cust_req_delivery_dt,
t.requested_delivery_date AS o_ln_cust_req_delivery_dt,
l.request_date AS o_ln_branch_req_ship_dt,
GREATEST (l.promise_date,
TO_DATE (:"SYS_B_62", :"SYS_B_63")
) AS o_ln_plant_prom_ship_dt,
l.schedule_ship_date AS o_ln_plant_sched_ship_dt,
t.creation_date AS o_ln_3lp_creation_dt,
t.model_string AS o_ln_3lp_model,
t.mse_config_status AS o_ln_3lp_mse_config_status
FROM apps.oe_order_lines_all l
JOIN
(SELECT org_id, header_id, line_number,
SUM (ordered_quantity) AS q_tot, COUNT
(*) AS q_split
FROM apps.oe_order_lines_all
WHERE org_id = :"SYS_B_64"
AND item_type_code IN (:"SYS_B_65", :"SYS_B_66")
GROUP BY org_id, header_id, line_number) r
ON r.org_id = l.org_id
AND r.header_id = l.header_id
AND r.line_number = l.line_number
JOIN apps.oe_order_headers_all h
ON h.org_id = l.org_id AND h.header_id = l.header_id
LEFT JOIN
(SELECT DISTINCT *
FROM xxom.xxom_xml1_pato_stg) p
ON p.config_hdr_id = l.config_header_id
LEFT JOIN xxom.xxom_3lp_sym_ora_order_lines t
ON t.org_id = l.org_id
AND t.header_id = l.header_id
AND t.line_id = l.line_id
LEFT JOIN xxom.xxom_3lp_sym_ora_order_hdr u
ON u.org_id = l.org_id AND u.header_id = l.header_id
LEFT JOIN apps.xxont_som_scheduler s
ON s.subscriber_id = u.order_admin
LEFT JOIN apps.oe_transaction_types_tl i
ON i.transaction_type_id = h.order_type_id
AND i.LANGUAGE = :"SYS_B_67"
LEFT JOIN apps.cst_item_cost_type_v c
ON c.organization_id = l.ship_from_org_id
AND c.inventory_item_id = l.inventory_item_id
AND c.cost_type = :"SYS_B_68"
LEFT JOIN b
ON b.org_id = l.org_id
AND b.header_id = l.header_id
AND b.line_id = l.line_id
LEFT JOIN v
ON v.org_id = l.org_id
AND v.header_id = l.header_id
AND v.line_number = l.line_number
WHERE l.org_id = :"SYS_B_69"
AND l.item_type_code IN (:"SYS_B_70", :"SYS_B_71")
AND SUBSTR (l.orig_sys_line_ref, :"SYS_B_72", :"SYS_B_73") !=
:"SYS_B_74")
|
|
|
|
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619288 is a reply to message #619284] |
Mon, 21 July 2014 04:19 |
cookiemonster
Messages: 13961 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
nishantranjan00787 wrote on Mon, 21 July 2014 07:15There is one query and it using Left Join and ON JOIN and it's a old model of writing code.
Define old model. JOIN clauses are relatively new.
nishantranjan00787 wrote on Mon, 21 July 2014 07:15
The main problem is how to remove that left join and ON join and use better query in place of that.
You use left join if you want to be able to get data from a query even if some of the tables involved don't have matching data. The only thing faster than an outer (left) join is in inner join, but that changes the meaning of the query. So if you need those joins to be outer joins you can't rewrite that in any way that would improve performance.
On clauses generally should have no performance issue at all.
There are probably things that can be done to improve the performance, but rewriting may not be one of them (or it may be).
Considering the complexity of the query it's almost impossible for us to say anything useful without a lot more information.
An explain plan would be a good start.
|
|
|
|
|
|
|
|
|
|
|
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619299 is a reply to message #619298] |
Mon, 21 July 2014 05:27 |
|
nishantranjan00787
Messages: 36 Registered: July 2014 Location: india
|
Member |
|
|
but john if you have to change the query or removing outer join of the below ,what will be your approach:
SELECT l.org_id, l.header_id, l.line_number,
MAX (n.last_update_date) AS ddu,
COUNT (DISTINCT (c.meaning)) AS ddc,
MAX (c.meaning) AS dds,
COUNT (DISTINCT (h.meaning)) AS dlc,
MAX (h.meaning) AS dls
FROM apps.oe_order_lines_all l JOIN apps.wsh_delivery_details d
ON d.org_id = l.org_id
AND d.source_header_id = l.header_id
AND d.source_line_id = l.line_id
JOIN apps.wsh_delivery_assignments m
ON m.delivery_detail_id = d.delivery_detail_id
JOIN apps.wsh_new_deliveries n
ON n.organization_id = l.ship_from_org_id
AND n.delivery_id = m.delivery_id
JOIN apps.fnd_lookup_values c
ON c.lookup_code = d.released_status
AND c.lookup_type = 'PICK_STATUS'
AND c.LANGUAGE = 'US'
JOIN apps.fnd_lookup_values h
ON h.lookup_code = n.status_code
AND h.lookup_type = 'DELIVERY_STATUS'
AND h.LANGUAGE = 'US'
WHERE l.org_id = 4680
AND l.item_type_code IN ('STANDARD', 'CONFIG')
GROUP BY l.org_id, l.header_id, l.line_number
ORDER BY l.org_id, l.header_id, l.line_number)
|
|
|
|
|
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619302 is a reply to message #619296] |
Mon, 21 July 2014 05:40 |
cookiemonster
Messages: 13961 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
nishantranjan00787 wrote on Mon, 21 July 2014 11:21I HAVE done modification in the top query.Please have a look:
(SELECT c.LOCATION, c.site_use_code, p.party_site_number
FROM apps.hz_cust_site_uses_all c , apps.hz_cust_acct_sites_all s,apps.hz_party_sites p
WHERE s.cust_acct_site_id = c.cust_acct_site_id
AND s.org_id = c.org_id
AND p.party_site_id = s.party_site_id
AND c.org_id = 4680 AND c.status = 'A')
You do realise that the JOIN is an inner-join unless it's preceeded by a LEFT, RIGHT or FULL keyword?
|
|
|
|
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #619313 is a reply to message #619303] |
Mon, 21 July 2014 06:16 |
cookiemonster
Messages: 13961 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
*sigh*
This:
(SELECT c.LOCATION, c.site_use_code, p.party_site_number
FROM apps.hz_cust_site_uses_all c , apps.hz_cust_acct_sites_all s,apps.hz_party_sites p
WHERE s.cust_acct_site_id = c.cust_acct_site_id
AND s.org_id = c.org_id
AND p.party_site_id = s.party_site_id
AND c.org_id = 4680 AND c.status = 'A')
Will perform exactly the same as:
SELECT c.LOCATION, c.site_use_code, p.party_site_number
FROM apps.hz_cust_site_uses_all c JOIN apps.hz_cust_acct_sites_all s
ON s.cust_acct_site_id = c.cust_acct_site_id
AND s.org_id = c.org_id
JOIN apps.hz_party_sites p
ON p.party_site_id = s.party_site_id
WHERE c.org_id = 4680 AND c.status = 'A'
JOIN is not a performance issue. repeat that until you believe it.
Using an outer join when you don't need to is a performance issue, but the above example contains no outer joins.
I suggest you spend some time reading the manuals on joins, since you don't appear to understand what JOIN does.
|
|
|
|
Re: How to remove left join and ON JOIN from query and use better things in place of that [message #620075 is a reply to message #619433] |
Mon, 28 July 2014 10:55 |
|
nishantranjan00787
Messages: 36 Registered: July 2014 Location: india
|
Member |
|
|
I have just rewrite the code to take the session level trace and to tune it.But i am getting error while running
/* Formatted on 2014/07/28 20:01 (Formatter Plus v4.8.8) */
SELECT *
FROM (WITH a AS
(SELECT c.LOCATION, c.site_use_code, p.party_site_number
FROM apps.hz_cust_site_uses_all c,
apps.hz_cust_acct_sites_all s,
apps.hz_party_sites p
WHERE s.cust_acct_site_id = c.cust_acct_site_id
AND s.org_id = c.org_id
AND p.party_site_id = s.party_site_id
AND c.org_id = 4680
AND c.status = 'A'),
b AS
(SELECT h.org_id, h.header_id, h.line_id,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h2011,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h0003,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h2021,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h0060,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h2035,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h2421,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h2422,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h2423
FROM apps.oe_order_holds_all h, apps.oe_hold_sources_all s
WHERE s.org_id = h.org_id
AND s.hold_source_id(+) = h.hold_source_id
AND s.hold_id IN
(2011, 3, 2021, 60, 2035, 2421, 2422, 2423)
AND h.org_id = 4680
AND h.released_flag = 'N'
GROUP BY h.org_id, h.header_id, h.line_id),
v AS
(SELECT l.org_id, l.header_id, l.line_number,
MAX (n.last_update_date) AS ddu,
COUNT (DISTINCT (c.meaning)) AS ddc,
MAX (c.meaning) AS dds,
COUNT (DISTINCT (h.meaning)) AS dlc,
MAX (h.meaning) AS dls
FROM apps.oe_order_lines_all l,
apps.wsh_delivery_details d,
apps.wsh_delivery_assignments m,
apps.wsh_new_deliveries n,
apps.fnd_lookup_values c,
apps.fnd_lookup_values h
WHERE d.org_id = l.org_id
AND d.source_header_id = l.header_id
AND d.source_line_id = l.line_id
AND m.delivery_detail_id = d.delivery_detail_id
AND n.organization_id = l.ship_from_org_id
AND n.delivery_id = m.delivery_id
AND c.lookup_code = d.released_status
AND c.lookup_type = 'PICK_STATUS'
AND c.LANGUAGE = 'US'
AND h.lookup_code = n.status_code
AND h.lookup_type = 'DELIVERY_STATUS'
AND h.LANGUAGE = 'US'
AND l.org_id = 4680
AND l.item_type_code IN ('STANDARD', 'CONFIG')
GROUP BY l.org_id, l.header_id, l.line_number
ORDER BY l.org_id, l.header_id, l.line_number)
SELECT h.order_number AS o_order_nr, i.NAME AS o_order_type,
l.line_number AS o_ln_nr,
l.orig_sys_document_ref AS o_orig_sys_document_ref,
l.orig_sys_line_ref AS o_ln_orig_sys_line_ref,
p.orig_sys_line_ref AS o_sato_orig_sys_line_ref,
NVL (p.orig_sys_line_ref,
l.orig_sys_line_ref
) AS o_ln_full_line_ref,
l.ordered_item AS o_ln_ordered_item,
p.model_string AS o_sato_model,
NVL (NVL (t.model_string, p.model_string),
l.ordered_item
) AS o_ln_full_model,
p.pato_parent_item AS o_sato_parent_item,
p.component_item AS o_sato_component_item,
h.flow_status_code AS o_hd_flow_status,
l.flow_status_code AS o_ln_flow_status,
DECODE (v.ddc,
NULL, 'NULL',
NULL, v.dds,
'NULL'
) AS o_ln_pick_status,
DECODE (v.dlc,
NULL, 'NULL',
NULL, v.dls,
'NULL'
) AS o_ln_delivery_status,
b.h2011 AS o_ln_configuration_hold,
b.h0003 AS o_ln_conf_validation_hold,
b.h2021 AS o_ln_calibration_hold,
b.h0060 AS o_ln_conf_exception_hold,
b.h2035 AS o_ln_auto_conf_hold,
b.h2421 AS o_ln_engineering_hold,
b.h2422 AS o_ln_scheduling_hold,
b.h2423 AS o_ln_invoicing_hold,
CASE
WHEN TRUNC (l.promise_date) =
TRUNC (l.creation_date)
+ NULL
THEN 'NULL'
ELSE 'NULL'
END AS o_ln_hub_promised,
CASE
WHEN TRUNC (l.schedule_ship_date) =
TRUNC (l.creation_date)
+ NULL
THEN 'NULL'
ELSE 'NULL'
END AS o_ln_hub_scheduled,
h.creation_date AS o_hd_creation_dt,
p.creation_date AS o_sato_creation_dt,
l.creation_date AS o_ln_creation_dt,
v.ddu AS o_ln_delivery_update_dt,
s.subscriber_name AS o_hd_subscriber_name,
r.q_tot AS o_ln_quantity, r.q_split AS o_ln_split_count,
l.order_quantity_uom AS o_ln_uom,
h.transactional_curr_code AS o_hd_trans_currency,
ROUND
(CASE
WHEN l.top_model_line_id IS NULL
THEN NVL (l.unit_selling_price, NULL) * r.q_tot
WHEN p.component_item IS NULL
THEN (SELECT SUM ( NVL (q.unit_selling_price, NULL)
* r.q_tot
)
FROM apps.oe_order_lines_all q
WHERE q.org_id = l.org_id
AND q.header_id = l.header_id
AND q.top_model_line_id = l.line_id)
ELSE (SELECT NVL (q.unit_selling_price, NULL) * r.q_tot
FROM apps.oe_order_lines_all q
WHERE q.org_id = l.org_id
AND q.header_id = l.header_id
AND q.top_model_line_id = l.top_model_line_id
AND q.ordered_item = p.component_item
AND ROWNUM = 1)
END,
NULL
) AS o_ln_extended_price,
ROUND
(CASE
WHEN l.item_type_code = 'NULL'
THEN NVL (c.item_cost, NULL) * r.q_tot
WHEN l.item_type_code = 'NULL'
THEN (SELECT NVL (v.item_cost, NULL) * r.q_tot
FROM apps.oe_order_lines_all q,
apps.cst_item_cost_type_v v
WHERE v.organization_id = q.ship_from_org_id
AND v.inventory_item_id =
q.inventory_item_id
AND v.cost_type = 'Frozen'
AND q.org_id = l.org_id
AND q.header_id = l.header_id
AND q.top_model_line_id = l.line_id
AND q.item_type_code = 'CONFIG')
END,
NULL
) AS o_ln_extended_cost,
h.cust_po_number AS o_hd_customer_po,
h.fob_point_code AS o_hd_shipping_terms,
u.rep_order_nbr AS o_hd_representative_order,
u.project_number AS o_hd_project_nr, u.sic_code AS o_hd_sic,
u.ultimate_dest AS o_hd_ultimate_dest,
h.end_customer_site_use_id AS o_hd_end_user_nr,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION =
h.end_customer_site_use_id)
AS o_hd_end_user_psn,
h.ship_to_org_id AS o_hd_ship_to_org_id,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION = h.ship_to_org_id
AND a.site_use_code = 'SHIP_TO') AS o_hd_ship_to_psn,
h.invoice_to_org_id AS o_hd_bill_to_org_id,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION = h.invoice_to_org_id
AND a.site_use_code = 'BILL_TO') AS o_hd_bill_to_psn,
u.named_place AS o_hd_named_place,
t.shiptoaddr AS o_ln_ship_to_nr,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION = t.shiptoaddr
AND a.site_use_code = 'SHIP_TO') AS o_ln_ship_to_psn,
u.po_originated AS o_hd_cust_po_dt,
u.poreceiptdate AS o_hd_branch_po_received_dt,
u.requested_delivery_date AS o_hd_cust_req_delivery_dt,
t.requested_delivery_date AS o_ln_cust_req_delivery_dt,
l.request_date AS o_ln_branch_req_ship_dt,
GREATEST (l.promise_date,
TO_DATE ('NULL', 'NULL')
) AS o_ln_plant_prom_ship_dt,
l.schedule_ship_date AS o_ln_plant_sched_ship_dt,
t.creation_date AS o_ln_3lp_creation_dt,
t.model_string AS o_ln_3lp_model,
t.mse_config_status AS o_ln_3lp_mse_config_status
FROM apps.oe_order_lines_all l,
apps.oe_order_headers_all h,
xxom.xxom_3lp_sym_ora_order_lines t,
xxom.xxom_3lp_sym_ora_order_hdr u,
apps.xxont_som_scheduler s,
apps.oe_transaction_types_tl i,
apps.cst_item_cost_type_v c,
(SELECT DISTINCT *
FROM xxom.xxom_xml1_pato_stg) p,
(SELECT org_id, header_id, line_number,
SUM (ordered_quantity) AS q_tot, COUNT
(*) AS q_split
FROM apps.oe_order_lines_all
WHERE org_id = 4680
AND item_type_code IN ('MODEL', 'STANDARD')
GROUP BY org_id, header_id, line_number) r
WHERE r.org_id = l.org_id
AND r.header_id = l.header_id
AND r.line_number = l.line_number
AND h.org_id = l.org_id
AND h.header_id = l.header_id
AND p.config_hdr_id = l.config_header_id(+)
AND t.org_id = l.org_id(+)
AND t.header_id = l.header_id(+)
AND t.line_id = l.line_id(+)
AND u.org_id = l.org_id
AND u.header_id = l.header_id
AND s.subscriber_id = u.order_admin(+)
AND i.transaction_type_id = h.order_type_id(+)
AND i.LANGUAGE = 'US'
AND c.organization_id = l.ship_from_org_id(+)
AND c.inventory_item_id = l.inventory_item_id
AND c.cost_type = 'Frozen'
AND b.org_id = l.org_id(+)
AND b.header_id = l.header_id(+)
AND b.line_id = l.line_id(+)
AND v.org_id = l.org_id(+)
AND v.header_id = l.header_id(+)
AND v.line_number = l.line_number
AND l.org_id = 4680
AND l.item_type_code IN ('MODEL', 'STANDARD')
AND SUBSTR (l.orig_sys_line_ref, 1, 18) != 'OE_ORDER_LINES_ALL');
It is giving the error ORA-00904: : invalid identifier
AND v.line_number = l.line_number
|
|
|
Error while running script [message #620087 is a reply to message #619255] |
Mon, 28 July 2014 12:47 |
|
nishantranjan00787
Messages: 36 Registered: July 2014 Location: india
|
Member |
|
|
I have just rewrite the code to take the session level trace and to tune it.But i am getting error while running
/* Formatted on 2014/07/28 20:01 (Formatter Plus v4.8.8) */
SELECT *
FROM (WITH a AS
(SELECT c.LOCATION, c.site_use_code, p.party_site_number
FROM apps.hz_cust_site_uses_all c,
apps.hz_cust_acct_sites_all s,
apps.hz_party_sites p
WHERE s.cust_acct_site_id = c.cust_acct_site_id
AND s.org_id = c.org_id
AND p.party_site_id = s.party_site_id
AND c.org_id = 4680
AND c.status = 'A'),
b AS
(SELECT h.org_id, h.header_id, h.line_id,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h2011,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h0003,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h2021,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h0060,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h2035,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h2421,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h2422,
MAX (CASE
WHEN s.hold_id = NULL
THEN 'NULL'
END) AS h2423
FROM apps.oe_order_holds_all h, apps.oe_hold_sources_all s
WHERE s.org_id = h.org_id
AND s.hold_source_id(+) = h.hold_source_id
AND s.hold_id IN
(2011, 3, 2021, 60, 2035, 2421, 2422, 2423)
AND h.org_id = 4680
AND h.released_flag = 'N'
GROUP BY h.org_id, h.header_id, h.line_id),
v AS
(SELECT l.org_id, l.header_id, l.line_number,
MAX (n.last_update_date) AS ddu,
COUNT (DISTINCT (c.meaning)) AS ddc,
MAX (c.meaning) AS dds,
COUNT (DISTINCT (h.meaning)) AS dlc,
MAX (h.meaning) AS dls
FROM apps.oe_order_lines_all l,
apps.wsh_delivery_details d,
apps.wsh_delivery_assignments m,
apps.wsh_new_deliveries n,
apps.fnd_lookup_values c,
apps.fnd_lookup_values h
WHERE d.org_id = l.org_id
AND d.source_header_id = l.header_id
AND d.source_line_id = l.line_id
AND m.delivery_detail_id = d.delivery_detail_id
AND n.organization_id = l.ship_from_org_id
AND n.delivery_id = m.delivery_id
AND c.lookup_code = d.released_status
AND c.lookup_type = 'PICK_STATUS'
AND c.LANGUAGE = 'US'
AND h.lookup_code = n.status_code
AND h.lookup_type = 'DELIVERY_STATUS'
AND h.LANGUAGE = 'US'
AND l.org_id = 4680
AND l.item_type_code IN ('STANDARD', 'CONFIG')
GROUP BY l.org_id, l.header_id, l.line_number
ORDER BY l.org_id, l.header_id, l.line_number)
SELECT h.order_number AS o_order_nr, i.NAME AS o_order_type,
l.line_number AS o_ln_nr,
l.orig_sys_document_ref AS o_orig_sys_document_ref,
l.orig_sys_line_ref AS o_ln_orig_sys_line_ref,
p.orig_sys_line_ref AS o_sato_orig_sys_line_ref,
NVL (p.orig_sys_line_ref,
l.orig_sys_line_ref
) AS o_ln_full_line_ref,
l.ordered_item AS o_ln_ordered_item,
p.model_string AS o_sato_model,
NVL (NVL (t.model_string, p.model_string),
l.ordered_item
) AS o_ln_full_model,
p.pato_parent_item AS o_sato_parent_item,
p.component_item AS o_sato_component_item,
h.flow_status_code AS o_hd_flow_status,
l.flow_status_code AS o_ln_flow_status,
DECODE (v.ddc,
NULL, 'NULL',
NULL, v.dds,
'NULL'
) AS o_ln_pick_status,
DECODE (v.dlc,
NULL, 'NULL',
NULL, v.dls,
'NULL'
) AS o_ln_delivery_status,
b.h2011 AS o_ln_configuration_hold,
b.h0003 AS o_ln_conf_validation_hold,
b.h2021 AS o_ln_calibration_hold,
b.h0060 AS o_ln_conf_exception_hold,
b.h2035 AS o_ln_auto_conf_hold,
b.h2421 AS o_ln_engineering_hold,
b.h2422 AS o_ln_scheduling_hold,
b.h2423 AS o_ln_invoicing_hold,
CASE
WHEN TRUNC (l.promise_date) =
TRUNC (l.creation_date)
+ NULL
THEN 'NULL'
ELSE 'NULL'
END AS o_ln_hub_promised,
CASE
WHEN TRUNC (l.schedule_ship_date) =
TRUNC (l.creation_date)
+ NULL
THEN 'NULL'
ELSE 'NULL'
END AS o_ln_hub_scheduled,
h.creation_date AS o_hd_creation_dt,
p.creation_date AS o_sato_creation_dt,
l.creation_date AS o_ln_creation_dt,
v.ddu AS o_ln_delivery_update_dt,
s.subscriber_name AS o_hd_subscriber_name,
r.q_tot AS o_ln_quantity, r.q_split AS o_ln_split_count,
l.order_quantity_uom AS o_ln_uom,
h.transactional_curr_code AS o_hd_trans_currency,
ROUND
(CASE
WHEN l.top_model_line_id IS NULL
THEN NVL (l.unit_selling_price, NULL) * r.q_tot
WHEN p.component_item IS NULL
THEN (SELECT SUM ( NVL (q.unit_selling_price, NULL)
* r.q_tot
)
FROM apps.oe_order_lines_all q
WHERE q.org_id = l.org_id
AND q.header_id = l.header_id
AND q.top_model_line_id = l.line_id)
ELSE (SELECT NVL (q.unit_selling_price, NULL) * r.q_tot
FROM apps.oe_order_lines_all q
WHERE q.org_id = l.org_id
AND q.header_id = l.header_id
AND q.top_model_line_id = l.top_model_line_id
AND q.ordered_item = p.component_item
AND ROWNUM = 1)
END,
NULL
) AS o_ln_extended_price,
ROUND
(CASE
WHEN l.item_type_code = 'NULL'
THEN NVL (c.item_cost, NULL) * r.q_tot
WHEN l.item_type_code = 'NULL'
THEN (SELECT NVL (v.item_cost, NULL) * r.q_tot
FROM apps.oe_order_lines_all q,
apps.cst_item_cost_type_v v
WHERE v.organization_id = q.ship_from_org_id
AND v.inventory_item_id =
q.inventory_item_id
AND v.cost_type = 'Frozen'
AND q.org_id = l.org_id
AND q.header_id = l.header_id
AND q.top_model_line_id = l.line_id
AND q.item_type_code = 'CONFIG')
END,
NULL
) AS o_ln_extended_cost,
h.cust_po_number AS o_hd_customer_po,
h.fob_point_code AS o_hd_shipping_terms,
u.rep_order_nbr AS o_hd_representative_order,
u.project_number AS o_hd_project_nr, u.sic_code AS o_hd_sic,
u.ultimate_dest AS o_hd_ultimate_dest,
h.end_customer_site_use_id AS o_hd_end_user_nr,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION =
h.end_customer_site_use_id)
AS o_hd_end_user_psn,
h.ship_to_org_id AS o_hd_ship_to_org_id,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION = h.ship_to_org_id
AND a.site_use_code = 'SHIP_TO') AS o_hd_ship_to_psn,
h.invoice_to_org_id AS o_hd_bill_to_org_id,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION = h.invoice_to_org_id
AND a.site_use_code = 'BILL_TO') AS o_hd_bill_to_psn,
u.named_place AS o_hd_named_place,
t.shiptoaddr AS o_ln_ship_to_nr,
(SELECT a.party_site_number
FROM a
WHERE a.LOCATION = t.shiptoaddr
AND a.site_use_code = 'SHIP_TO') AS o_ln_ship_to_psn,
u.po_originated AS o_hd_cust_po_dt,
u.poreceiptdate AS o_hd_branch_po_received_dt,
u.requested_delivery_date AS o_hd_cust_req_delivery_dt,
t.requested_delivery_date AS o_ln_cust_req_delivery_dt,
l.request_date AS o_ln_branch_req_ship_dt,
GREATEST (l.promise_date,
TO_DATE ('NULL', 'NULL')
) AS o_ln_plant_prom_ship_dt,
l.schedule_ship_date AS o_ln_plant_sched_ship_dt,
t.creation_date AS o_ln_3lp_creation_dt,
t.model_string AS o_ln_3lp_model,
t.mse_config_status AS o_ln_3lp_mse_config_status
FROM apps.oe_order_lines_all l,
apps.oe_order_headers_all h,
xxom.xxom_3lp_sym_ora_order_lines t,
xxom.xxom_3lp_sym_ora_order_hdr u,
apps.xxont_som_scheduler s,
apps.oe_transaction_types_tl i,
apps.cst_item_cost_type_v c,
(SELECT DISTINCT *
FROM xxom.xxom_xml1_pato_stg) p,
(SELECT org_id, header_id, line_number,
SUM (ordered_quantity) AS q_tot, COUNT
(*) AS q_split
FROM apps.oe_order_lines_all
WHERE org_id = 4680
AND item_type_code IN ('MODEL', 'STANDARD')
GROUP BY org_id, header_id, line_number) r
WHERE r.org_id = l.org_id
AND r.header_id = l.header_id
AND r.line_number = l.line_number
AND h.org_id = l.org_id
AND h.header_id = l.header_id
AND p.config_hdr_id = l.config_header_id(+)
AND t.org_id = l.org_id(+)
AND t.header_id = l.header_id(+)
AND t.line_id = l.line_id(+)
AND u.org_id = l.org_id
AND u.header_id = l.header_id
AND s.subscriber_id = u.order_admin(+)
AND i.transaction_type_id = h.order_type_id(+)
AND i.LANGUAGE = 'US'
AND c.organization_id = l.ship_from_org_id(+)
AND c.inventory_item_id = l.inventory_item_id
AND c.cost_type = 'Frozen'
AND b.org_id = l.org_id(+)
AND b.header_id = l.header_id(+)
AND b.line_id = l.line_id(+)
AND v.org_id = l.org_id(+)
AND v.header_id = l.header_id(+)
AND v.line_number = l.line_number
AND l.org_id = 4680
AND l.item_type_code IN ('MODEL', 'STANDARD')
AND SUBSTR (l.orig_sys_line_ref, 1, 18) != 'OE_ORDER_LINES_ALL');
It is giving the error ORA-00904: : invalid identifier
ERROR at line 258:
ORA-00904: "V"."LINE_NUMBER": invalid identifier
*BlackSwan added {code} tags. Please do so yourself in the future.
see http://www.orafaq.com/forum/t/174502/
[Updated on: Mon, 28 July 2014 12:52] by Moderator Report message to a moderator
|
|
|
Re: Error while running script [message #620088 is a reply to message #620087] |
Mon, 28 July 2014 12:51 |
John Watson
Messages: 8962 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
What is anyone supposed to do with that unformatted mess? Find a bug that you have just introduced? I would fire any of our developers who produced stuff like that.
And what about the advice I gave you to remove a totally unnecessary outer join? And investigate whether the other outer joins are needed? If you do not intend to do that, it would be polite to explain why.
|
|
|
Re: Error while running script [message #620089 is a reply to message #620088] |
Mon, 28 July 2014 12:54 |
John Watson
Messages: 8962 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Oh no! I confused your post with another one! Please accept my apologies, the comment regarding outer join was intended for someone else.
--update:
this is not my day. I see that you are the same person who ignored my fix for out joins. Just on a different topic.
[Updated on: Mon, 28 July 2014 12:56] Report message to a moderator
|
|
|
|
Re: Error while running script [message #620092 is a reply to message #620087] |
Mon, 28 July 2014 13:01 |
John Watson
Messages: 8962 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Now that BS has added [code] tags, your code is readable. What table is aliased as V? I can't see one. It really does help if you use more meaningfue aliases than single letters.
|
|
|
Re: Error while running script [message #620093 is a reply to message #620092] |
Mon, 28 July 2014 13:02 |
|
nishantranjan00787
Messages: 36 Registered: July 2014 Location: india
|
Member |
|
|
GROUP BY h.org_id, h.header_id, h.line_id),
v AS
(SELECT l.org_id, l.header_id, l.line_number,
MAX (n.last_update_date) AS ddu,
COUNT (DISTINCT (c.meaning)) AS ddc,
MAX (c.meaning) AS dds,
COUNT (DISTINCT (h.meaning)) AS dlc,
MAX (h.meaning) AS dls
FROM apps.oe_order_lines_all l,
apps.wsh_delivery_details d,
apps.wsh_delivery_assignments m,
apps.wsh_new_deliveries n,
apps.fnd_lookup_values c,
apps.fnd_lookup_values h
|
|
|
|
Re: Error while running script [message #620095 is a reply to message #620093] |
Mon, 28 July 2014 13:09 |
John Watson
Messages: 8962 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I've merged your two topics.
You have made a conversion from modern, easily readable, ANSI join syntax to the
twentieth century Oracle join syntax. That is a terrible idea. The fact that you
have introduced a bug should convince you of that. ANSI syntax is easier to
read and less prone to error.
If you want to remove outer joins, I've already told the one that you can get rid
of. If you want to get remove any others, look at your data wand work out if the
outer join is needed.
You cannot tune SQL if you do not understand your data.
|
|
|
|
|
Goto Forum:
Current Time: Thu Jan 02 18:17:09 CST 2025
|