script help

[Anis]

I have a database with 2 fields:

TYPE and ID

I would like to mark for deletion all records with type 2, but also records with type 1 that have the same ID that type 2 has.
So I need to read type 2 ID field and delete Type 1 and Type 2 with the same ID.
the number of records vary all the time, but if there's a Type 2 then there's a type 1 with the same ID.

[G Gabriel]

Re: script help

How many ID's are we talking about?

[G Gabriel]

Re: script help

Here is a script that will work regardless of how many ID's:

Create an index with an expression: Type-ID (that is type minus ID), filtered to only have Type 2 and let's call this index "type_ID"

t=table.open("your_table")
t.fetch_first()
while .not. t.fetch_eof()
if t.type="type 2".or.exists(t.type-t.id,"your_table","type_ID")
t.change_begin()
t.mark()
t.change_end(.t.)
t.fetch_next()
end while

[Anis]

Re: script help

There should be no more than type 1 and type 2 for the same ID, so one ID could exist maximum of 2 times.

[G Gabriel]

Re: script help

There should be no more than type 1 and type 2 for the same ID, so one ID could exist maximum of 2 times.

I understand that..my question was: how many different ID's are there?
That's irrlevant anyway.. The above script should do the job.

[Anis]

Re: script help

usually close to 40,000 in total

[G Gabriel]

Re: script help

The above script should do the job.

[CALocklin]

Re: script help

Gabe's got the general concept right. The details are lacking which means the result won't work. (Gabe, I know time is precious but you need to take more time thinking these through and testing them.)

General comment / disclaimer: t.type-t.id will only work IF both are character fields but you didn't tell us what types of fields these are. If they are both numbers and Gabe's index is used, then there is too much chance of matching with a record that really shouldn't match. And if they are mixed - Type is Numeric and ID is character - then setting an index like this won't work at all. Also, FWIW, if both are character fields, then t.type-t.id gives exactly the same result in this case as t.type+t.id; it just "appends" one to the other.

Obvious typo - missing END IF.
Obvious typo - there is no Exists() command, it's Exist()

This will always be true and, therefore, every record will be marked:
exist(t.type-t.id,"your_table","type_ID")
This just checks to see if there is a record - any record - that has an index that matches the current record's values. Since the current record's index had better match it's own values, this will be true for every record.

You have to make sure the t.fetch_next() is outside the IF statement so the loop doesn't run endlessly once the IF is corrected such that some records will not match the IF criteria. (If the "IF" statement is false and the t.fetch_next() is inside the IF statement, the t.fetch_next() is never reached and it loops continuously on the current record.)

ASSUMING these are both character values and the Type field is only one character wide:
Use the index as Gabe defined it.

t=table.open("your_table")
t.fetch_first()
WHILE .not. t.fetch_eof()
   IF t.type="2" .or. exist( [B]"2"[/B]+t.ID, "your_table", "type_ID")
      t.change_begin()
      t.mark()
      t.change_end(.t.)
   END IF
   t.fetch_next()
END WHILE

If the Type is a numeric value and the ID is character, then the index should be:
str(type) + ID

And the code should be:

t=table.open("your_table")
t.fetch_first()
WHILE .not. t.fetch_eof()
   IF t.type = 2 .or. exist( str(2) + t.id, "your_table", "type_ID" )
      t.change_begin()
      t.mark()
      t.change_end(.t.)
   END IF
   t.fetch_next()
END WHILE

If both are numeric values, I'd build the index using str() of both fields and change the IF to:
IF t.type = 2 .or. exist( str(2) + str(t.id), "your_table", "type_ID" )

If you don't change them to string values, then adding Type 2 plus ID 345 would result in 347 and there is a possibility that another record might have Type 1 plus ID 346 and the result would be the same 347.

Note also that this "one loop" method only works because you are MARKING the records. If you were deleting records, you would either have to run the loop once to remove the matching Type 1 records and again to remove all the Type 2 records - or - guarantee that all Type 1 records would come before their matching Type 2 records. The second method could be done by setting an index or query by Type before starting the loop.

[G Gabriel]

Re: script help

Correction:
Create an index based on ID filtered to only have Type 2 and let's call this index "ID"

t=table.open("your_table")
t.fetch_first()
while .not. t.fetch_eof()
if t.type="type 2".or.exists(t.id,"your_table","ID")
t.change_begin()
t.mark()
t.change_end(.t.)
end if
t.fetch_next()
end while

Haven't read Cal's comments yet. Just had an episode of insomnia, thought about this and (and an online contest I am pariticpating in) knew I had to make a minor correction. Will read Cal's comment momentarily.

[G Gabriel]

Re: script help

I am giving Anis enough credit that he could modify the code based on whether it's character or numeric. My first inqury went un-answered, so I offered a generic code that should do the job.

Yes, "end if" is missing and won't take a genius to figure that out or fix it.

This just checks to see if there is a record - any record - that has an index that matches the current record's values. Since the current record's index had better match it's own values, this will be true for every record.

Not so. The index only has "Type 2". Evidently you are not quite clear on the concept.

I didn't read any further...

[G Gabriel]

Re: script help

duplicate entry.. disregard.

[Anis]

Re: script help

Thanks for all the help.
Actually, both fields are numeric, so as Cal mentioned both have to be converted with STR().

That type of help is why the message board is worth millions.

Thanks again

[CALocklin]

Re: script help

Not so. The index only has "Type 2".

You are quite correct. What's worse is that I saw that comment about the filtered index then forgot about it when setting up my test.

As for giving someone credit for being able to fix my code, that's just a difference of opinion. My take on that is that even though Anis might be able to work out those details, there are other readers who might not - so it helps to have accurate code and to provide more info about possible alternatives that may arise so even new users can learn. I've seen people get totally confused by very basic issues so I try to give detailed answers that have been tested. To most of us a missing END IF would be obvious but I wouldn't be shocked to have someone ask why it wasn't used or where it should go. I've seen equally "dumb" questions and probably even asked a few myself when first learning.

[Anis]

Re: script help

That worked perfect.

Thanks for every one.