My reqt is to extract all the rows having Acct No, Eff date, Exp date, Cov date from Table A, wherein we should take unique record of Acct No, Eff Date, Exp date and then load it to flat file.
My SQL is
DECLARE CSR CURSOR FOR
FROM <TABLE A>
GROUP BY ACCT_NO, EFF_DATE, EXP_DATE;
For getting unqiue row of Acct No,Eff date, Exp Date, - I have used GROUP BY, however for GROUP BY to be correct, I should have COV_DATE too in the GROUP BY clause. But I don't want to check the uniqueness of COV_DATE.
what if there are two records with the same ACCT_NO,EFF_DATE,EXP_DATE but with a different COV_DATE, which one should be in the output ?
the answer could be the lowest cov_date : select min(COV_DATE) the answer could also be first(cov_date) or any(cod_date) , but these don't work in DB2 z/OS
If you have multiple columns besides cov_date and the values must be from the same row, you still need an extra criteria : fe. the ones with the lowest primary key.
thus your select would look like
FROM <TABLE A> A
where pk = (select min(pk) from <TABLE A> B
where a.ACCT_NO = b.ACCT_NO
and a.EFF_DATE = b.EFF_DATE
and a.EXP_DATE = b.EXP_DATE)
without the group by
if your primary key is multiple columns it gets more complicated.