Join 2 tables and subselect (maybe....)

[Coder7]

Hello gurus.

My brain is twisted trying to get this to work and I could really use your help, please. If there is a duplicate value for the max(chg_eff_date), then I must also use the max(sel_seq_nbr) to get the correct row.

Here's the data, the results I need and my query (currently getting an error because the predicates in the subselect's where clause are unrecognized).
------------------
The data is from:
2 cobol variables and 2 db2 tables
-----
cobol variables:
:cust_agrmt_key = 7991272
arty_key = 496528291
-----
sfg4d db2 table:
cust_agrmt_key mbr_seq_nbr party_key
7991272 1 496528291
-----
sfg49 db2 table:
cust_agrmt_key mbr_seq_nbr sel_seq_nbr chg_eff_date
7991272 1 4 07/01/95
7991272 1 3 07/01/95
7991272 1 2 11/03/94
7991272 1 1 11/01/91
-----
sfg49 table (CONTINUED):
cov_eff_date cov_end_date pkg_seq_nbr end_reas_cd
07/01/95 01/31/96 3 rde
07/01/95 03/01/95 3 cls
11/03/94 06/30/95 2 gr
11/01/91 11/02/94 1 gca
-----
the desired results:

cust_agrmt_key mbr_seq_nbr pkg_seq_nbr end_reas_cd
7991272 1 3 rde

chg_eff_date
07/01/95

my query:

SELECT DISTINCT C.CUST_AGRMT_KEY
,C.MBR_SEQ_NBR
,C.PKG_SEQ_NBR
,C.END_REAS_CD
,C.MAX(CHG_EFF_DATE)
FROM HUM.A_SFG4D SFG4D, HUM.A_SFG49 SFG49,

(SELECT CUST_AGRMT_KEY
,MBR_SEQ_NBR
,PKG_SEQ_NBR
,END_REAS_CD
,MAX(SEL_SEQ_NBR) AS SSN
,MAX(CHG_EFF_DATE)AS CED
FROM HUM.A_SFG49
WHERE CUST_AGRMT_KEY = SFG4D.CUST_AGRMT_KEY
WHERE MBR_SEQ_NBR = SFG4D.MBR_SEQ_NBR
AND ((COV_END_DATE IS NULL) OR (COV_EFF_DATE <> COV_END_DATE))
GROUP BY CUST_AGRMT_KEY
,MBR_SEQ_NBR
,PKG_SEQ_NBR
,END_REAS_CD
,CHG_EFF_DATE
HAVING MAX(CHG_EFF_DATE) = CHG_EFF_DATE) AS C
WHERE SFG4D.CUST_AGRMT_KEY = :7991272
AND SFG4D.PARTY_KEY = 496528291
AND SFG49.CUST_AGRMT_KEY = SFG4D.CUST_AGRMT_KEY
AND SFG49.MBR_SEQ_NBR = SFG4D.MBR_SEQ_NBR

 

[BrianTyler]

Break the query down by using a sub-select to determine the maximum date, and then have an outer query to get the maximum sequence number where the date equals the date from the subquery.

Basically
Select
..... , max(seq_no)
from
...
where
..... and
date =
(select
max(date)
from
...
where
..

(As this is embedded SQL, the two queries could be written and executed seperately, i.e. make Cobol to more of the logic))

Another solution is to ignore the seqno in the selection in the main query and sort on it (descending). Add a clause 'fetch first 1 rows only' at the end to get a singleton result.

 

[BrianTyler]

You may also like to look at some of the OLAP functions:

http://www-106.ibm.com/developerworks/db2/library/techarticle/lyle/0110lyle.html

gives some examples