带子查询的内部连接的DB2 SQL语法

vddsk6oq 于 2023-03-02 发布在 DB2

关注(0)|答案(2)|浏览(83)

我不是很精通SQL，但我的语法似乎是正确的，我得到“TDEST.ENDZONE是无效的，在上下文中使用它”，并没有找到很多信息的内部连接与子查询到目前为止。

select * from LYNX.LEGSUM T2 inner join
(    SELECT
      TLORDER.END_ZONE,LEGSUM.LS_DRIVER, LEGSUM.LS_POWER_UNIT
    FROM
      LYNX.LEGSUM LEGSUM
      LEFT OUTER JOIN LYNX.TLORDER TLORDER ON LEGSUM.LS_DLID = TLORDER.DETAIL_LINE_ID
    WHERE
      TLORDER.ORIGIN = 'PRO8060'
      AND LEGSUM.LS_ACTUAL_DATE >= '2017-01-01 00:00:00'
      AND LEGSUM.LS_ACTUAL_DATE < '2018-01-01 00:00:00'
      AND NOT (
        TLORDER.CURRENT_STATUS = 'CANCEL'
        OR TLORDER.CURRENT_STATUS = 'CANCL'
        OR TLORDER.CURRENT_STATUS = 'ENTRY'
      )
) TDEST
ON TDEST.ENDZONE = T2.LEGSUM.LS_FROM_ZONE
AND TDEST.LS_DRIVER = T2.LS_DRIVER
AND TDEST.LS_POWER_UNIT = T2.LS_POWER_UNIT

如果需要任何解释，我尝试实现的是从LEGSUM表中获取与子查询结果相对应的数据集，然后使用“IN”函数过滤更多数据
谢谢你的帮助

db2

来源：https://stackoverflow.com/questions/49176545/db2-sql-syntax-on-inner-join-with-subquery

2条答案

按热度按时间

2g32fytz1#

重新考虑子查询的使用，因为外部查询和子查询在单元级别共享同一个源表LYNX.LEGSUM。请考虑在此同一个表上合并自联接：

SELECT l2.*
FROM
  LYNX.LEGSUM l
LEFT OUTER JOIN LYNX.TLORDER t
  ON l.LS_DLID = t.DETAIL_LINE_ID
INNER JOIN LYNX.LEGSUM l2
  AND l2.LS_FROM_ZONE = t.END_ZONE
  AND l2.LS_DRIVER = l.LS_DRIVER
  AND l2.LS_POWER_UNIT = l.LS_POWER_UNIT

WHERE
  t.ORIGIN = 'PRO8060'
  AND l.LS_ACTUAL_DATE >= '2017-01-01 00:00:00'
  AND l.LS_ACTUAL_DATE < '2018-01-01 00:00:00'
  AND NOT (
    t.CURRENT_STATUS = 'CANCEL'
    OR t.CURRENT_STATUS = 'CANCL'
    OR t.CURRENT_STATUS = 'ENTRY'
  )

赞(0）回复(0）举报 2023-03-02

9w11ddsr2#

ON子句中使用的END_ZONE中缺少下划线

赞(0）回复(0）举报 2023-03-02