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

How to see Duplicate records in DB2 table?


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

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

Superior Member


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

PostPosted: Wed Sep 26, 2007 2:27 pm
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
Reply with quote

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

Superior Member


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

PostPosted: Wed Sep 26, 2007 4:06 pm
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
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

Moderator Emeritus


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

PostPosted: Wed Oct 03, 2007 11:11 am
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
Reply with quote

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

Superior Member


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

PostPosted: Wed Oct 03, 2007 1:16 pm
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts combine multiple unique records into ... DFSORT/ICETOOL 2
No new posts To Omit records based n SORT condition DFSORT/ICETOOL 6
No new posts Unload and Load ISPF Table TSO/ISPF 4
No new posts ISPF Table to add a new column TSO/ISPF 1
No new posts Inserting into table while open selec... DB2 1
Search our Forums:

Back to Top