>I'm trying to do a SELECT from a table where some of the records are deleted. I need to include the deleted records in the result set. I've done a SET DELETED OFF and my SELECT. If I don't filter the list, eg SELECT * FROM myTable INTO CURSOR zzz it maintains the deleted tag. As soon as I try filtering the table the deleted tag disappears:
>
>
>SELECT iid, ;
> fk_product, ;
> avail_day, ;
> editdatetime ;
> FROM specials ;
> WHERE editdatetime > THIS.dLastUpdate ;
> INTO CURSOR v_specials
>
>
> This includes the deleted records but seems to clear the deleted flag. Is there some keyword I'm missing? Is this even possible?
>I can work around this by doing:
>
>
>SELECT iid, ;
> fk_product, ;
> avail_day, ;
> editdatetime, ;
> DELETED() AS IsDeleted ;
> FROM specials ;
> WHERE editdatetime > THIS.dLastUpdate ;
> INTO CURSOR v_specials
>
>
> But for my own curiosity I'm still wondering if I can do it more directly.
The output cursor is a table in its own right, completely separate from "Specials" (unless it's a filtered view, but that's a separate issue). If SELECT determines that a row should be written to the cursor, by definition it "belongs" there and there is never any reason to set the deleted flag on it.
Your workaround is exactly the right way to get the result you want.
Regards. Al
"Violence is the last refuge of the incompetent." -- Isaac Asimov
"Never let your sense of morals prevent you from doing what is right." -- Isaac Asimov
Neither a despot, nor a doormat, be
Every app wants to be a database app when it grows up