Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 - Selecting Unique record

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 151
Location: India

PostPosted: Tue Jan 19, 2010 7:06 am    Post subject: DB2 - Selecting Unique record
Reply with quote

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
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jan 19, 2010 9:59 am    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Jan 22, 2010 9:35 pm    Post subject:
Reply with quote

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
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 151
Location: India

PostPosted: Tue Feb 02, 2010 4:26 am    Post subject:
Reply with quote

Thanks. It works fine.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to generate a new unique Input fi... for1ranjith CLIST & REXX 11 Sat Jul 01, 2017 12:09 pm
No new posts Need to write record of PS File in ex... Chandan1993 JCL & VSAM 1 Wed Jun 07, 2017 1:35 am
No new posts Adding big TEXT lines to each record ... bshkris SYNCSORT 4 Sat May 06, 2017 1:40 am
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts Sort Large record length cmsmoon DFSORT/ICETOOL 14 Tue Apr 11, 2017 5:49 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us