Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need to find full duplicate rows of records in VFP database

Status
Not open for further replies.

dandman71

MIS
Aug 14, 2002
14
US
We have a app that uses a OLE connection to find data in the VFP database and bring it to SQL for viewing in a program. Problem is, when or database connection sees a full duplicate in Foxpro, it will not display the page.

I ahve searched and tried on this site and have not found the correct statement to use.

There are 4 key fields in the cdx for teh dbf file we are accesing, so I need to have a search statement that will scan the database for records that are duplicated for ALL 4 fields.

Any help guys and girls???
 
Dandman,

Not quite sure what you mean by "bring it to SQL". Also, "4 key fields in the cdx" doesn't really make sense. The CDX contains indexes; the fields are in the DBC.

That said, you probably want some code similar to the following:

SELECT MyField, COUNT(MyField) FROM Table GROUP BY MyField HAVING COUNT(MyField) > 1

If that's not what you want, perhaps you could clarify your requirements.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site: My Crystal Reports web site:
 
Sure, i am sorry.

I am in Foxpro, trying to find all records within a database that exact duplicates for four or more fields.

There are four database fields that are used by another program that cannot be ALL the same, or an error is recieved in the other program while accessing the foxpro database.

So, I need to scan the .dbf file for records that are exactly matching for four specific fields so I can manually mark the one duplicate line for deletion. Does this help? Thanks....
 
One of method for identifying duplicates is to use the UNIQUE property of the INDEX command.

Code:
USE MyTable IN 0 EXCLUSIVE
SELECT MyTable

* --- Delete ALL Records ---
DELETE ALL
INDEX ON (Fld1 + Fld2 + Fld3 + Fld4) UNIQUE TAG Uniq_Rec

* --- Recall All UNIQUE Records ---
RECALL ALL

* --- At this point Duplicated records added later are now marked DELETED ---
SET ORDER TO

Good Luck,
JRB-Bldr
 
Can you show exactly how to do it with the four fileds, I am getting a group error?
 
Dandman,

OK, I think this will do it:

SELECT Field1 + Field2 + Field3 + Field4, COUNT(*) FROM Table GROUP BY 1 HAVING COUNT(*) > 1

This assumes that all four fields are character fields. If any is not, wrap its name with TRANSFORM().

I haven't tested this, but I think it is what you want.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site: My Crystal Reports web site:
 
dandman71,

Not an answer to your question, you've already got that here, but the reason you are getting the group by error is that the requirements for Group By changed in VFP 8 to bring it more in line with the SQL-2 standards. It basically means that in the select portion of your SQL statement you can only have aggregate functions or fields that also exist in your Group By clause.

Code:
Select myfld1, count(*) from mytable group by myfld1 && Is valid in VFP 8

Select myfld1, myfld2, count(*) from mytable group by myfld1 && is not valid because myfld2 is neither an aggregate function nor in the group by clause

...now there is a way to get the old behavior to still apply in VFP 7, but it involves using the SET ENGINEBEHAVIOR command like this:

Code:
SET ENGINEBEHAVIOR 70
Select myfld1, myfld2, count(*) from mytable group by myfld1 && now this sql statement is valid even in VFP 8

boyd.gif

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top