troubleshooting Question

Still may need sub query

Avatar of Harrzack
HarrzackFlag for United States of America asked on
Operating SystemsDB2
20 Comments1 Solution700 ViewsLast Modified:
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 needed
 
The 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 t1
JOIN hbcprodfl.wopib AS t2 on T2.IPSKU = T1.ITSKU
JOIN HBCPRODFL.WOPHB AS t3 ON t2.IPORDN = t3.POORDN
WHERE t1.ITCSPK > 0
GROUP BY t2.IPORDN
ORDER BY t2.ipordn
ASKER CERTIFIED SOLUTION
Kent Olsen
Data Warehouse / Database Architect

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 20 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 20 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros