|
View previous topic :: View next topic
|
| Author |
Message |
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
Hi all,
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
| Code: |
DECLARE CSR CURSOR FOR
SELECT ACCT_NO,EFF_DATE,EXP_DATE,COV_DATE
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.
Can anyone please help me in this.
Thanks
Vinu |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
One way to do what you want is to use ORDER BY rather than GROUP BY and when fetching the rows, bypass any duplicates in the program code (not the sql). . . |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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
| Code: |
SELECT ACCT_NO,EFF_DATE,EXP_DATE,COV_DATE
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. |
|
| Back to top |
|
|
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
| Thanks. It works fine. |
|
| Back to top |
|
|
|
|
