|
View previous topic :: View next topic
|
| Author |
Message |
syed-sameer-ahmed
Warnings : 2
New User
.jpg)
Joined: 22 Feb 2007
Posts: 45
Location: Bangalore
|
|
|
|
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 |
|
 |
d_pansare
New User
Joined: 25 Apr 2009
Posts: 20
Location: Pune
|
|
|
|
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 |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 295
Location: Singapore
|
|
|
|
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 |
|
|
syed-sameer-ahmed
Warnings : 2
New User
Joined: 22 Feb 2007
Posts: 45
Location: Bangalore
|
|
|
|
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 |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 295
Location: Singapore
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
syed-sameer-ahmed
Warnings : 2
New User
Joined: 22 Feb 2007
Posts: 45
Location: Bangalore
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| 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 |
|
|
|
|
