ibbm


Is there anyway to do a query that displays only duplicate records Preferrably in 6 as I'm not ready for v9.

Thanks as always!




Re: finding duplicates with a query with fox 6 or 9...

Tamar E. Granor


Finding duplicate records with a query isn't hard. The hard part is deciding what constitutes a duplicate, that is, what fields you want to compare.

To demonstrate, say you want to consider all records where the first and last name fields are the same as duplicates. You do it like this:

SELECT cFirst, cLast, CNT(*) ;
FROM YourTable ;
GROUP BY 1, 2 ;
HAVING CNT(*) > 1 ;
INTO CURSOR DupValues

SELECT YourTable.* ;
FROM YourTable ;
JOIN DupValues ;
ON YourTable.cFirst == DupValues.cFirst ;
AND YourTable.cLast == DupValues.cLast ;
INTO CURSOR DupRecords

In VFP 9, you can actually do this in a single query using a derived table. If you only need to match a single field, you can probably do it with a subquery in earlier versions.

Tamar





Re: finding duplicates with a query with fox 6 or 9...

Naomi Nosonovsky

You may also check my FAQ on the topic (this is a direct link):

http://www.universalthread.com/wconnect/wc.dll 2,84,14,8129






Re: finding duplicates with a query with fox 6 or 9...

ibbm

Thanks for these answers but I should have put more thought into what I was asking. Here's the scenario that I am facing. I am using a third party transaction sales file that I cannot touch. Everymonth I need to charge a fee against each transaction but I cannot flag this particular file. I need to create (I think) a shadow file that I can flag as charged as the next month there may have been some changes to the file for the prior month that may have been added without anyones knowledge. I need to ensure that the shadow flag is insync with the original file on a period basis. The problem with the original file is that it has no obvious unique key and possible could be identical so I'm not sure how to approach it. I thought I could scatter the record of the original to an array and look for it in the shadow file. If it's not in the shadow file then I could add it. The problem with this is the shadow file would not be identical to the original file as I would add extra fields to it. (ie a unique key and a transaction charged field).

The transaction file is huge and has at least 50+ fields in it.

Any ideas on how to resolve this





Re: finding duplicates with a query with fox 6 or 9...

Naomi Nosonovsky

select 'S' as Identifier, 50 fields from Shadow ;

union ;

select 'O' as Identifier, 50 fields from Original ;

into cursor curAllWithNew

This file would containt all records from Shadow and only new records from Original (not in Shadow).





Re: finding duplicates with a query with fox 6 or 9...

ibbm

so Shadow has actually 52 fields and I need the final cursor to have all these fields in it. The cursor you just created would replace the shadow file. I want the new records from the Original to be added to this file but I need the two other fields to be blank so that when I run my "charge fee process" I can select all the records that haven't been charged yet.

Can this be done Am I making sense

ie

original file

client, date, sale, .... 47 more fields

abc100, 20070701, 10.00, .......

def200, 20070701,10.00, ......

shadow file after the first charge fee process

client, date, sale, .... 47 more fields, lchargeflag

abc100, 20070701, 10.00, ......., T

def200, 20070701,10.00, ......, T

if I run the query above I think I will get the following data in the cursor

client, date, sale, .... 47 more fields

abc100, 20070701, 10.00, .......

def200, 20070701,10.00, ......

ghi300, 20070702,155.00, ......

I will lose the last field. I am looking for this result

ghi300, 20070702,155.00, ......

that way I can add it to the shadow field and have the lcChargeflag as well but it will be blank.

or perhaps I am not understanding something. ( I have just moved to VP9)

Thanks.





Re: finding duplicates with a query with fox 6 or 9...

Naomi Nosonovsky

Once you have the cursor with new and old records, you may try

insert into Shadow select *, space(10) as ExtraBlankField1, space(20) as ExtraBlankField2 from curAllRecsWithNew where Source = 'O'

This would insert source field also, if you don't want it, you would need to list all fields twice in Insert command.





Re: finding duplicates with a query with fox 6 or 9...

ibbm

as a test to try this i copy a portion of the original file and called it shawdow. The structures should be identical It has 61563 records in it. then i tried this code

select "S" as identifier, shadow.* FROM shadow;

union;

SELECT "O" as identifier,transact.* FROM transact;

into CURSOR curAllRecord

the result was I got what looks like 98% of the transact file with identifier "O" and all of the shadow file with identifier "S" Out of the 61563 only 2121 were not copied I expected the size of the new cursor to be the same size as the transact file (572002) but it wasn't it was 631444. is my code wrong as I said the shadow is a copy of the transact file.





Re: finding duplicates with a query with fox 6 or 9...

Naomi Nosonovsky

I would expect it to select all records from Shadow and only unique records (not already in Shadow) from Original file.

If you remove Identifier field, what would you get





Re: finding duplicates with a query with fox 6 or 9...

ibbm

I have to take the identifier out... but also i had the "set enginebehavior 70" on ... that really didn't help now did it !!

Now I get 2114 less records than the original but the original I am thinking has duplicates in it so this won't work because I need to process all the transactions... any other ideas





Re: finding duplicates with a query with fox 6 or 9...

ibbm

as I have just moved to version 9 can you tell me how I can do this with a single query What is a derived table

thanks





Re: finding duplicates with a query with fox 6 or 9...

Naomi Nosonovsky

I understand derived table as an inner select used in the whole query, e.g. Tamar's first sample can be changed to


SELECT YourTable.* ;
FROM YourTable ;
JOIN (SELECT cFirst, cLast, CNT(*) ;
FROM YourTable ;
GROUP BY 1, 2 ;
HAVING CNT(*) > 1) DupValues ;
ON YourTable.cFirst == DupValues.cFirst ;
AND YourTable.cLast == DupValues.cLast ;
INTO CURSOR DupRecords

As you see, we create 'DupValues' table inside the main select.





Re: finding duplicates with a query with fox 6 or 9...

ibbm

thanks both of you. These answers have me on my way for this problem!