DB2 SQL Syntax on inner join with subquery

Question

I am getting "TDEST.ENDZONE is not valid in the context where it is used".

    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

I am trying to get a data set from my LEGSUM table that corresponds to the result of my subquery which will then be used to filter more data using IN.

Answer 1

Reconsider the use of a subquery since outer and subquery share same source table, LYNX.LEGSUM, at unit level. Consider incorporating a self-join on this same table:

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'
  )

Answer 2

You are missing Underscore in the END_ZONE used in ON clause