Hi All,
I have seen people used to write select statement inside the select query to printing some value.
for example
SELECT iop.organization_code,
po_hdr.segment1 po_number,
po_hdr.comments,
hp.party_name supplier,
ESIB.item_type,
(SELECT segment1
|| ‘.’
|| segment2
|| ‘.’
|| segment3
|| ‘.’
|| segment4
|| ‘.’
|| segment5
|| ‘.’
|| segment6
|| ‘.’
|| segment7
FROM gl_code_combinations gcc,
po_distributions_all pda
WHERE 1 = 1
AND pda.po_line_id = po_line.po_line_id
AND gcc.code_combination_id = pda.code_combination_id
AND ROWNUM = 1) INV_EXP_DEFATUL_ACCT,
(SELECT ppf.full_name
FROM per_person_names_f ppf
WHERE ppf.person_name_id = po_hdr.agent_id
AND Trunc(SYSDATE) BETWEEN Trunc(ppf.effective_start_date) AND
Trunc(ppf.effective_end_date)
AND ROWNUM = 1) buyer,
(SELECT prha.requisition_number
FROM por_req_distributions_all prda,
por_requisition_lines_all prla,
por_requisition_headers_all prha,
po_distributions_all pda
WHERE prda.requisition_line_id = prla.requisition_line_id
AND prla.requisition_header_id = prha.requisition_header_id
AND prda.distribution_id = pda.req_distribution_id
AND pda.line_location_id = line_loc.line_location_id
AND ROWNUM = 1) requisition,
FROM po_headers_all po_hdr,
poz_suppliers pv,
hz_parties hp,
poz_supplier_sites_all_m pvsa,
po_lines_all po_line,
po_line_locations_all line_loc,
egp_system_items_b esib,
inv_org_parameters iop,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
hr_operating_units hou
WHERE po_hdr.po_header_id = po_line.po_header_id
AND pv.vendor_id = po_hdr.vendor_id
AND pv.party_id = hp.party_id
AND pvsa.vendor_site_id = po_hdr.vendor_site_id
AND po_line.po_line_id = line_loc.po_line_id
AND po_line.item_id = esib.inventory_item_id
AND line_loc.ship_to_organization_id = esib.organization_id
AND iop.organization_id = line_loc.ship_to_organization_id
AND esib.organization_id = iop.organization_id
AND rsh.shipment_header_id(+) = rsl.shipment_header_id
AND line_loc.po_line_id = rsl.po_line_id(+)
AND line_loc.po_header_id = rsl.po_header_id(+)
AND rsl.po_line_location_id = line_loc.line_location_id
AND rsl.source_document_code = ‘po’
AND hou.organization_id = po_hdr.prc_bu_id
Here we can see select nested select query has been used thrice .I would like to know how the execution happen and how it affects performance.
Regards,
Bikram