|
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 |
|
|
|
|
