Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Find duplicate rows in DB2 table with SQL statement

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

New User


Joined: 03 Jul 2006
Posts: 53

PostPosted: Sat Oct 06, 2007 1:15 am    Post subject: Find duplicate rows in DB2 table with SQL statement
Reply with quote

Hello everybody. I've tried but looks like it's too advanced for me.

I need to find duplicate rows in DB2 table but simple "GROUP BY / HAVING COUNT > 1" doesn't work.

Let's say I have two columns: Rows will be considered duplicates if ITEM-NO matches and SETUP_DATE is within 5 days (+/- 5 days range) of each other. In example below rows 1,2 and 4,5 are considered duplicates.

ITEM-NO SETUP_DATE

1. 1111111 10-10-2007
2. 1111111 10-12-2007 >>> +2 days
3. 1111111 11-30-2007
4. 2222222 01-14-2007
5. 2222222 01-11-2007 >>> -3 days
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: Sat Oct 06, 2007 5:25 am    Post subject:
Reply with quote

Hello,

What should be done with the "duplicates"? To determine duplication, there must be 2 rows - is data from both to be used?

Is it possible for an item_no to have multiple rows with the same setup_date? Are these considered "duplicates" or does it need to be a different date in the allowable date range to be "duplicate"? How would these be treated?

Using the following data:
Code:
1.  111111 10-10-2007
1a  111111 10-11-2007
2.  111111 10-12-2007
3.  111111 11-30-2007
4.  222222 01-14-2007
5.  222222 01-11-2007


With the addition of the 1a row, what should happen?

Until there is more clear definition of the requirement, any suggestion wouild be a guess - might be a good guess, but still a guess. . .
Back to top
View user's profile Send private message
rmd3003

New User


Joined: 03 Jul 2006
Posts: 53

PostPosted: Mon Oct 08, 2007 7:56 pm    Post subject: Reply to: Need little help with SQL statement
Reply with quote

Thanks for reply. Yes, if you add row 1a, I want to see it too. Basically, in your case I want to see rows 1, 1a, 2 and 4 and 5. Row 3 we will skip because it's more than +/- 5 days to the closest date even though item-no is the same.
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: Tue Oct 09, 2007 7:56 am    Post subject:
Reply with quote

Hello,

Is there a maximum number of rows in the table for any particular item_no?
Back to top
View user's profile Send private message
TG Murphy

Active User


Joined: 23 Mar 2007
Posts: 149
Location: Ottawa Canada

PostPosted: Tue Oct 09, 2007 7:56 pm    Post subject:
Reply with quote

Looks to me like you need to join the table to itself where a.ITEM_NO = b.ITEM_NO and A.SETUP_DATE - B.SETUP_DATE BETWEEN -5 AND 5

This if off the top of my head and may or may not work. Or may require other conditions. But it is worth pursuing..
Back to top
View user's profile Send private message
martin9

Active User


Joined: 01 Mar 2006
Posts: 290
Location: Basel, Switzerland

PostPosted: Tue Oct 09, 2007 8:12 pm    Post subject:
Reply with quote

hy,

try i subselect in the where/having clause...

i.e.

select columns-you-need from table
where 1 < (select count(*) from ....
where item-no is the same
and setup-date between -5 to +5)

just code the correct join correlations...
you can try something similar like that...
maybe you need to group it first also...

regards,

martin9
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 Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 2 Wed Dec 06, 2017 1:50 am
No new posts Can we combine the rows with same key... V S Amarendra Reddy DB2 12 Fri Dec 01, 2017 10:29 pm
No new posts COBOL - EXIT statement weird behavior anthony.pangestu COBOL Programming 0 Fri Oct 27, 2017 9:57 am
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us