在DB2中LEFT JOIN | 在DB2中LEFT

本文介绍了在DB2中LEFT JOIN的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我的（缩写）代码是：

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?

推荐答案

这样的事我猜...

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中LEFT JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

1403页，肝出来的..