Joining two grouped subqueries IBM i, DB2 SQL

Question

Good afternoon,

I'm trying to create a daily sales report summary joining a few different files and summarizing based on varying factors.

The report will end up as follows:

Category    On Hold    Back Orders    Late    Current Month    Next Month    Future Months    Total
LOC          24.75        50.01       15.00      45.00           25.25           28.03        188.04            
MAR          16.15        21.85       31.20      18.00           25.00           34.05        146.25
....

with various accounting classes. I'm pulling the data from an AS400, IBM I server using DB2 SQL.

I can summarize some of the categories independently, but am having trouble joining them together. For example On Hold:

SELECT x.CDA0CD, SUM(x.CDDUVA) AS "On Hold"     
  FROM (
     SELECT LINES.CDCVNB, /* QUOTE/ORDER NO */
            LINES.CDDCCD, /* ORDER TYPE */
            LINES.CDAITX, /* ITEM NUMBER */ 
            LINES.CDGLCD, /* ITEM CLASS */
            LINES.CDA0CD, /* IAC */
            LINES.CDDUVA, /* LC - NET SALES AMOUNT */
            HOLDS.CKCZCD, /* HOLD CODE */
            HOLDS.CKACP8, /* RELEASE TYPE */            
            LINES.CDZ902  /* TOTAL BACKORDERED QTY */ 
       FROM MBCDREP LINES,
            MBCKREP HOLDS
      WHERE LINES.CDCVNB = HOLDS.CKCVNB AND /* SELECT ONLY LINES FROM ORDERS ON HOLD */ 
            LINES.CDDCCD = 1 AND /* ORDER TYPE: ORDER */  
            LINES.CDZ902 = 0 AND /* NO BACKORDERS */
            HOLDS.CKACP8 != '1') x /* HOLD NOT RELEASED */
GROUP BY x.CDA0CD
WITH ROLLUP

Gives my result:

 Item accounting                              "On Hold"  
 class                                                       
   LAY                                        8,357.290  
   LOC                                          909.570  
   MAR                                        2,666.340  
   MCT                                        4,933.970  
   SAF                                          378.000  
   STM                                          876.550  
   STN                                        4,739.800  
   TAG                                        2,709.280  
   TAP                                        6,670.930  
   VIS                                        1,885.100- 
   -                                         30,356.630

And for Back Orders:

SELECT y.CDA0CD, SUM(y.CDDUVA) AS "Back Orders"
  FROM (
     SELECT CDCVNB, /* QUOTE/ORDER NO */
            CDDCCD, /* ORDER TYPE */
            CDAITX, /* ITEM NUMBER */ 
            CDGLCD, /* ITEM CLASS */
            CDA0CD, /* IAC */
            CDDUVA, /* LC - NET SALES AMOUNT */     
            CDZ902  /* TOTAL BACKORDERED QTY */ 
       FROM MBCDREP
      WHERE CDDCCD = 1 AND /* ORDER TYPE: ORDER */  
            CDZ902 > 0) y /* BACKORDERS QTY GREATER THAN ZERO */
GROUP BY y.CDA0CD
WITH ROLLUP

With results:

 Item accounting                          "Back Orders"  
 class                                                       
  LOC                                           24.750  
  MAR                                          917.580  
  MCT                                       67,366.110  
  OTH                                             .000  
  STM                                           10.580  
  TAG                                        3,225.440  
  TAP                                          106.310  
  VIS                                       16,675.380  
  -                                         88,326.150

I want to join those together, plus other summaries based on the above goal. I may need a full outer join, which isn't supported in DB2, so I tried a convoluted Left Outer Join and a Right Exceptions, to no avail (sort of following this suggestion: https://www.mcpressonline.com/analytics-cognitive/db2/techtip-full-outer-joins-on-db2-for-i5os):

SELECT x.CDA0CD, x.CDDUVA AS On_Hold     
FROM (
     SELECT LINES.CDCVNB, /* QUOTE/ORDER NO */
            LINES.CDDCCD, /* ORDER TYPE */
            LINES.CDAITX, /* ITEM NUMBER */ 
            LINES.CDGLCD, /* ITEM CLASS */
            LINES.CDA0CD, /* IAC */
            LINES.CDDUVA, /* LC - NET SALES AMOUNT */
            HOLDS.CKCZCD, /* HOLD CODE */
            HOLDS.CKACP8, /* RELEASE TYPE */            
            LINES.CDZ902  /* TOTAL BACKORDERED QTY */ 
       FROM MBCDREP LINES,
            MBCKREP HOLDS
      WHERE LINES.CDCVNB = HOLDS.CKCVNB AND /* SELECT ONLY LINES FROM ORDERS 
ON HOLD */ 
            LINES.CDDCCD = 1 AND /* ORDER TYPE: ORDER */  
            LINES.CDZ902 = 0 AND /* NO BACKORDERS */
            HOLDS.CKACP8 != '1') x /* HOLD NOT RELEASED */
LEFT OUTER JOIN  
 (SELECT *
       FROM MBCDREP
      WHERE CDDCCD = 1 AND /* ORDER TYPE: ORDER */  
            CDZ902 > 0) y /* BACKORDERS QTY GREATER THAN ZERO */
ON x.CDA0CD = y.CDA0CD
UNION 
(SELECT yy.CDA0CD, 
    yy.CDDUVA AS Back_Orders 
FROM (
     SELECT CDCVNB, /* QUOTE/ORDER NO */
            CDDCCD, /* ORDER TYPE */
            CDAITX, /* ITEM NUMBER */ 
            CDGLCD, /* ITEM CLASS */
            CDA0CD, /* IAC */
            CDDUVA, /* LC - NET SALES AMOUNT */     
            CDZ902  /* TOTAL BACKORDERED QTY */ 
       FROM MBCDREP
      WHERE CDDCCD = 1 AND /* ORDER TYPE: ORDER */  
            CDZ902 > 0) yy
EXCEPTION JOIN 
(SELECT xx.CDA0CD, xx.CDDUVA AS On_Hold     
FROM (
     SELECT LINES.CDCVNB, /* QUOTE/ORDER NO */
            LINES.CDDCCD, /* ORDER TYPE */
            LINES.CDAITX, /* ITEM NUMBER */ 
            LINES.CDGLCD, /* ITEM CLASS */
            LINES.CDA0CD, /* IAC */
            LINES.CDDUVA, /* LC - NET SALES AMOUNT */
            HOLDS.CKCZCD, /* HOLD CODE */
            HOLDS.CKACP8, /* RELEASE TYPE */            
            LINES.CDZ902  /* TOTAL BACKORDERED QTY */ 
       FROM MBCDREP LINES,
            MBCKREP HOLDS
      WHERE LINES.CDCVNB = HOLDS.CKCVNB AND /* SELECT ONLY LINES FROM ORDERS 
ON HOLD */ 
            LINES.CDDCCD = 1 AND /* ORDER TYPE: ORDER */  
            LINES.CDZ902 = 0 AND /* NO BACKORDERS */
            HOLDS.CKACP8 != '1') xx) xxx 
    on yy.CDA0CD = xxx.CDA0CD   
            )

Which only gives me On hold...

Item accounting              ON_HOLD   
class                                  
  TAP                     59.160   
  LAY                  1,668.000   
  LOC                     27.230   
  STM                     83.490   
  STM                     25.120   
  STM                     75.370   
  LAY                      1.500   
  LAY                     18.270   
  STN                     61.580   
  LAY                     23.040   
  MAR                      2.400   
  LAY                    218.680   
  TAG                    523.980   
  TAG                    524.040   
  LAY                  1,819.800   
  MCT                    470.400   
  TAG                     65.930   
  TAG                     33.630   
....

Please help! I know there's got to be a simpler way. I'm having date format issues, too, but I'll post that as a separate question, since it's technically a different issue, though I'll end up joining them all together.

Thanks.

You could start with `select CDA0CD from table1 union select CDA0CD from table2 union ...` and then `left join` the other tables against this — giorgiga, Feb 12 '18 at 22:42
Could you flesh this out a little bit, please? Are you suggesting something different than danny117's answer? — Sescopeland, Feb 12 '18 at 22:47

score 1 · Answer 1 · answered Feb 12 '18 at 22:37

Here's a technique that creates a T column for "On Hold" on "Back Order". You just have three columns in the result. Easy to add more type aka T. Really easy to understand output format. You'll have to try it. I may have syntax errors as I've just typed in. I can take this technique out to a statement of 10000 characters so its pretty flexible. Not a fast query but once run the info you need is in just three columns. Maybe you find what your looking for.

SELECT 'On Hold' as t, x.CDA0CD, SUM(x.CDDUVA) AS sumx    
  FROM (
     SELECT LINES.CDCVNB, /* QUOTE/ORDER NO */
            LINES.CDDCCD, /* ORDER TYPE */
            LINES.CDAITX, /* ITEM NUMBER */ 
            LINES.CDGLCD, /* ITEM CLASS */
            LINES.CDA0CD, /* IAC */
            LINES.CDDUVA, /* LC - NET SALES AMOUNT */
            HOLDS.CKCZCD, /* HOLD CODE */
            HOLDS.CKACP8, /* RELEASE TYPE */            
            LINES.CDZ902  /* TOTAL BACKORDERED QTY */ 
       FROM MBCDREP LINES,
            MBCKREP HOLDS
      WHERE LINES.CDCVNB = HOLDS.CKCVNB AND /* SELECT ONLY LINES FROM ORDERS ON HOLD */ 
            LINES.CDDCCD = 1 AND /* ORDER TYPE: ORDER */  
            LINES.CDZ902 = 0 AND /* NO BACKORDERS */
            HOLDS.CKACP8 != '1') x /* HOLD NOT RELEASED */
GROUP BY 'On Hold', x.CDA0CD
WITH ROLLUP 
union all
SELECT 'Back Orders' as t, y.CDA0CD, SUM(y.CDDUVA) AS sumx
  FROM (
     SELECT CDCVNB, /* QUOTE/ORDER NO */
            CDDCCD, /* ORDER TYPE */
            CDAITX, /* ITEM NUMBER */ 
            CDGLCD, /* ITEM CLASS */
            CDA0CD, /* IAC */
            CDDUVA, /* LC - NET SALES AMOUNT */     
            CDZ902  /* TOTAL BACKORDERED QTY */ 
       FROM MBCDREP
      WHERE CDDCCD = 1 AND /* ORDER TYPE: ORDER */  
            CDZ902 > 0) y /* BACKORDERS QTY GREATER THAN ZERO */
GROUP BY y.CDA0CD
WITH ROLLUP

Okay, so I see the results are stacked vertically. Is there an easy way to then have them side by side? Basically you've done a row bind and I want a column bind, if that makes sense... — Sescopeland, Feb 12 '18 at 22:44
@Sescopeland Search for How to pivot?. You wont even need my answer. Its just another technique to see data. — danny117, Feb 13 '18 at 15:12
unknowingly, you helped me solve a separate issue I've been mulling over for months. I kept searching "transpose" but what I needed was "pivot" — Sescopeland, Feb 13 '18 at 15:32

score 1 · Accepted Answer · answered Feb 13 '18 at 02:46

1

Try this

select CDA0CD , sum(holdvalue) , sum(backordervalue) from ( /* hold query / select CDA0CD , SUM(CDDUVA) as holdvalue , 0 as backordervalue ...... / hold query / union all / backorder query / select CDA0CD , 0 as holdvalue , SUM(CDDUVA) as backordervalue ...... / backorder query */ ) group by CDA0CD

answered Feb 13 '18 at 02:46

Satya

583
1
4
6

Simple, concise, and easy to add to. This is perfect @Satya! @danny117's solution grouped each individually first which was frustrating, but by grouping at the end, and using the "0 as ..." was brilliant. – Sescopeland Feb 13 '18 at 15:30

score 0 · Answer 3 · answered Feb 12 '18 at 23:06

Reader beware:

Please note this is just a suggestion: I am not a DB2 user and do not have an instance available to test. I'm posting this as an answer rather than a comment only because the OP asked me to elaborate and comments don't have enough space.

You seem to have a bunch of queries that are all in the form:

select CDA0CD, foo, bar, baz from ...

but you miss a table of all CDA0CD to join against (think: you miss the fact table in a star schema).

You can "simulate" that table in a subquery, eg. with something like:

with CDA0CD as (
          select CDA0CD from whatever1
    union select CDA0CD from whatever2
    union select CDA0CD from whatever3
    union ...
) on_hold as (
    select CDA0CD, foo, bar, baz from whatever1
), back_orders as (
    select CDA0CD, foo, bar, baz from whatever2
), late as (
    ...
)
select CDA0CD.CDA0CD,
       on_hold.foo, on_hold.bar, on_hold.baz,
       back_orders.foo, back_orders.bar, back_orders.baz,
       ...
  from CDA0CD
       left join on_hold on on_hold.CDA0CD = CDA0CD.CDA0CD
       left join ...

or maybe even

with on_hold as (
    select CDA0CD, foo, bar, baz from whatever
), back_orders as (
    select CDA0CD, foo, bar, baz from whatever
), late as (
    ...
), CDA0CD as (
          select CDA0CD from on_hold
    union select CDA0CD from back_orders
    union select CDA0CD from late
)
select CDA0CD.CDA0CD,
       on_hold.foo, on_hold.bar, on_hold.baz,
       back_orders.foo, back_orders.bar, back_orders.baz,
       ...
  from CDA0CD
       left join on_hold on on_hold.CDA0CD = CDA0CD.CDA0CD
       left join ...

Joining two grouped subqueries IBM i, DB2 SQL

3 Answers3