View previous topic :: View next topic
|
Author |
Message |
manjul
New User
Joined: 24 Apr 2007 Posts: 13 Location: Noida
|
|
|
|
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 |
|
|
acevedo
Active User
Joined: 11 May 2005 Posts: 344 Location: Spain
|
|
|
|
search for select with group by and having count/*)>1 |
|
Back to top |
|
|
manjul
New User
Joined: 24 Apr 2007 Posts: 13 Location: Noida
|
|
|
|
thanks i belive this will work |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6248 Location: Mumbai, India
|
|
|
|
Hi,
Don't just believe ..try it as well.
P.S: It'll work. |
|
Back to top |
|
|
psreddy Currently Banned New User
Joined: 18 May 2006 Posts: 4
|
|
|
|
Can any one write the complete query for this |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6248 Location: Mumbai, India
|
|
|
|
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 |
|
|
mosinjamadar
New User
Joined: 26 Sep 2007 Posts: 42 Location: pune
|
|
|
|
hi
same query i tried in SPUFI but its giving error SQLCODE = -104,
please help me |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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 |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007 Posts: 295 Location: Singapore
|
|
|
|
COUNT/*)>1;? it should be
COUNT(*)>1; |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6248 Location: Mumbai, India
|
|
|
|
Hi Srihari,
I tried both this
and this
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 |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007 Posts: 295 Location: Singapore
|
|
|
|
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 |
|
|
acevedo
Active User
Joined: 11 May 2005 Posts: 344 Location: Spain
|
|
|
|
of course COUNT/*)>1 i'ts a typo...it should be COUNT(*)>1. |
|
Back to top |
|
|
|