How to SUM in JOIN, to avoid issue with two SUM in SELECT statement

Question

I have read numerous posts regarding a similar issue, but none have lead to a solution so I am posting a new question. These are two in particular that address similar issues:

SQL SELECT with multiple tables and SUM

How to write subquery inside the OUTER JOIN Statement

This is the first version of the code I had and the issue it created. This is in a DB2 for iSeries database. I just get a generic ODBC error 1004, which means the SQL is fine, but the database doesn't like something about the code. I have verified table names and column names are correct.

SELECT i.IMFGR || i.ICOLOR || i.IPATT Item, i.INAME DESC1, i.INAME2 DESC2, 
       i.IPOL1 PC1, i.IPOL2 PC2, i.IPOL3 PC3, SUM(s.JONHAN) QOH, i.IUNITS UM, 
       SUM(inv.INEXTP) Sales, i.IINVEN INV, i.ICCTR CC, i.ICLAS1 I1, i.IDISCD DDate, 
       i.ILPODT LOrder, i.IAVGC AVGC 
FROM QS36F.ITEM i
JOIN QS36F.ITEMSTK s
     ON i.IMFGR || i.ICOLOR || i.IPATT = s.JMFGR || s.JCOLOR || s.JPAT
        JOIN QS36F.INVOICED inv
     ON inv.INMFGR || inv.INCOLO || inv.INPATT = i.IMFGR || i.ICOLOR || i.IPATT
WHERE (i.IMFGR = '" & man & "') AND ((i.IPOL1 = 'SP') OR (i.IPOL2 = 'SP') OR 
      (i.IPOL3 = 'SP') OR (i.IPOL1 = 'DI') OR (i.IPOL2 = 'DI') OR (i.IPOL3 = 'DI'))
GROUP BY i.IMFGR || i.ICOLOR || i.IPATT, i.INAME, i.INAME2, i.IPOL1, i.IPOL2, i.IPOL3, 
         i.IUNITS, i.IINVEN, i.ICCTR, i.ICLAS1, i.IDISCD, i.ILPODT, i.IAVGC
ORDER BY i.IMFGR || i.ICOLOR || i.IPATT

This wasn't working because it was causing the SUM(s.JONHAN) QOH to be multiplied many times over, so is thus inaccurate.

After reading some threads, it looks like I need to put the second SUM in a JOIN statement. However, I am still getting an ODBC error 1004, so I am not sure what I missing.

SELECT i.IMFGR || i.ICOLOR || i.IPATT Item, i.INAME DESC1, i.INAME2 DESC2, 
       i.IPOL1 PC1, i.IPOL2 PC2, i.IPOL3 PC3, SUM(s.JONHAN) QOH, i.IUNITS UM, 
       inv.Sales, i.IINVEN INV, i.ICCTR CC, i.ICLAS1 I1, i.IDISCD DDate,
       i.ILPODT LOrder, i.IAVGC AVGC 
FROM QS36F.ITEM i
JOIN QS36F.ITEMSTK s
     ON i.IMFGR || i.ICOLOR || i.IPATT = s.JMFGR || s.JCOLOR || s.JPAT
LEFT OUTER JOIN (SELECT INMFGR || INCOLO || INPATT Item, SUM(INEXTP) Sales
                 FROM QS36F.INVOICED
                 GROUP BY INMFGR, INCOLO, INPATT) inv
                     ON inv.Item = i.IMFGR || i.ICOLOR || i.IPATT
WHERE (i.IMFGR = '" & man & "') AND ((i.IPOL1 = 'SP') OR (i.IPOL2 = 'SP') OR 
      (i.IPOL3 = 'SP') OR (i.IPOL1 = 'DI') OR (i.IPOL2 = 'DI') OR (i.IPOL3 = 'DI'))
GROUP BY i.IMFGR || i.ICOLOR || i.IPATT, i.INAME, i.INAME2, i.IPOL1, i.IPOL2, i.IPOL3, 
         i.IUNITS, i.IINVEN, i.ICCTR, i.ICLAS1, i.IDISCD, i.ILPODT, i.IAVGC
ORDER BY i.IMFGR || i.ICOLOR || i.IPATT

Everything plays nice until the second SUM and JOIN statement is added. I can do a query on the SELECT in the second JOIN and it runs as it should.

Answer 1

First off, this:

JOIN QS36F.ITEMSTK s
     ON i.IMFGR || i.ICOLOR || i.IPATT = s.JMFGR || s.JCOLOR || s.JPAT

Is a bad idea, it should really be

JOIN QS36F.ITEMSTK s
     ON i.IMFGR = s.JMFGR
        and i.ICOLOR = s.JCOLOR 
        and i.IPATT =  s.JPAT

Secondly, I don't see anything obviously wrong. It'd help if you could look at the joblog of the QZDASOINIT job servicing the ODBC request and report the actual error thrown by DB2.

Having said that, my preference is for Common Table Expressions (CTE) as opposed to Nested Table Expressions (NTE)

WITH inv as (SELECT INMFGR, INCOLO, INPATT, SUM(INEXTP) Sales
               FROM QS36F.INVOICED
              GROUP BY INMFGR, INCOLO, INPATT
             )
, s as (SELECT JMFGR, JCOLOR, JPAT, sum(JOHHAN) TotalOH
          FROM QS36F.ITEMSTK
         GROUP BY JMFGR, JCOLOR, JPAT
       )
SELECT i.IMFGR || i.ICOLOR || i.IPATT Item, i.INAME DESC1, i.INAME2 DESC2, 
       i.IPOL1 PC1, i.IPOL2 PC2, i.IPOL3 PC3, s.TotalOH QOH, i.IUNITS UM, 
       inv.Sales, i.IINVEN INV, i.ICCTR CC, i.ICLAS1 I1, i.IDISCD DDate,
       i.ILPODT LOrder, i.IAVGC AVGC 
FROM QS36F.ITEM i
JOIN s
      ON i.IMFGR = s.INMFGR
         and i.ICOLOR = s.INCOLOR 
         and i.IPATT =  s.INPATT
LEFT OUTER JOIN inv
       ON i.IMFGR = inv.JMFGR
            and i.ICOLOR = inv.JCOLOR 
            and i.IPATT =  inv.JPAT
WHERE (i.IMFGR = '" & man & "') AND ((i.IPOL1 = 'SP') OR (i.IPOL2 = 'SP') OR 
      (i.IPOL3 = 'SP') OR (i.IPOL1 = 'DI') OR (i.IPOL2 = 'DI') OR (i.IPOL3 = 'DI'))
ORDER BY i.IMFGR, i.ICOLOR, i.IPATT