SET DELETE ON QUESTION RELATING TO SQL

[hawkieboy]

Hi All

I have a strange one:

I run the following with set deleted on

SELECT * FROM GHDD3 INTO CURSOR RESULTS WHERE alltrim(UPPER
(ghdsname)) = "SHIT"
WAIT WINDOW STR(RECCOUNT('RESULTS'))

the wait wind shows 0

when i run the above with set deleted off the wait wind shows the record count of the ghdd3 table.

the table ghdd3 has no deleted records so I am a bit confused. could anyone explain what is going on to me

Many Thanks

Nick

your help and advice is appreciated

 

[rgbean]

Nick,
Any time you Select all and only the fields from a single table, VFP will try to save time by creating a "filtered" result set on the original table, rather than creating a "real" cursor. Therefore RESULTS will actually point to the original table! If you change the SELECT to

SELECT *, " " as junk FROM GHDD3 INTO CURSOR RESULTS ;
  WHERE alltrim(UPPER(ghdsname)) = "SHIT"

then VFP is forced to create a real cursor, and I believe you'll find that you'll get what you expect.

Rick

 

[StewartUK]

If you add the keyword NOFILTER to the end of the SQL statement, you also will get a real cursor.

Stewart

 

[rgbean]

Thanks Stewart, I always forget the "new" fixes (Since VFP 6.0 SP3?) - I've been using the old techniques for too long!

Rick

 

[KeithTrangmar]

You can also use _TALLY to get at the number of records returned by the last operation, which in this case would be the record count, even without resorting to the use of NOFILTER etc.. If you don't actually need the results in a distinct table of their own (i.e. a filtered view of an existing table will suffice), not resorting to NOFILTER can sometimes make a significant difference to performance.

Keith

 

[hawkieboy]

Thanks guys your comments have been really helpfull



your help and advice is appreciated