Deleted records not retaining their deleted status in cursor

[captsnappy]

I'm trying to load a cursor from a table and have the deleted records be flagged as deleted in the cursor. Not working so far. Here's my code:

select * from orders where &mfiltstr into cursor crsRptListOrders NOFILTER readwrite

(&mfiltstr can include "and deleted()" or "and not deleted()" )

The select statement is working in that if the filter is "and deleted()" it only selects deleted records, but they don't show up as deleted in the cursor.

What obvious, simple thing am I missing?

Thank you, all VFP gods on the forum here.

 

[Dan Freeman]

When I've had to put this much effort into explaining the words I choose, I usually just change my words. <g>

You're really going to stand on "the help file is wrong", eh? You're going to say Deleted("alias") can't work? Even though it almost always does work, everywhere except inside SQL queries? And even inside SQL queries it sometimes works?

Even with all that, you're still standing on CAN'T DO IT?

 

[Olaf Doschke]

Dan, common!

What I said is

On the other side, if the need to query data from more table arises, it's not working to select deleted(), as you can't specify of what table you want to select it's deleted() status.

This is quite clearly defining the preconditions under which deleted() does not work and you therefore can't use it.

You're putting words in my mouth I never said, if you interpet it that way, as if I said the alias parameter never works.

I made my point very clear. This is really getting annoying and ridiculous of you.

Bye, Olaf.

 

[Olaf Doschke]

Why I think of alternatives as improper:

1. "You should not" still means you can, "You shouldn't" is only a recommendation, but you can overide it if you want.

2. "You must not" is too strong, thee is no law or rule disallowing to pass an alias in.

And I agree, can't also has the meaning of "are unable to", which is wrong. In my eyes it's still the least wrong phrase, if you take the meaning of cannot as in the idioms I gave above.

So in some sense all those alternatives can be interpreted wrong and if you're picky you always can blame me saying something wrong.

So maybe you'd be satisfied if I rephrase it without using any of these three alternatives, but say:

If you specify an alias in functions like Deleted(alias), Recno(alias) or other workarea specific functions indide an SQL select, you don't get what you expect, as the SQL engine works on different alias names internally. the functions don't error, the functions actually work on the specified alias, but SQL does query on other workareas.

Are you satisfied with that wording now?

Sample code, that illustrates this even in a single alias query:

Set Deleted Off
Create Cursor curDeleted (iID I, cText C(10))
Insert into curDeleted Values (1,"hello")
Insert into curDeleted Values (1,"world")
Delete Next 1 && deletes the record (1,"world")
Insert into curDeleted Values (2,"world")

Select Deleted() as Deletedflag, * From curDeleted ;
Into Cursor curResultWithoutAlias Nofilter

Select Deleted('curDeleted') as Deletedflag, * From curDeleted ;
Into Cursor curResultWithAlias Nofilter

Goto 2 In curDeleted

Select Deleted('curDeleted') as Deletedflag, * From curDeleted ;
Into Cursor curResultWithAlias2 Nofilter

* and this try will even error:
Select Deleted('t1') as Deletedflag, * From curDeleted alias t1 ;
Into Cursor curResultWithAlias2 Nofilter

If you expect both queries to give the same result, then you'll be surprised, if you take a look at the result cursors. It's not working to specify an alias even in that single alias query. And that is where I get back to you can't specify an alias, as you don't know which alias names SQL is using internally and you can't specify an alias name you don't know in advance. even if you use the SQL language alias this is not the foxpro alias the sql engine will use.

This is neither a bug of sql or deleted(), this is by design.

Bye, Olaf.

 

[Dan Freeman]

If you specify an alias in functions like Deleted(alias), Recno(alias) or other workarea specific functions indide an SQL select, you don't get what you expect, as the SQL engine works on different alias names internally. the functions don't error, the functions actually work on the specified alias, but SQL does query on other workareas.

Are you satisfied with that wording now?

Much clearer and more accurate, yes, and it is less likely to confuse people with less familiarity with the product (i.e. those asking questions about it).

 

[TamarGranor]

Yep. Olaf, the revised version is quite clear.

Tamar

 

[Olaf Doschke]

good, very nice.

Bye, Olaf.