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 query to fetch duplicate rows

 
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
annu zacson

New User


Joined: 19 Aug 2008
Posts: 15
Location: Cochin

PostPosted: Mon Sep 01, 2008 12:31 pm    Post subject: Db2 query to fetch duplicate rows
Reply with quote

How to write a Db2 query for retrieving rows with same and duplicate values.
For eg:
Please find the doc attached:
These are the data which are present in the database.
I need to write these into 2 output files OUT1 and OUT2 based on the following conditions.
1) write only one record into output file OUT1(a customer which is having same national id)
2)write both the rows into output file OUT2(a customer which are having 2 different national id).

The sample query that i have written and the output is given in the excel sheet. But i need to write the output into 2 diff output files.
Please help me in doing this.
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Sep 01, 2008 5:08 pm    Post subject:
Reply with quote

Here you go

For OUT1
Code:



SELECT CUSTID,NATID FROM TABLE GROUP BY CUSTID,NATID HAVING COUNT(*) > 1 ;


For OUT2

Code:



SELECT CUSTID,NATID FROM TABLE GROUP BY CUSTID,NATID HAVING COUNT(*) = 1 ;
Back to top
View user's profile Send private message
Lijo

New User


Joined: 24 Aug 2008
Posts: 41
Location: Cochin

PostPosted: Tue Sep 02, 2008 3:38 am    Post subject:
Reply with quote

Hi,

Above mentioned query will give the desired output for OUT1.

For OUT2:

SELECT CUSTOMER_ID,NATIONAL_ID FROM TABLE A, TABLE B
WHERE A.CUSTOMER_ID=B.CUSTOMER_ID
AND A.NATIONAL_ID<>B.NATIONAL_ID;
Back to top
View user's profile Send private message
sudhakarreddy.ibm
Warnings : 1

New User


Joined: 20 Apr 2009
Posts: 15
Location: chennai

PostPosted: Mon Jun 08, 2009 6:21 pm    Post subject:
Reply with quote

Hi All,

IN DB2,

by using cursor you can do it. declare the cursor for OUT1,OUT2
DECLARE CURSOR duplicate-records FOR SELECT CUSTID,NATID, count(*) FROM TABLE GROUP BY CUSTID,NATID HAVING COUNT(*) > 1 ;

DECLARE CURSOR uniq-records FOR SELECT CUSTID,NATID, count(*) FROM TABLE GROUP BY CUSTID,NATID HAVING COUNT(*) = 1 ;

IN JCL

by using sort utility
////job JOB (DBBX16#),'RECEIV. GENERA.', ,,
//STEP205 EXEC PGM=SORT
//SORTIN DD DSN=A.B.C,
// DISP=SHR
//SORTOUT1 DD DSN=A.B.C.DUPLI,
//XSUM DD DSM=A.B.C.UNIQ
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS = (, , , ,)
SORT FIELDS = NONE,XSUM
/*
//
kindly let me know if any wrong

Thanks in advance
sudhakar
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Mon Jun 08, 2009 7:24 pm    Post subject:
Reply with quote

Hi sudhakar,

How does this SORT solution work for the original question? icon_confused.gif
Back to top
View user's profile Send private message
Anush Dhanush

New User


Joined: 07 Nov 2011
Posts: 1
Location: India

PostPosted: Fri Apr 17, 2015 12:53 am    Post subject: Reply to: Db2 query to fetch duplicate rows
Reply with quote

OUT1:

Code:
select custid,natid, [b]count(*)[/b] from table group by custid, natid having count(*) > 1 ;


OUT2:

Code:
select natid, custid, count(*) from table group by natid, custid having count(*) > 1 ;

@cvishu: Yes, we can Unload a Table fro m JCL itself, thru IKJEFT01 Utility.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1736
Location: NY,USA

PostPosted: Fri Apr 17, 2015 3:03 am    Post subject:
Reply with quote

Please make a use of UNION between two queries suppiled by ashimer and place a cursor.

Code:
SELECT CUSTID,NATID,Y FROM TABLE GROUP BY CUSTID,NATID ,3 HAVING COUNT(*) > 1
UNION
SELECT CUSTID,NATID,N FROM TABLE GROUP BY CUSTID,NATID,3 HAVING COUNT(*) = 1

Fetch cursor
  when sqlcode = 0
     if ws-hostt-3 = 'Y'
        write out1
     else
        write out2
    end-if
   when other
    error
end-evaluate
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Apr 17, 2015 11:16 am    Post subject:
Reply with quote

Hi,

Do we really need two cursors or Union here? Not sure I may be missing something here

I guess it can be achievable with a single cursor and without Union as below

Code:

SELECT CUSTID,NATID,COUNT(*) FROM TABLE GROUP BY CUSTID,NATID

Fetch cursor
  when sqlcode = 0
     if Host variable Count(*) > 1
        write out1
     else
        write out2
    end-if
   when other
    error
end-evaluate


Regards,
Chandan
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 378
Location: USA

PostPosted: Fri Apr 17, 2015 6:17 pm    Post subject: Reply to: Db2 query to fetch duplicate rows
Reply with quote

I hope we all realize that it is a 7 year old topic. The solution was already given then and now we are stretching it unnecessarily.

Let's get this closed.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Apr 17, 2015 11:05 pm    Post subject:
Reply with quote

Ha ha ha..my bad..
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies.    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 compare 2 rows within the same file ram_vizag SYNCSORT 7 Wed Jun 14, 2017 12:34 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts Using ICETOOL, how we can seperate th... bshkris SYNCSORT 5 Tue May 09, 2017 8:33 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm


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