| Partial duplicate records [message #374722] |
Tue, 26 June 2001 13:09  |
Don
Messages: 24
Registered: June 2001
|
Junior Member |
|
|
Hello. This is my first post to this board. The database is in Oracle 8.0.5. I'm trying to find duplicate credit cards used to pay for more than one customer and having some problems. Here are the fields:
CUST_ID, PAY_SEQ_NO, CRD_CARD, PAY_DATE
I want to find all entries where the same credit card number was used more than once in a month. The PK is on (CUST_ID, PAY_SEQ_NO) and there is an index on the payment date. Here's the problem I'm running into - there is no index on credit card number and the table contains about 38 Million records. I've tried a query where the credit card is in a subquery where the count of credit card entries is > 1, but it never comes back. Any suggestions? Thanks.
|
|
|
|
| Re: Partial duplicate records [message #374731 is a reply to message #374722] |
Wed, 27 June 2001 06:27  |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
First thing to check is that you don't have any trailing or leading spaces on the credit card numbers, and they are all in the same format (voice of bitter experience).
Other things to try:
A query like this should work, and use the PK too.
select cust_id, crd_card
from table t1
where exists (select 'x'
from table t2
where t1.cust_id = t2.cust_id
and t1.crd_card != t2.crd_card)
Or:
If this is a one off check, create a temporary table holding only the cust_id and crd_card fields, create an index on those two fields and do the query off that table instead
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
