CODEWITHSUNDEEP
sqlibm-midrangedb2-400rpgle
Tretiak
Tretiak

Reputation: 59

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;

Upvotes: 0

Views: 615

Answers (2)

ChrisHiebert
ChrisHiebert

Reputation: 200

This query can be used to get all records from HPOL07 and match to one record in @POA

SELECT HPOL07.*, @POA.*
FROM HPOL07

CROSS JOIN LATERAL (
SELECT @POA.*
FROM @POA
WHERE PORD = @POA.POAPON  AND PLINE = @POA.POALNO
ORDER BY @POA.<SEQUENCE NUMBER> DESC
FETCH FIRST 1 ROWS ONLY
) @POA 

WHERE PORD = :NBR AND PID <> 'PZ'

Upvotes: 0

Andrew
Andrew

Reputation: 8758

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

Upvotes: 0

Related Questions