Search code examples
sqlibm-midrangedb2-400rpgle

DB2 SQL - Inner Join get last row

I am trying to get the last record from table @POA with the same PO # and Line # in table HPOL07. The records in @POA have a sequence number. So I am trying to get the record with the last sequence number. I am not sure how to do this?

EXEC SQL Declare RSCURSOR cursor for
SELECT HPOL07.*, @POA.*
FROM HPOL07
INNER JOIN @POA ON PORD = @POA.POAPON  AND PLINE = @POA.POALNO
WHERE PORD = :NBR AND PID <> 'PZ';

EXEC SQL  Open RSCURSOR;

EXEC SQL SET RESULT SETS Cursor RSCURSOR;
Solution

  • Won't swear this works on DB2 400, but I think it will.

        SELECT HPOL07.*, @POA.*
    FROM HPOL07
    INNER JOIN @POA ON PORD = @POA.POAPON  AND PLINE = @POA.POALNO
    WHERE PORD = :NBR AND PID <> 'PZ'
    ORDER BY @POA.<SEQUENCE NUMBER> DESC
FETCH FIRST 1 ROWS ONLY