DB2 中的左连接

问题描述

我的(缩写)代码是:

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)

等等.

问题是它给出了 -338 ON 子句无效.对于以 AND F0.EVNT_SQNBR = ... 开头的所有行(含)

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?

推荐答案

我猜是这样的......

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)

这篇关于DB2 中的左连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！