Need to browse only exact duplicates records from a table based on a field 4

[titoneon]

Hi,
I have this table named Tstandard and contain several fields(ie.. Item, onhand etc)
I have a few records duplicated under the field Item wich is a character field, the problem i have is that i have not found a Select sql command tta filter the duplicated record cause under field "Item" i could have :
Fields
Item onhand
99935 4
99936 3
99937-245 6
11011-233 15
11011-233 1
11011-245 20
11011-245 1
ok so when i run an sql i am getting as duplicated 999935, 99936 and 99937-245, these are not duplicated but cause starting the 3 digits are the same, then are shown as duplicated, actually what i need is to browse the table and show these type of dupliactes
11011-233 15
11011-233 1
11011-245 20
11011-245 1
so then the one i need to delete are the one that are having under the field onhand<=2 so i wrote this code and i am able to mark for deletion some of the real duplicated but also are others duplicated records that are not marked for deletion where the onhand should be <=2, here is the code i have, of course i need to do this under an old foxpro dos version 2.0, but i wrote the code under VFP 5.0

USE tstandard excl
INDEX on item +STR(onhand)TAG it
SET ORDER TO it
GO top
DO WHILE !EOF() && Begins loop
STORE item TO n
SKIP
STORE item TO n_1
IF EOF( )
EXIT
ENDIF
IF n#n_1
SKIP
LOOP
ENDIF
SKIP -1
DELETE
SKIP 2
ENDDO && Ends loop
BROWSE
USE
retu

*** what could be wrong ? or it is there any sql code, that can point me to something like this but always showing the table in order to delete the duplicates i want ?
Thanks
Ernesto

 

[JRB-Bldr]

One way to find the Duplicate records might be:

SELECT Item, Count(OnHand) AS Records;
FROM tstandard;
GROUP BY Item;
ORDER BY 2 Desc;
INTO CURSOR Chk4Dupes READWRITE

SELECT Chk4Dupes
DELETE FOR Records = 1
INDEX Item TAG Item FOR !DELETED()

SELECT tstandard
SET RELATION TO Item INTO Chk4Dupes
SET FILTER TO !EMPTY(Chk4Dupes.Item)
BROWSE

Now if you want to check for dupes on only a part of the Item number (example: only first 5 characters), then modifications would need to be made to the code.

Good Luck,
JRB-Bldr

 

[Dan Freeman]

And, of course, you can skip that second step of deleting in the result with a HAVING clause on the first pass:

SELECT Item, Count(OnHand) AS Records;
FROM tstandard;
GROUP BY Item;
ORDER BY 2 Desc;
HAVING Records > 1 ;
INTO CURSOR Chk4Dupes READWRITE

I'm a little disconcerted by talk of "first three digits the same" causing problems because none of the given examples would cause that.

 

[titoneon]

HEY Guys,
I will get back to you asap about the results, Danfreeman, i will cehck what is going on, probably i did not explain myself correctly, i get back to you guys Thansks a lot
Ernesto

 

[titoneon]

Hi,
these records i want delete in table "tstandard" were created a long time ago in a foxpro dos version and needed to be in that version, besides for testing to see if my code or your code will work i am using the table under vfp 5.0 but the READWRITE at the end it is not supported, i and getting error "command contain unrecognized phrase/keyword" just cause this is not supported on VFP 5.0 then of course won't be on FOXPRO DOS if i really want to use this code.
Is there any other approach ?
In fact if i was able to use these codes they both work but not the READWRITE, if it was then i will be able to update the "tstandard" table and get rid of dups.

SELECT Item, Count(OnHand) AS Records;
FROM tstandard;
GROUP BY Item;
ORDER BY 2 Desc;
INTO CURSOR Chk4Dupes READWRITE


or

SELECT Item, Count(OnHand) AS Records;
FROM tstandard;
GROUP BY Item;
ORDER BY 2 Desc;
HAVING Records > 1 ;
INTO CURSOR Chk4Dupes READWRITE

Thanks

 

[Mike Lewis]

One solution to the READWRITE problem would be to create a second cursor, with the same structure as TsStandard. Use CREATE CURSOR to create that cursor. Then use APPEND FROM to copy all the records from TsStandard to the new cursor.

The point is that cursors created by SELECT .... INTO CURSOR are always READWRITE (in VFP 5.0), but those created by CREATE CURSOR are updateable.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Before VFP7 you could do USE DBF() AGAIN IN 0 to make a cursor readwrite.

But that aside you have an index type, which could remove duplicates:
INDEX ON item TAG item UNIQUE

In itself it does not remove duplicates, but directly after it is created and whenever you SET ORDER TO TAG item, you will only see each first record with a certain item value.
Now you can SET ORDER TO (setting order to no tag), then DELETE ALL, after that SET ORDER TO TAG item and RECALL ALL to only recall the records the index points to. Finally you PACK and have removed all duplicates. Before you do that veryfy the correct records are marked for deletion.

In one go this would be:

USE yourtable in 0 EXCLUSIVE
INDEX ON item TAG item UNIQUE
SET ORDER TO
DELETE ALL
SET ORDER TO TAG item
RECALL ALL
SET DELETED OFF
BROWSE
PACK
USE

Bye, Olaf.

 

[titoneon]

Guys,
Thanks a lot, you guys are the best, i am learning but hitting the wall but also by following your suggestions.
C'ya
Ernesto

 

[JRB-Bldr]

I often do as Olaf has suggested above using the UNIQUE option of the INDEX command. It works well.

The only problem with that approach is that it does not allow you to view the records and choose/select the specific records that you will Delete.

Good Luck,
JRB-Bldr

 

[Dan Freeman]

Just be aware that UNIQUE indexes are not updated. Create it, use it, and delete it.

Note that this query:

SELECT Item, Count(OnHand) AS Records;
FROM tstandard;
GROUP BY Item;
ORDER BY 2 Desc;
HAVING Records > 1 ;
INTO CURSOR Chk4Dupes READWRITE

should work just fine in VFP5 if you remove the READWRITE keyword. Then you'll have your cursor. As the others have said, you can either append to a readwrite cursor or USE DBF("the cursor") AGAIN IN 0 to get it readwrite, or you can COPY TO a temp file. (There are always three ways in Xbase.)

Mike:

The point is that cursors created by SELECT .... INTO CURSOR are always READWRITE (in VFP 5.0)

Typo? SQL result sets are read-only in VFP 5.

 

[Mike Lewis]

Typo? SQL result sets are read-only in VFP 5.

Correct. Not the first time you've spotted my blunders, Dan. Thanks.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

It's true unique indexes are not updated, for example if you have one record in the index and two further not, as they are duplicates, deleting the original record does not put one of the further records into the index. But it's untrue you can't see what you delete, I just showed you can. After you delete all and then recall the unique records, before you pack, you can browse and see what you will pack. I just should have put another line before BROWSE: do SET ORDER TO. And the browse will then show all records, the unique ones, undeleted, and the duplicates, with delete mark. And you can manually override that, before you pack.

Bye, Olaf.