How to LEFT JOIN in DB2 iseries with first row?

Question

I have need a query that JOIN a TABLE with A first row of other table value based:

 SELECT * FROM TABLEA A LEFT JOIN
    (SELECT * from TABLEB
       WHERE FIELD1 <> '3' and FIELD2 = 'D' AND A.CODE=CODE
      FETCH FIRST 1 ROW ONLY
     ) B
 on a.FIELDA = b.FIELDA
 and A.FIELDB = B.FIELDB

but DB2 return ERROR because can't use A.CODE

How can solve this?

Answer 1

You need to use the nested table expression:

SELECT * FROM TABLEA A LEFT JOIN
  LATERAL (SELECT * from TABLEB
     WHERE FIELD1 <> '3' and FIELD2 = 'D' AND A.CODE=CODE
     FETCH FIRST 1 ROW ONLY
  ) B
on a.FIELDA = b.FIELDA
and A.FIELDB = B.FIELDB