Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Problem in finding duplicates between 2 tables

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

New User


Joined: 07 Aug 2007
Posts: 20
Location: chennai

PostPosted: Mon Aug 13, 2007 3:09 pm    Post subject: Problem in finding duplicates between 2 tables
Reply with quote

Hi all ,
I have problem in finding duplicates between 2 tables. Let the 2 tables be A - ( active ) & B - (Archive). A - has 6 columns and 3 indexes . While B has 7 columns and 4 indexes.

Can any one suggest me some query to find out such duplicates between A and B?

NOTE : A DUPLICATE IN OUR CONTEXT MEANS A SAME ROW EXISTING IN BOTH 'A' & 'B'.
Back to top
View user's profile Send private message

Prajesh_v_p

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Mon Aug 13, 2007 3:27 pm    Post subject:
Reply with quote

Why cant you just try a select with join between these tables A and B on columns you want to find out duplicates?
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Aug 13, 2007 6:28 pm    Post subject:
Reply with quote

Well, none of the rows will be the same. Table B has an extra column. Do the tables have identical primary keys?
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: Mon Aug 13, 2007 9:20 pm    Post subject:
Reply with quote

Hello,

It may help if you provide a better definition of "duplicate".

It may also be easier and maybe even faster to unload the tables and do your comparison in batch against the sequential files.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Aug 13, 2007 9:38 pm    Post subject:
Reply with quote

It also depends on what you plan on doing with those duplicates. Are you wanting to delete duplicates, process something based on that 7th column, or just identify them?

Simply identifying them is fairly simple with some SQL. Be sure to match the WHERE so the columns of the primary key are the first matched, then work through the indexes and end with those columns that aren't part of any index.
Code:

Select A.C1,A.C2,A.C3,A.C4,A.C5,A.C6, B.C1,B.C2,B.C3,B.C4,B.C5,B.C6,B.C7
From A, B
WHERE A.C1 = B.C1, A.C2=B.C2, etc
Back to top
View user's profile Send private message
sgiri
Warnings : 1

New User


Joined: 07 Aug 2007
Posts: 20
Location: chennai

PostPosted: Tue Aug 14, 2007 10:17 am    Post subject: Thank you
Reply with quote

Thank you stoldas what you have said worked out ... Thank you !!
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 Strings with double quotes having pro... raja Arumugam All Other Mainframe Topics 4 Thu Mar 30, 2017 10:34 am
No new posts Data replication from multiple Db2 ta... kishpra DB2 5 Mon Mar 27, 2017 9:58 pm
No new posts Problem reading GTF trace output danik56 ABENDS & Debugging 7 Thu Mar 16, 2017 1:02 pm
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts To know activities on db2 tables ashek15 DB2 4 Fri Mar 03, 2017 11:23 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us