Home » SQL & PL/SQL » SQL & PL/SQL » ANSI JOIN requirement
ANSI JOIN requirement [message #686512] Fri, 30 September 2022 09:08 Go to next message
nishant1987
Messages: 8
Registered: September 2022
Junior Member
i need to write below sql in ANSI style because of one condition.I am not comfortable in writing in ANSI style.actually rows processed is same but data mismatch ,so need to write in ANSI style to implement 2 outer join on same table.It throw error as

ORA-01417: a table may be outer joined to at most one other table


/* Formatted on 9/30/2022 6:56:13 PM (QP5 v5.318) */
WITH /*+t_z1*/
tb_promise1
AS
(SELECT pla.promised_date,
pla.po_header_id,
pla.po_line_id,
pla.line_location_id,
pla.revision_num,
MIN (revision_num) OVER (PARTITION BY line_location_id)
min_rev_num
FROM apps.po_line_locations_archive_all pla
WHERE promised_date IS NOT NULL),
tb_promise2
AS
(SELECT pla.promised_date,
pla.po_header_id,
pla.po_line_id,
pla.line_location_id,
pla.revision_num
FROM tb_promise1 pla
WHERE revision_num = min_rev_num)
SELECT DISTINCT
pll.po_line_id,
ploc.line_location_id,
(SELECT ptl.line_type
FROM apps.po_line_types_tl ptl
WHERE ptl.line_type_id = pll.line_type_id AND ptl.LANGUAGE = 'US')
"Line_Type",
ploc.quantity_accepted,
NULL
release_approved_date,
NULL
release_date,
NULL
release_hold_flag,
NULL
release_type,
DECODE (ploc.po_release_id, NULL, NULL, ploc.quantity)
released_quantity,
items.preprocessing_lead_time
"PreProcessing_LT",
items.full_lead_time
"Processing_LT",
items.postprocessing_lead_time
"PostProcessing_LT",
-- (SELECT /*+ use_nl (msib ploc) */
-- items.preprocessing_lead_time
-- FROM apps.mtl_system_items_b items
-- WHERE items.inventory_item_id = pll.item_id
-- AND items.organization_id = ploc.SHIP_TO_ORGANIZATION_ID)
-- "PreProcessing_LT",
-- (SELECT /*+ use_nl (msib ploc) */
-- items.full_lead_time
-- FROM apps.mtl_system_items_b items
-- WHERE items.inventory_item_id = pll.item_id
-- AND items.organization_id = ploc.SHIP_TO_ORGANIZATION_ID)
-- "Processing_LT",
-- (SELECT /*+ use_nl (msib ploc) */
-- items.postprocessing_lead_time
-- FROM apps.mtl_system_items_b items
-- WHERE items.inventory_item_id = pll.item_id
-- AND items.organization_id = ploc.SHIP_TO_ORGANIZATION_ID)
-- "PostProcessing_LT",
ploc.firm_status_lookup_code,
-- NVL (
-- (SELECT pla.promised_date
-- FROM apps.po_line_locations_archive_all pla
-- WHERE pla.po_header_id = pha.po_header_id
-- AND pla.po_line_id = pll.po_line_id
-- AND pla.line_location_id = ploc.line_location_id
-- AND pla.revision_num =
-- (SELECT MIN (revision_num)
-- FROM apps.po_line_locations_archive_all plla2
-- WHERE plla2.promised_date IS NOT NULL
-- AND plla2.line_location_id =
-- ploc.line_location_id)),
-- ploc.promised_date)
-- "Original_Promise_Date",
NVL (pla.promised_date, ploc.promised_date),
(SELECT items.long_description
FROM apps.mtl_system_items_tl items
WHERE items.inventory_item_id = pll.item_id
AND items.organization_id IN
(SELECT fin.inventory_organization_id
FROM apps.financials_system_params_all fin
WHERE fin.org_id = pha.org_id)
AND items.LANGUAGE = 'US')
"Item_Long_Description",
NVL (ploc.approved_flag, 'N')
approved_code,
pvs.country
"Supplier_Site_Country",
pll.note_to_vendor,
NVL (ploc.quantity, 0)
- NVL (ploc.quantity_cancelled, 0)
- NVL (ploc.quantity_received, 0) * ploc.price_override
"Shipment_Amount",
ploc.attribute4
"PO_Ship_Date",
(SELECT meaning
FROM apps.fnd_lookup_values
WHERE lookup_type = 'SHIP_METHOD'
AND lookup_code = ploc.attribute9
AND language = 'US')
"Ship_Method",
(SELECT prla.note_to_receiver
FROM apps.po_req_distributions_all prda
INNER JOIN apps.po_requisition_lines_all prla
ON prda.requisition_line_id = prla.requisition_line_id
WHERE prda.distribution_id = pdi.req_distribution_id)
"Note_To_Receiver",
DECODE (pha.USER_HOLD_FLAG, 'Y', 'Y', pll.USER_HOLD_FLAG)
"Hold_Flag",
(SELECT ABC_CLASS_NAME
FROM APPS.MTL_ABC_ASSIGNMENT_GROUPS ASG
INNER JOIN APPS.MTL_ABC_ASSIGNMENTS ASSI
ON ASG.ASSIGNMENT_GROUP_ID = ASSI.ASSIGNMENT_GROUP_ID
INNER JOIN APPS.MTL_ABC_CLASSES classes
ON ASSI.ABC_CLASS_ID = classes.ABC_CLASS_ID
WHERE ASG.organization_id = ploc.SHIP_TO_ORGANIZATION_ID
AND ASG.ASSIGNMENT_GROUP_NAME = 'MIN ABC Assignment'
AND ASSI.inventory_item_id = pll.item_id)
ABCClass,
(SELECT CONCATENATED_SEGMENTS AS charge_accountsfrom
FROM apps.gl_code_combinations_kfv gcc
WHERE gcc.code_combination_id = pdi.code_combination_id)
AS charge_accounts
FROM apps.po_headers_all pha,
apps.po_lines_all pll,
apps.po_line_locations_all ploc,
apps.po_distributions_all pdi,
apps.mtl_system_items_b items,
-- apps.per_all_people_f papf,
-- apps.AP_SUPPLIERS pv,
apps.AP_SUPPLIER_SITES_ALL pvs,
-- apps.AP_SUPPLIER_CONTACTS pvc,
-- apps.ap_terms apt,
-- apps.po_lookup_codes plc1,
-- apps.po_lookup_codes plc2,
-- apps.hr_locations hlv_line_ship_to,
-- apps.hr_locations hlv_ship_to,
-- apps.hr_locations hlv_bill_to,
apps.hr_organization_units hou,
tb_promise2 pla
-- ,
-- apps.hr_locations_no_join loc,
-- apps.hr_locations_all_tl hrl1,
-- apps.hr_locations_all_tl hrl2
WHERE pll.po_header_id(+) = pha.po_header_id
AND ploc.po_line_id(+) = pll.po_line_id
AND pdi.line_location_id(+) = ploc.line_location_id
AND ploc.shipment_type IN ('STANDARD', 'PLANNED')
/* AND papf.person_id(+) = pha.agent_id
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND papf.employee_number IS NOT NULL
AND pv.vendor_id(+) = pha.vendor_id */
AND pvs.vendor_site_id(+) = pha.vendor_site_id
AND ITEMs.INVENTORY_ITEM_ID(+) = pll.ITEM_ID
AND ITEMs.ORGANIZATION_ID(+) = ploc.SHIP_TO_ORGANIZATION_ID ---problematic
AND pvs.ORG_ID = pha.org_id
AND hou.organization_id = pha.org_id
AND hou.organization_id IN (2763)
AND NVL (pha.closed_code, 'OPEN') IN ('OPEN', 'CLOSED')
AND NVL (pll.closed_code, 'OPEN') IN ('OPEN', 'CLOSED')
AND NVL (ploc.cancel_flag, 'N') = 'N'
AND pha.authorization_status IN
('APPROVED', 'REQUIRES REAPPROVAL', 'IN PROCESS')
-- and tla.po_header_id(+) = pha.po_header_id
-- AND tla.po_line_id(+) = pll.po_line_id
AND pla.line_location_id(+) = ploc.line_location_id
and pll.po_line_id =981477
Re: ANSI JOIN requirement [message #686513 is a reply to message #686512] Fri, 30 September 2022 09:13 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

If you don't know how to format the code, learn it using SQL Formatter.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

And feedback in your topics.

[Updated on: Fri, 30 September 2022 09:13]

Report message to a moderator

Previous Topic: Sql is causing performance issue
Next Topic: Procedure Parameter List
Goto Forum:
  


Current Time: Fri Mar 29 10:12:16 CDT 2024