DB2 - Using left join in a subquery

Question

I managed to build a select that is working.

Now I need to JOIN to another table.

SELECT
    t.TICKETID -- the other table
    ,c.OWNERID 
    ,c.CREATEDATE
FROM 
    (
      SELECT 
        *
      , ROW_NUMBER () OVER (PARTITION BY c.OWNERID  ORDER BY c.CREATEDATE) AS RN_
      FROM  MAXIMO.COMMLOG c
      LEFT JOIN maximo.TICKET t  on t.TICKETUID  = c.OWNERID
      WHERE c.UNIQUEID = '468'
      AND c.OWNERID = '4355070'
    ) c
WHERE RN_ = 1
ORDER BY c.CREATEDATE DESC

Result without JOIN

OWNERID	CREATEDATE
16031985	22022-08-11-08.02.30.351000

Expected result with JOIN

TICKETID	OWNERID	CREATEDATE
123456	16031985	2022-08-11-08.02.30.351000

Error for JOIN

Error SQL [42601]: An unexpected token "." was found following "c". Expected tokens may include: ",".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.31.10

Answer 1

Correlation names t and c of the subselect are not visible in the outer SELECT.
You reuse c in outer and this is why you can use this correlation name to qualify the columns there.
Try this.

SELECT
     c.TICKETID -- Here is the information from the other table
    ,c.OWNERID 
    ,c.CREATEDATE

FROM 
    (
      SELECT 
        c.*
      , t.TICKETID
      , ROW_NUMBER () OVER (PARTITION BY c.OWNERID  ORDER BY c.CREATEDATE) AS RN_
      FROM  MAXIMO.COMMLOG c
      left join maximo.TICKET t  on t.TICKETUID  = c.OWNERID -- here is my join
      WHERE c.UNIQUEID = '468'
      AND c.OWNERID = '4355070'
    ) c
WHERE RN_ = 1
ORDER BY c.CREATEDATE DESC