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
 

 

How to see Duplicate records in DB2 table?

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

New User


Joined: 24 Apr 2007
Posts: 13
Location: Noida

PostPosted: Wed Sep 26, 2007 11:38 am    Post subject: How to see Duplicate records in DB2 table?
Reply with quote

Can some one please help me to find out duplicate records in a DB2 table which do not have a unique key?
Back to top
View user's profile Send private message

acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Wed Sep 26, 2007 11:45 am    Post subject:
Reply with quote

search for select with group by and having count/*)>1
Back to top
View user's profile Send private message
manjul

New User


Joined: 24 Apr 2007
Posts: 13
Location: Noida

PostPosted: Wed Sep 26, 2007 12:06 pm    Post subject:
Reply with quote

thanks i belive this will work
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


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

PostPosted: Wed Sep 26, 2007 2:27 pm    Post subject:
Reply with quote

Hi,

Don't just believe icon_wink.gif ..try it as well.


P.S: It'll work.
Back to top
View user's profile Send private message
psreddy
Currently Banned

New User


Joined: 18 May 2006
Posts: 4

PostPosted: Wed Sep 26, 2007 3:09 pm    Post subject: Hi
Reply with quote

Can any one write the complete query for this
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


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

PostPosted: Wed Sep 26, 2007 4:06 pm    Post subject:
Reply with quote

Hi,

Try this:

Code:
SELECT * FROM Owner.Table name
GROUP BY COUNT/*)>1;         
WITH UR;                     


I tried this in QMF, it worked out.
Back to top
View user's profile Send private message
mosinjamadar

New User


Joined: 26 Sep 2007
Posts: 42
Location: pune

PostPosted: Wed Oct 03, 2007 10:56 am    Post subject:
Reply with quote

hi
same query i tried in SPUFI but its giving error SQLCODE = -104,

please help me
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: Wed Oct 03, 2007 11:11 am    Post subject:
Reply with quote

Hello,

Did you look up the -104 return code? The db2 messages manual is available via the "Manuals" link at the top of the web page.

You might try
Code:
select colA, count(*) from your_tbl
group by colA having count(*) > 1
You don't have to use colA, it is just for example.
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Wed Oct 03, 2007 11:11 am    Post subject:
Reply with quote

COUNT/*)>1;? it should be
COUNT(*)>1;
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


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

PostPosted: Wed Oct 03, 2007 1:16 pm    Post subject:
Reply with quote

Hi Srihari,

I tried both this
Code:
COUNT/*)>1

and this
Code:
COUNT(*)>1


Both in QMF & in a batch job, compared (SuperC) the files got from batch JOBs, both the queris are giving the same results. Did you try this?
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Wed Oct 03, 2007 2:28 pm    Post subject:
Reply with quote

Have you tried it in SPUFI. I tried both of them in SPUFI

COUNT/*)>1 gives -104 and
COUNT(*)>1 works fine.
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Wed Oct 03, 2007 3:00 pm    Post subject:
Reply with quote

of course COUNT/*)>1 i'ts a typo...it should be COUNT(*)>1.
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 Using ICETOOL, how we can seperate th... bshkris SYNCSORT 5 Tue May 09, 2017 8:33 pm
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am


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