I’m using Toad for Oracle.
I currently have Order Numbers with items in both our yard and our warehouse. I’m looking to see items that ONLY have items in the yard.
Essentially I need a count of the different items in the yard and a sum of their weight for each order
I currently have it where it’s counting the items for each order that are in the yard but it’s not limiting it to orders that ONLY have yard items
select ORD.WH_ID, SIO.ORDER_NUMBER, SIO.CUSTOMER_NAME, COUNT (ORD.ITEM_NUMBER) AS LINE_COUNT, SUM(TIM.UNIT_WEIGHT * ORD.QTY) AS WEIGHT from t_ORDER_DETAIL ORD JOIN T_ITEM_MASTER TIM ON ORD.WH_ID = TIM.WH_ID AND ORD.ITEM_NUMBER = TIM.ITEM_NUMBER JOIN SI_ORDER SIO ON SIO.WH_ID = ORD.WH_ID AND SIO.ORDER_NUMBER = ORD.ORDER_NUMBER where ORD.wh_id = '603' --AND ORDER_NUMBER = '1923421' AND PROCESS_DATE >= SYSDATE -1 AND TIM.CLASS_ID IN ('GYARD','GSSPIPE','GBLKALLTHR','GCOPPPIPE','GCOPPCNC') AND SIO.CARRIER = 'OT' GROUP BY ORD.WH_ID, SIO.ORDER_NUMBER, SIO.CUSTOMER_NAME, WEIGHT ORDER BY SIO.ORDER_NUMBER table 1 order # Item # Order1 Y51 Order1 Y52 Order2 Y51 Order2 W35 Order3 W21 Order3 W80 Table 2 Item # Location Y51 Yard Y52 Yard W35 Warehouse W21 Warehouse W80 Warehouse
From the above sample set, I would only want to see Order1 because it is the only order that contains ONLY items located in the Yard
Advertisement
Answer
I am using your two sample tables (table 1 = orders, table 2 = items). This is how to find the order numbers of orders that contain yard items but no warehouse itms:
select o.orderno from orders o join items i on i.itemno = o.itemno group by o.orderno having count(case when i.location = 'Yard' then 1 end) > 0 and count(case when i.location = 'Warehouse' then 1 end) = 0;
If you want to show complete orders, use above query in a subquery, e.g.:
select * from orders where orderno in ( <above query> ) order by orderno, itemno;
If you want an aggregation per order instead, you can modify the query accordingly, e.g.:
select o.orderno, count(*) as number_of_positions, sum(o.amount) as number_of_pieces, sum(o.amount * i.weight) as total_weight from orders o join items i on i.itemno = o.itemno group by o.orderno having count(case when i.location = 'Yard' then 1 end) > 0 and count(case when i.location = 'Warehouse' then 1 end) = 0 order by o.orderno;
And if you want an aggregation per item, you might need the subquery plus a separate aggregation.