I've seen worse. My programmers don't know how to use NOT EXISTS even though I've explained it many times. And that's the least of my problems. Look at this mess:
SELECT *
FROM sar.pax_header_suspense_err_temp
WHERE manifest_type
|| manifesting_station
|| fiscal_year
|| manifest_serial_number NOT IN (
SELECT manifest_type
|| manifesting_station
|| fiscal_year
|| manifest_serial_number
FROM manifest_serial_number_history)
Takes over an hour to run. I rewrote it as such:
SELECT *
FROM sar.pax_header_suspense_err_temp t
WHERE NOT EXISTS
(SELECT 'X'
FROM manifest_serial_number_history h
WHERE
t.manifest_type = h.manifest_type and
t.manifesting_station = h.manifesting_station and
t.fiscal_year = h.fiscal_year and
t.manifest_serial_number = h.manifest_serial_number )
Under a second.
Jerry Whittle
ACIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145
-----Original Message-----
From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
Raj,
I needed a 12 pack adter this one, it's from PeopleSlop:
SELECT 0001560265,OPL.BUSINESS_UNIT,OPL.PRODUCTION_ID,OPL.OP_SEQUENCE,
PID.INV_ITEM_ID,TMP.COST_ELEMENT,'04',0,0,0,0,0,0,PID.ORIG_UOM,PID.PRDN_AREA_COD
E,
PID.PRODUCTION_TYPE,OPL.QTY_SCRAPPED,OPL.PERCENT_COMP,' ',1,0,' ','
',0000000000
FROM PS_BU_ITEMS_INV INV,PS_SF_PRDNID_HEADR PID,PS_CE_OP_LIST_COPY OPL,
PS_CE_OP_LIST_VW OPLIST,PS_SF_COMP_LIST CMP,PS_CE_ITEMVAR_TMP TMP
WHERE INV.BUSINESS_UNIT='VICOR'
AND PID.BUSINESS_UNIT='VICOR'
AND OPL.BUSINESS_UNIT='VICOR'
AND OPLIST.BUSINESS_UNIT='VICOR'
AND CMP.BUSINESS_UNIT='VICOR'
AND TMP.BUSINESS_UNIT='VICOR'
AND TMP.PROCESS_INSTANCE=0001560265
AND OPL.PROCESS_INSTANCE=0001560265
AND OPLIST.PROCESS_INSTANCE=0001560265
AND OPL.PRODUCTION_ID= PID.PRODUCTION_ID
AND OPL.PRODUCTION_ID= CMP.PRODUCTION_ID
AND OPL.PRODUCTION_ID= OPLIST.PRODUCTION_ID
AND INV.INV_ITEM_ID= PID.INV_ITEM_ID
AND ( OPL.OP_SEQUENCE= CMP.OP_SEQUENCE OR (CMP.OP_SEQUENCE = 0 AND
OPL.OP_SEQUENCE = OPLIST.OP_SEQUENCE))
AND PID.PROD_STATUS BETWEEN '30' AND '60'
AND TMP.INV_ITEM_ID= CMP.COMPONENT_ID
AND TMP.CONFIG_CODE= CMP.CONFIG_CODE
AND CMP.SOURCE_CODE <> '5'
AND CMP.NON_OWN_FLAG = 'N'
AND NOT EXISTS (SELECT 'X' FROM PS_CE_SCRAPCST_TMP TMP2
WHERE TMP2.PROCESS_INSTANCE=0001560265
AND TMP2.BUSINESS_UNIT = OPL.BUSINESS_UNIT
AND TMP2.PRODUCTION_ID = OPL.PRODUCTION_ID
AND TMP2.OP_SEQUENCE = OPL.OP_SEQUENCE
AND TMP2.COST_ELEMENT= TMP.COST_ELEMENT)
GROUP BY OPL.BUSINESS_UNIT,OPL.PRODUCTION_ID,OPL.OP_SEQUENCE,PID.INV_ITEM_ID,
TMP.COST_ELEMENT,PID.ORIG_UOM,PID.PRDN_AREA_CODE,PID.PRODUCTION_TYPE,
OPL.PERCENT_COMP,OPL.QTY_SCRAPPED
