1 在from中的表中表
SELECT
PRL.ITEM_DESCRIPTION,
PRL.UNIT_MEAS_LOOKUP_CODE,
PRL.QUANTITY,
TO_CHAR(PRL.NEED_BY_DATE, 'yyyy/mm/dd') NEED_BY_DATE,
PRL.VENDOR_ID,
PRL.SUGGESTED_VENDOR_NAME VENDOR_NAME
FROM PO.PO_REQUISITION_HEADERS_ALL PRH,
PO.PO_REQUISITION_LINES_ALL PRL,
APPS.PER_PEOPLE_F PPF1,
(SELECT DISTINCT AGENT_ID, AGENT_NAME FROM APPS.PO_AGENTS_V) PPF2,----表中表
PO.PO_REQ_DISTRIBUTIONS_ALL PRD,
INV.MTL_SYSTEM_ITEMS_B MSI,
PO.PO_LINE_LOCATIONS_ALL PLL,
PO.PO_LINES_ALL PL,
PO.PO_HEADERS_ALL PH
WHERE PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID
AND PPF1.PERSON_ID = PRH.PREPARER_ID
AND PRH.CREATION_DATE BETWEEN PPF1.EFFECTIVE_START_DATE AND
PPF1.EFFECTIVE_END_DATE
AND PPF2.AGENT_ID(+) = MSI.BUYER_ID
2 在select中的表中表
SELECT
poh.po_header_id
,poh.segment1 po_number
,poh.currency_code
,to_char(poh.creation_date,'YYYY-MM-DD') po_date
,pov.vendor_name
,pov.segment1 vendor_number
,pvs.address_line1 vendor_address
,pvc.area_code || pvc.phone vendor_phone
,pvc.fax_area_code || pvc.fax vendor_fax
,ppf.full_name buyer
,pvc.last_name || pvc.first_name contact_name
,hrl1.location_code ship_to_location
,hrl2.location_code bill_to_location
,poh.attribute5 discount
/*,decode(hrl.location_code
,'香港建勝'
,'香港九龍灣宏冠道6號鴻力工業中心A座1樓A1&A21室'
,'台北公司'
,'台北市內湖區洲子街88號9樓'
,'台北工廠'
,'新竹縣芎林鄉上山村文德路231號') location_code*/
,hla1.address_line_1||hla1.address_line_2||hla1.address_line_3 ship_address
,hla1.telephone_number_1 ship_tel
,hla1.telephone_number_2||hla1.telephone_number_3 ship_fax
,hla2.address_line_1||hla2.address_line_2||hla2.address_line_3 bill_address
,hla2.telephone_number_1 bill_tel
,hla2.telephone_number_2||hla2.telephone_number_3 bill_fax
,hrl.ADDRESS_LINE_1 location_code
,poh.REVISION_NUM revision
,to_char(poh.REVISED_DATE,'YYYY-MM-DD') revision_date
,(SELECT MAX(ffv.description) -------------------------------表中表 这里用到了from中的表po_headers_all poh ,所以不能放在from中。
FROM fnd_flex_values_vl ffv
,fnd_flex_value_sets ffvs
WHERE ffv.flex_value_set_id = ffvs.flex_value_set_id
AND ffvs.flex_value_set_name = '3G_PO_PAYMENT_TERM'
AND ffv.flex_value = poh.attribute4) payment
,(SELECT MAX(ffv.description)
FROM fnd_flex_values_vl ffv
,fnd_flex_value_sets ffvs
WHERE ffv.flex_value_set_id = ffvs.flex_value_set_id
AND ffvs.flex_value_set_name = '3G_PO_DELIVERY_TERM'
AND ffv.flex_value = poh.attribute3) delivery
,(SELECT MAX(ffv.description)
FROM fnd_flex_values_vl ffv
,fnd_flex_value_sets ffvs
WHERE ffv.flex_value_set_id = ffvs.flex_value_set_id
AND ffvs.flex_value_set_name = '3G_PO_SHIPMENT'
AND ffv.flex_value = poh.attribute2) shipment
,(SELECT MAX(ffv.description)
FROM fnd_flex_values_vl ffv
,fnd_flex_value_sets ffvs
WHERE ffv.flex_value_set_id = ffvs.flex_value_set_id
AND ffvs.flex_value_set_name = '3G_PO_PACKING'
AND ffv.flex_value = poh.attribute1) packing
FROM po_headers_all poh
,po_vendors pov
,po_vendor_sites_all pvs
,po_vendor_contacts pvc
,per_people_f ppf
,hr_locations_all hrl
,hr_locations_all hla1
,hr_locations_all hla2
,hr_locations_all_tl hrl1
,hr_locations_all_tl hrl2
WHERE poh.vendor_id = pov.vendor_id
AND pov.vendor_id = pvs.vendor_id
AND poh.agent_id = ppf.person_id
AND pvs.vendor_site_id(+) = poh.vendor_site_id
AND pvc.vendor_contact_id(+) = poh.vendor_contact_id
AND pvc.vendor_site_id(+) = poh.vendor_site_id
AND poh.ship_to_location_id = hrl.location_id(+)
AND poh.ship_to_location_id = hla1.location_id(+)
AND poh.bill_to_location_id = hla2.location_id(+)
AND hrl1.location_id(+) = poh.ship_to_location_id
AND hrl1.LANGUAGE(+) = userenv('LANG')
AND hrl2.location_id(+) = poh.bill_to_location_id
AND hrl2.LANGUAGE(+) = userenv('LANG')
AND ppf.EFFECTIVE_END_DATE >= SYSDATE
AND poh.authorization_status = 'APPROVED'
AND poh.segment1 between nvl(:p_po_from ,poh.segment1) and nvl(:p_po_to ,poh.segment1)
AND pov.vendor_id = nvl(:p_vendor_id,pov.vendor_id)
AND poh.creation_date between nvl(:p_date_form,poh.creation_date) AND nvl(:p_date_to,poh.creation_date)
AND ppf.full_name = nvl(:p_buyer,ppf.full_name)
ORDER BY poh.segment1;
两者有区别:
放在select中可以引用from中的表来筛选数据,而且(好像)一次只跑出一笔,
放在form中不可以引用from中的表作为条件,会跑出所以满足条件的数据,在from中和其他表的作用一样。在where中再筛选数据