title: Joining two grouped subqueries IBM i, DB2 SQL
body: <p>Good afternoon,</p>
  
  <p>I'm trying to create a daily sales report summary joining a few different files and summarizing based on varying factors.</p>
  
  <p>The report will end up as follows:</p>
  
  <pre><code>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
  ....
  </code></pre>
  
  <p>with various accounting classes. I'm pulling the data from an AS400, IBM I server using DB2 SQL. </p>
  
  <p>I can summarize some of the categories independently, but am having trouble joining them together. For example On Hold:</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>Gives my result:</p>
  
  <pre><code> 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  
  </code></pre>
  
  <p>And for Back Orders:</p>
  
  <pre><code>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 &gt; 0) y /* BACKORDERS QTY GREATER THAN ZERO */
  GROUP BY y.CDA0CD
  WITH ROLLUP
  </code></pre>
  
  <p>With results:</p>
  
  <pre><code> 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  
  </code></pre>
  
  <p>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: <a href="https://www.mcpressonline.com/analytics-cognitive/db2/techtip-full-outer-joins-on-db2-for-i5os" rel="nofollow noreferrer">https://www.mcpressonline.com/analytics-cognitive/db2/techtip-full-outer-joins-on-db2-for-i5os</a>):</p>
  
  <pre><code>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 &gt; 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 &gt; 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   
              )
  </code></pre>
  
  <p>Which only gives me On hold...</p>
  
  <pre><code>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   
  ....
  </code></pre>
  
  <p>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.</p>
  
  <p>Thanks.</p>