September 19, 2009 at 7:24 am
Hi friends,
I run this query in Oracle to find the duplicates of all occurences in a table for the pk values.
SELECT * FROM qtral WHERE (tral_no, tral_pd)
in (SELECT tral_no, tral_pd FROM qtral
group by tral_no, tral_pd
having count (*) >1
)order by tral_no, tral_pd;
This query returns all the occurences of duplicate values in a table.. But in SQL server, I get an error :
Incorrect syntax near ','.
Need help..
Thanks
September 19, 2009 at 9:37 am
That's because the IN clause cannot compare more than one dimension in the array...
I adopted the script to table variable sso I can test it. Here's what you need:
declare @qtral TABLE (tral_no CHAR(1), tral_pd int)
INSERT @qtral (tral_no , tral_pd )
SELECT 'A',1
UNION ALL
SELECT 'A',2
UNION ALL
SELECT 'A',1
UNION ALL
SELECT 'B',2
UNION ALL
SELECT 'B',2
UNION ALL
SELECT 'B',3
SELECT
A.*,
COUNT(*) AS NUM_OF_OCCURANCES
FROM
@qtral A
INNER JOIN
(
SELECT tral_no, tral_pd FROM @qtral
group by tral_no, tral_pd
having count (*) >1
) B ON
A.tral_no = B.tral_no
AND
A.tral_pd = B.tral_pd
group by
A.tral_no, A.tral_pd
September 19, 2009 at 11:17 am
or use the ever lasting exists with a correlated subquery.
SELECT Q.*
FROM qtral Q
WHERE exists (SELECT 1
FROM qtral Qx
where Qx.tral_no = Q.tral_no
and Qx.tral_pd = Q.tral_pd
group by Qx.tral_no, Qx.tral_pd
having count (*) >1
)
order by Q.tral_no, Q.tral_pd;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 20, 2009 at 11:08 am
I have to say... impressive solutions.
Call me lazy but, how about...
SELECT *
FROM qtral
WHERE tral_no + tral_pd
IN(
SELECT tral_no + tral_pd
FROM qtral
GROUP BY tral_no, tral_pd
HAVING count (*) >1)
ORDER BY tral_no, tral_pd
😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 20, 2009 at 1:28 pm
well, first of all , you will fail if they are integers. If one will be 15 and the other 13 you'll get 28, which might also be the case for many other combinations (12+16, 10+18, ...)
If they are textual you might end up in the same situation. ABC+DEF are the same as AB+CDEF.
You'll have to gthink about a good delimiter, but why do so in the first place?!
September 20, 2009 at 4:26 pm
newbieuser (9/19/2009)
Hi friends,I run this query in Oracle to find the duplicates of all occurences in a table for the pk values.
SELECT * FROM qtral WHERE (tral_no, tral_pd)
in (SELECT tral_no, tral_pd FROM qtral
group by tral_no, tral_pd
having count (*) >1
)order by tral_no, tral_pd;
This query returns all the occurences of duplicate values in a table.. But in SQL server, I get an error :
Incorrect syntax near ','.
Need help..
Thanks
As you can see, there are many ways to find dupes. The real question would be... what do you want to do with them once you find them?
--Jeff Moden
September 20, 2009 at 5:43 pm
dbo.benyos (9/20/2009)
well, first of all , you will fail if they are integers.
Do you remember cast()... make them strings 😉
dbo.benyos (9/20/2009)
If they are textual you might end up in the same situation. ABC+DEF are the same as AB+CDEF.
how about first_column + 'somedelimiter' + second_column? 😀
The idea is to point poster in the right direction, not to do his/her job :satisfied:
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 22, 2009 at 12:35 pm
Hi friends,
When I run this query I get 267 records:
SELECT Q.*
FROM qtral Q
WHERE exists (SELECT 1
FROM qtral Qx
where Qx.tral_no = Q.tral_no
and Qx.tral_pd = Q.tral_pd
group by Qx.tral_no, Qx.tral_pd
having count (*) >1
)
order by Q.tral_no, Q.tral_pd;
But, when I try to select columns from different table in the same query I get only 266 records.. Not sure what I'm doing wrong..
SELECT Q.*, p.pmtco, p.pmtno, p.pmtbk, p.pmtrcn, p.pmtamt
FROM qtral Q , pymt p
WHERE exists (SELECT 1
FROM qtral Qx
where Qx.tral_no = Q.tral_no
and Qx.tral_pd = Q.tral_pd
group by Qx.tral_no, Qx.tral_pd
having count (*) >1
)
and Q.chpmtno = p.pmtno
and Q.tral_pd = p.pmtbk
and Q.chpmtco = p.pmtco
order by Q.tral_no, Q.tral_pd;
Please help me.. Thanks a lot
October 22, 2009 at 1:21 pm
Chances are there are 266 matching rows on pymt table, isn't it?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 22, 2009 at 1:33 pm
Yes, right Paul. That 1 record is in qtral but not in pymt table.
But, I need to join so even if the record is in qtral but not in pymt table is also returned? Is it possible to do this?
SELECT Q.*, p.pmtco, p.pmtno, p.pmtbk, p.pmtrcn, p.pmtamt
FROM qtral Q , pymt p
WHERE exists (SELECT 1
FROM qtral Qx
where Qx.tral_no = Q.tral_no
and Qx.tral_pd = Q.tral_pd
group by Qx.tral_no, Qx.tral_pd
having count (*) >1
)
and Q.chpmtno = p.pmtno
and Q.tral_pd = p.pmtbk
and Q.chpmtco = p.pmtco
order by Q.tral_no, Q.tral_pd;
Thank you so much
October 22, 2009 at 1:45 pm
I tried this, but not sure if it is right.. it returns 267 rows though..
SELECT Q.*, p.pmtco, p.pmtno, p.pmtbk, p.pmtrcn, p.pmtamt
FROM qtral Q left outer join pymt p on Q.chpmtno = p.pmtno
and Q.tral_pd = p.pmtbk
and Q.chpmtco = p.pmtco
WHERE exists (SELECT 1
FROM qtral Qx
where Qx.tral_no = Q.tral_no
and Qx.tral_pd = Q.tral_pd
group by Qx.tral_no, Qx.tral_pd
having count (*) >1
)
order by Q.tral_no, Q.tral_pd;
Thanks
October 25, 2009 at 5:17 am
newbieuser (10/22/2009)
I tried this, but not sure if it is right.. it returns 267 rows though..
SELECT Q.*, p.pmtco, p.pmtno, p.pmtbk, p.pmtrcn, p.pmtamt
FROM qtral Q left outer join pymt p on Q.chpmtno = p.pmtno
and Q.tral_pd = p.pmtbk
and Q.chpmtco = p.pmtco
WHERE exists (SELECT 1
FROM qtral Qx
where Qx.tral_no = Q.tral_no
and Qx.tral_pd = Q.tral_pd
group by Qx.tral_no, Qx.tral_pd
having count (*) >1
)
order by Q.tral_no, Q.tral_pd;
Thanks
This is perfect! Oracle is also using the same outer join syntax from Oracle9i and above.
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply