hubbsshaun
IS-IT--Management
Hi all,
I have written the SQL statement below which shows me item information for items ordered within the time frame specified within the subquery. The issue I am having is that I am actually looking for the items that have NOT been ordered, but changing the first part of my WHERE statement to NOT IN results in 0 rows.
Any help is greatly appreciated. Thanks in advance.
Shaun Hubbs
SELECT DISTINCT MTL_SYSTEM_ITEMS_B.DESCRIPTION,
MTL_SYSTEM_ITEMS_B.SEGMENT1,
MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_STATUS_CODE,
MTL_SYSTEM_ITEMS_B.ITEM_TYPE,
MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID
FROM INV.MTL_SYSTEM_ITEMS_B MTL_SYSTEM_ITEMS_B
WHERE ( MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID IN
(
SELECT PO_LINES_ALL.ITEM_ID
FROM PO.PO_LINES_ALL PO_LINES_ALL
WHERE ( PO_LINES_ALL.CREATION_DATE BETWEEN TO_DATE('01-NOV-2007','DD-MM-YYYY') AND TO_DATE('29-NOV-2007','DD-MM-YYYY') )
AND ( ( ( PO_LINES_ALL.CANCEL_FLAG IS NULL OR PO_LINES_ALL.CANCEL_FLAG = 'N' ) ) )
)
)
AND ( MTL_SYSTEM_ITEMS_B.ITEM_TYPE NOT IN ('STK','DIRECT','SP') )
AND ( MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_STATUS_CODE = 'Active' )
AND ( MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID = '5' )
ORDER BY MTL_SYSTEM_ITEMS_B.SEGMENT1
;
I have written the SQL statement below which shows me item information for items ordered within the time frame specified within the subquery. The issue I am having is that I am actually looking for the items that have NOT been ordered, but changing the first part of my WHERE statement to NOT IN results in 0 rows.
Any help is greatly appreciated. Thanks in advance.
Shaun Hubbs
SELECT DISTINCT MTL_SYSTEM_ITEMS_B.DESCRIPTION,
MTL_SYSTEM_ITEMS_B.SEGMENT1,
MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_STATUS_CODE,
MTL_SYSTEM_ITEMS_B.ITEM_TYPE,
MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID
FROM INV.MTL_SYSTEM_ITEMS_B MTL_SYSTEM_ITEMS_B
WHERE ( MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID IN
(
SELECT PO_LINES_ALL.ITEM_ID
FROM PO.PO_LINES_ALL PO_LINES_ALL
WHERE ( PO_LINES_ALL.CREATION_DATE BETWEEN TO_DATE('01-NOV-2007','DD-MM-YYYY') AND TO_DATE('29-NOV-2007','DD-MM-YYYY') )
AND ( ( ( PO_LINES_ALL.CANCEL_FLAG IS NULL OR PO_LINES_ALL.CANCEL_FLAG = 'N' ) ) )
)
)
AND ( MTL_SYSTEM_ITEMS_B.ITEM_TYPE NOT IN ('STK','DIRECT','SP') )
AND ( MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_STATUS_CODE = 'Active' )
AND ( MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID = '5' )
ORDER BY MTL_SYSTEM_ITEMS_B.SEGMENT1
;