CODEWITHSUNDEEP
sqldb2iseries-navigator
Daniele Grillo
Daniele Grillo

Reputation: 1023

How to LEFT JOIN in DB2 iseries with first row?

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?

Upvotes: 3

Views: 22179

Answers (3)

WarrenT
WarrenT

Reputation: 4542

You can use the OLAP function row_number() to rank the records according to somefield(s) within a (fielda,fieldb,code) group. Somefield might be a transaction id, or sequence, for example. The order by clause is optional there, but without it, you might be randomly picking which record is the first in the group.

 WITH B AS
 (SELECT *,
         row_number() over (partition by fielda,fieldb,code
                            order     by somefield
                           ) as pick 
    from TABLEB
    WHERE FIELD1 <> '3'
      and FIELD2 = 'D'
 )
 SELECT * 
   FROM TABLEA A LEFT JOIN B

       on a.FIELDA = b.FIELDA
      and A.FIELDB = B.FIELDB
      and A.CODE   = B.CODE
   where pick=1

Upvotes: 0

danny117
danny117

Reputation: 5651

This is a highly optimized statement. Your not getting any data from tableb and your going for first row so you just need exists clause. 

select a.* from tablea a 
where exists (select * from tableb b 
    where a.fielda = b.fielda 
    and a.fieldb = b.fieldb 
    and b.code = a.code 
    and b.field2 = 'd' and b.field1 <> '3')

Upvotes: 1

mustaccio
mustaccio

Reputation: 19001

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

Upvotes: 11

Related Questions