Finding items not ordered in specified time frame 1

[hubbsshaun]

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
;

 

[PWise]

try this

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 ,
                case b.ordereditemid when is null then 'not ordered' else 'ordered' end [ordered]
FROM INV.MTL_SYSTEM_ITEMS_B MTL_SYSTEM_ITEMS_B a


left join(SELECT PO_LINES_ALL.ITEM_ID ordereditemid
        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' )

)  b on a.MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID=b.ordereditemid

 

[PWise]

sorry the case statement is wrong sb

case when b.ordereditemid is null then 'not ordered' else 'ordered' end [ordered]

 

[SQLSister]

This is SQL server, don't bother with that ; at the end. And get rid of all those unneeded parentheses. And if you are using aliases use something shorter not the same name as the table. It makes it hard to read.

Why did you write this as a subselect? wouldn't a left join have been a better choice?

SELECT DISTINCT 	A.DESCRIPTION, 
                	A.SEGMENT1, 
               	A.INVENTORY_ITEM_STATUS_CODE, 
                	A.ITEM_TYPE,
               	A.INVENTORY_ITEM_ID
                
FROM INV.MTL_SYSTEM_ITEMS_B  A
LEFT JOIN PO.PO_LINES_ALL B ON B.ITEM_ID = A.INVENTORY_ITEM_ID
AND B.CREATION_DATE BETWEEN TO_DATE('01-NOV-2007','DD-MM-YYYY') AND TO_DATE('29-NOV-2007','DD-MM-YYYY') 
              AND( B.CANCEL_FLAG IS NULL OR B.CANCEL_FLAG = 'N' )
WHERE    	A.ITEM_TYPE NOT IN ('STK','DIRECT','SP') 
     	AND A.INVENTORY_ITEM_STATUS_CODE = 'Active' 
      	AND A.ORGANIZATION_ID = '5' 
		AND B.ITEM_ID is null

ORDER BY A.SEGMENT1

Not sure if distinct is really needed here. If not it will slow things down, so remove it.


"NOTHING is more important in a database than integrity." ESquared

 

[hubbsshaun]

Thanks pwise!

 

[hubbsshaun]

Thanks as well to SQLSister. I got rid of all of the parentheses as well as the semi-colon. Also, removing the subselect seemed to speed up the query and removing DISTINCT helped as well. It definitely wasn't necessary in this case.

Anyway, thanks again to both of you. It seems to be working great now.

Shaun