LEFT JOIN in DB2

Question

My (abbreviated) Code is:

SELECT *

FROM
ASSESSMENT A
LEFT JOIN FINANCE F0 
ON F0.CLIENT = A.CLIENT
AND F0.FIELD_CODE = 1
AND F0.EVNT_SQNBR =
      (SELECT MAX(FX.EVNT_SQNBR)
      FROM
      FINANCE FX, CROSSREF Y
      WHERE
           Y.CLIENT        = A.CLIENT
       AND Y.ASMT_TCD      = A.ASMT_TCD
       AND Y.ASMT_TY_SQNBR = A.ASMT_TY_SQNBR
       AND FX.CLIENT_ID    = A.CLIENT
       AND FX.FIELD_CODE   = F0.FIELD_CODE
       AND FX.BUS_LN_SQNBR = F0.BUS_LN_SQNBR
       AND FX.EVNT_SQNBR   = Y.EVNT_SQNBR)

LEFT JOIN FINANCE F1 (SAME CODE HERE EXCEPT F1.FIELD_CODE IS DIFFERENT)
...
LEFT JOIN FINANCE F2 (SAME CODE HERE EXCEPT F2.FIELD_CODE IS DIFFERENT)

And so on.

The question is it gives -338 ON Clause is Invalid. For all lines (inclusive) starting with AND F0.EVNT_SQNBR = ...

Any idea why, and how to fix it?

Answer 1

Something like this I guess...

WITH MAXES AS
(
  SELECT FX.CLIENT_ID, FX.FIELD_CODE, FX.BUS_LN_SQNBR, FX.EVNT_SQNBR, Y.ASMT_TCD, Y.ASMT_TY_SQNBR, MAX(FX.EVNT_SQNBR) AS THEMAX
  FROM FINANCE FX
  JOIN CROSSREF Y ON FX.CLIENT_ID = Y.CLIENT AND FX.EVNT_SQNBR = Y.EVNT_SQNBR
  GROUP BY   
    FX.CLIENT_ID   ,
    FX.FIELD_CODE  ,
    FX.BUS_LN_SQNBR,
    FX.EVNT_SQNBR,
    Y.ASMT_TCD,
    Y.ASMT_TY_SQNBR
)
SELECT *
FROM ASSESSMENT A
LEFT JOIN FINANCE F0 ON F0.CLIENT = A.CLIENT AND F0.FIELD_CODE = 1 
LEFT JOIN MAXES M0 ON (M0.THEMAX, M0.ASMT_TCD,  M0.ASMT_TY_SQNBR, M0.CLIENT_ID, M0.FIELD_CODE, M0.BUS_LN_SQNBR) = (F0.EVNT_SQNBR, A.ASMT_TCD, A.ASMT_TY_SQNBR, A.CLIENT, F0.FIELD_CODE, F0.BUS_LN_SQNBR)
LEFT JOIN FINANCE F1 ON F1.CLIENT = A.CLIENT AND F1.FIELD_CODE = 2 
LEFT JOIN MAXES M1 ON (M1.THEMAX, M1.ASMT_TCD,  M1.ASMT_TY_SQNBR, M1.CLIENT_ID, M1.FIELD_CODE, M1.BUS_LN_SQNBR) = (F1.EVNT_SQNBR, A.ASMT_TCD, A.ASMT_TY_SQNBR, A.CLIENT, F0.FIELD_CODE, F1.BUS_LN_SQNBR)
LEFT JOIN FINANCE F2 ON F2.CLIENT = A.CLIENT AND F2.FIELD_CODE = 3 
LEFT JOIN MAXES M2 ON (M2.THEMAX, M2.ASMT_TCD,  M2.ASMT_TY_SQNBR, M2.CLIENT_ID, M2.FIELD_CODE, M2.BUS_LN_SQNBR) = (F2.EVNT_SQNBR, A.ASMT_TCD, A.ASMT_TY_SQNBR, A.CLIENT, F0.FIELD_CODE, F2.BUS_LN_SQNBR)

Answer 2

The qualifier FN0 does not exist in the JOIN SQL Statement. I think you meant F0.

AND FX.FIELD_CODE   = F0.FIELD_CODE
AND FX.BUS_LN_SQNBR = F0.BUS_LN_SQNBR