IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SQL query to fetch duplicate rows


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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
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

Superior Member


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

PostPosted: Mon Jun 08, 2009 7:24 pm
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
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

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Fri Apr 17, 2015 3:03 am
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: 275
Location: Mumbai

PostPosted: Fri Apr 17, 2015 11:16 am
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: 446
Location: USA

PostPosted: Fri Apr 17, 2015 6:17 pm
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: 275
Location: Mumbai

PostPosted: Fri Apr 17, 2015 11:05 pm
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   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Duplicate transid's declared using CEDA CICS 3
No new posts RC query -Time column CA Products 3
No new posts Exclude rows with > than x occurre... DFSORT/ICETOOL 6
No new posts Dynamically pass table name to a sele... DB2 2
Search our Forums:

Back to Top