October 6, 2008 10:24am pdt

1. momi_sa... 230,085
2. Kdo 193,257
3. daveslash 67,363
4. ghp7000 18,593
5. tliotta 16,904
6. murphey2 13,525
7. Lowfatsp... 8,821
8. TomasHelgi 8,190
9. rward 8,000
10. mark_wills 6,700
11. ocgstyles 6,086
12. angelIII 5,656
13. jinesh_ka... 4,000
14. giltjr 3,600
15. HonorGod 3,000
16. mlmcc 2,998
17. sdstuber 2,900
18. BillBach 2,800
19. chapmandew 2,750
20. Gary_Th... 2,600
21. TheLearn... 2,500
22. TextReport 2,375
23. jafastinger 2,370
24. objects 2,000
24. _agx_ 2,000
24. multithrea... 2,000
24. aiklamha 2,000
24. hernst42 2,000
24. hielo 2,000
24. Vernster 2,000
24. samtran0... 2,000
24. anarki_ji... 2,000
24. gravit9 2,000
24. ghostdog74 2,000

1. Kdo 518,571
2. momi_sa... 462,151
3. ghp7000 359,879
4. daveslash 183,009
5. Lowfatsp... 132,133
6. BigSchmuh 98,198
7. tliotta 89,253
8. mglxxx 77,570
9. sachinwa... 74,271
10. bondtrader 44,457
11. db2inst1 40,611
12. ocgstyles 37,664
13. shalomc 33,735
14. waynezhu 17,756
15. granbajo 17,627
16. yongsing 17,097
17. angelIII 16,581
18. murphey2 13,525
19. askanivg 13,235
20. jdlambert1 12,172
21. srielau 10,356
22. TomasHelgi 9,079
23. dr_marko 8,360
24. rward 8,000
25. elfe69 7,552

07.16.2008 at 01:59PM PDT, ID: 23571280

	[x] Attachment Details

Still may need sub query

Asked by Harrzack in DB2 Database, AS / 400

Tags: sql, db2

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.Start Free Trial

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:

           
           
             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

           
         

Related Solutions: Need assistance writting SQL which may use Sub-Querys

Keywords: Still may need sub query

	Title
1	MS Access Inventory control
2	Insert with query and sub query
3	Simple sql sub-query question (new…
4	Algorithm resources for Inventory DB
5	Counting Inventory
6	Using EVAL in a query

Loading Advertisement...

20080716-EE-VQP-32 / EE_QW_Related_20080208

Still may need sub query

Asked by Harrzack in DB2 Database, AS / 400

Tags: sql, db2

About this solution