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
 

 

How to pick the duplicates without a primary key

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
syed-sameer-ahmed
Warnings : 2

New User


Joined: 22 Feb 2007
Posts: 46
Location: Bangalore

PostPosted: Tue Aug 11, 2009 4:42 pm    Post subject: How to pick the duplicates without a primary key
Reply with quote

Hello All,

I have a table which has NO primary Key and it has many fields which are duplicates.I want to fetch the customer id from this table

for ex

Code:
Customer id      Tin   pin    Add      code        level
----------------------------------------------------------

1234                 1     2         3         4             5
987A                 1     2         3         4             5
A125                 45   46        77      88            99
B123                 45   46        77      88            99
HERD                 45  46        77      88             99 
Code'd


This table has 99167 records and there are many records in similar format but different duplicate data..

Now I have to fetch those customer Id whose TIn,pin,add,code,level are duplicates.

Please suggest.
Back to top
View user's profile Send private message

d_pansare

New User


Joined: 25 Apr 2009
Posts: 20
Location: Pune

PostPosted: Tue Aug 11, 2009 6:16 pm    Post subject:
Reply with quote

You can write self join query given below:

Select A.customer id from table a, table b where
a.customerid <> b.customerid and a.tin = b.tin;

you can use similar queries for rest of the fields. You can even add the rest of conditions in the same query.

I hope this will work for you..

Thanks
Dipak
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Tue Aug 11, 2009 6:30 pm    Post subject:
Reply with quote

Hi Sayed,

You can try the following query.

Select Customer
From t1 a
Where
(select count(*) from t1 b
Where a.Tin = b.Tin and
a.pin = b.pin and
a.add = b.add and
a.code = b.code and
a.level = b.level) > 1;
Back to top
View user's profile Send private message
syed-sameer-ahmed
Warnings : 2

New User


Joined: 22 Feb 2007
Posts: 46
Location: Bangalore

PostPosted: Thu Aug 13, 2009 5:54 pm    Post subject:
Reply with quote

Actually its only one table and I have to fetch records which are duplicate in the same table.And the records are next to each other.

Code:


Customer id      Tin   pin      Add      code        level
----------------------------------------------------------

1234                 1     2         3       4             5
987A                 1     2         3       4             5
A125                45    46        77       88            99
B123                45    46        77       88            99
HERD                45    46        77       88            99
B123                12    23        45       56            66
HERD                12    23        45       56            66
CATS                1      2         3       4              5



Now I need to compare 1 record with record 2 and last record...

I had an idea to first record first and put in Working storage var and fetch the 2nd record and put it in another Working Storage var and then compare..

i do not know how to fetch the second record after fetching first record and it should be followed till SQL=+100
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Thu Aug 13, 2009 5:56 pm    Post subject:
Reply with quote

Syed, The query given is for single table only. That would avoid all the programming for you. Just declare cursor and fetch data.
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: Thu Aug 13, 2009 7:10 pm    Post subject:
Reply with quote

Hello,

Quote:
Actually its only one table
Yes, the table would be joined to itself using only one "real" table, but logically it is 2 tables (table a and table b).
Back to top
View user's profile Send private message
syed-sameer-ahmed
Warnings : 2

New User


Joined: 22 Feb 2007
Posts: 46
Location: Bangalore

PostPosted: Thu Aug 13, 2009 7:40 pm    Post subject:
Reply with quote

I tried executing your query but It was taking lot of time,almost 3 hours.

ANy other suggestions ? Ill try your query again. but more ideas are approciated.

Thanks in advance.
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: Thu Aug 13, 2009 9:44 pm    Post subject:
Reply with quote

Hello,

Quote:
but It was taking lot of time,almost 3 hours.
Yes, rather small amounts of "innocent looking" code can consume vast amounts of system resources.

How many rows are in the table?
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Aug 14, 2009 8:49 pm    Post subject:
Reply with quote

Quote:


I had an idea to first record first and put in Working storage var and fetch the 2nd record and put it in another Working Storage var and then compare..



Good Idea ..use order by all the cols in your cursor and then check each record one by one ...
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 How to pick only YYMMDD from DATE1P (... atulbaviskar SYNCSORT 7 Wed Mar 22, 2017 11:39 am
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Updating the counters after eliminati... PANDU1 DFSORT/ICETOOL 12 Mon Nov 21, 2016 9:47 am
No new posts SFTP to primary and secondary servers mallik4u JCL & VSAM 4 Tue Jun 07, 2016 12:52 pm
No new posts Pick a record from a file based on ti... Mani453 COBOL Programming 4 Thu Jan 07, 2016 6:18 am


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