TableB( KEY PO, SEQ)
PO SEQ STATUS
11 1 A
11 2 B
11 3 C
21 1 A
21 2 B
21 3 C
31 1 A
OUTPUT
PO LOC SEQ STATUS ORD_DT
11 111 3 P 2005-11-02
21 222 3 P 2005-11-03
31 3 1 A 2005-11-05
If the PO occurs multiple times then I want to have status as P and Sequence as maximum sequence,
Latest Order date otherwise I want the actual value from table.
Try the following SQL (tested in DB2 for z/OS V8 ):
Code:
WITH Y as (SELECT PO, MAX(SEQ) as max_seq, count(*) as num1
from tableb group by po)
select a.PO, a.LOC, B.B_SEQ, B.B_STATUS, A.ORD_DT
FROM tablea as A,
(select Y.PO as B_PO, Y.MAX_SEQ as B_SEQ,
case when num1 = 1 then X.status else 'P' end as b_status
from tableb as X, Y
where (X.PO, X.SEQ) = (Y.PO, Y.MAX_SEQ) ) as B
where A.PO = B.B_PO
and (A.PO, A.LOC) in
(select PO, MAX(LOC) from tablea group by PO)
Hi
Thanks for the post.
Can you please explain me the following SQL.
select Y.PO as B_PO, Y.MAX_SEQ as B_SEQ,
case when num1 = 1 then X.status else 'P' end as b_status
from tableb as X, Y
where (X.PO, X.SEQ) = (Y.PO, Y.MAX_SEQ) ) as B
where A.PO = B.B_PO
result B store data with 3 column3: PO, B_SEQ, B_STATUS.
each different PO from tableb will generate one record in B, with the max SEQ of the PO, and B_STATUS will be 'P' if there is more than 1 record for the PO in table b, or it will be the status of the record if there is only 1 record for the PO
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
