My previous question was mostly solved by using MAX values to look up single value columns.
However there is a result needed which may REALLY need some sort of sub-query. Please excuse this long-winded description!
Each order may have many SKU's. Each SKU as it's own Items-per-case value in the Inventory Master table. The current report (created by Query/400) gets the values correctly, but I can't re-create this with pure SQL.
What needs to happen is each SKU on an order must be used to get it's ITCSPK (items-per-case) value from the Master Inventory table (WITMB). Then the integer value of ITEMQTY/ITCSPK for each is summed. Then the same values are used but the remainders of each are summed to produce the total remainders or "each" items - that are less than case size.
I don't see how this can be done with standard aggregate values - some sort of iteration appears to be in order.
SELECT MAX(t3.POORDN) AS ORDNUM,MAX(t3.POORTY) AS ORDTYP,MAX(t3.POOPRI) AS ORDPRI,DECIMAL(COUNT(t2.ipordn),7,0) AS LINES,SUM(t2.IPORDQ) AS EACHQTY,/* In this section I belive a sub-query may be neededThe result valuess needed are two values:Total full cases,total "remainder" or "each" items Each SKU on the order has a different items-per-case value in the master inventory table*/ The code outside this block executes as expected.MAX(t3.POGWGT) AS GROSWT,MAX(t3.POGVOL) AS GROVOL,MAX(t3.POGVOL)/61440 AS ESTPAL,from hbcprodfl.witmb as t1JOIN hbcprodfl.wopib AS t2 on T2.IPSKU = T1.ITSKUJOIN HBCPRODFL.WOPHB AS t3 ON t2.IPORDN = t3.POORDNWHERE t1.ITCSPK > 0GROUP BY t2.IPORDNORDER BY t2.ipordn
If I understand this correctly, you're happy with the query so far, and just need to add ITCSPK to each line. (Any computations using ITEMQTY and ITCSPK is trivial once the data is associated with the row.)
Several different SQL structures/formats will do this. Perhaps the easiest one is to join WITMB into the current result set, presumably on the SKU. You've already done that, so it should be just a simple matter of including that value on the list of items selected.
It's interesting that you group by t2.IPORDN, but don't actually select the value of IPORDN. You'll need to add ITCSPK to the GROUP BY clause.
Try the query below and see if that doesn't come close. Then just modify it to compute the value that you need.
Open in new window