ACTIVE' must be created with SELECT...INTO TABLE." goterror 7

[jlg13]

I have been running a procedure that contains the below code weekly without error for months... until this morning.

Since last week (the last successful run), I have added a few indexes to some of my tables. Wondering if that might be the cause?

Here's the code:

SELECT cert_code, min_remain FROM LM_Pkage;
WHERE min_remain > 0;
INTO CURSOR active
SELECT email, a.cert_code, min_remain FROM LM_Em2Cc e, active a;
WHERE e.cert_code = a.cert_code;
INTO CURSOR email

 

[Koen Piller]

Hi,
The first thing which comes into my mind:
disable the indexes and the eventually other code or better upload your backup and see if the error still occurs.
If it does not you know where to look.

Regards,
Koen

 

[Mike Lewis]

The problem is that you are using a "repackaged" cursor, that is, you are creating a cursor with a SELECT, and then tying to use that cursor in another select.

The solution is to add NOFILTER to the first SELECT.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Just to clarify my previous answer ...

When a SELECT meets certain conditions, VFP doesn't actually create a new cursor. It simply applies a filter, and possibly a different index order, to the original cursor (or table). Usually, you don't need to know anything about that. It happens behind the scenes.

The problem arises when you try to use that filtered table as the input to another SELECT. That second SELECT requires an actual table or cursor to exist, rather than the filtered version of the original cursor. It can't find that physical table, hence the error message.

Your error message suggests that you should use INTO TABLE instead of INTO CURSOR (for the first SELECT). That would certainly avoid the issue. But adding NOFILTER (or alternatively READWRITE) to the SELECT will also solve the problem, as that tells VFP not to filter the original table but to create a new table or cursor.

The reason that the issue has only just arisen is probably because you added some indexes, which meant that VFP was able to optimise the query (which it couldn't do before), and was in turn able to create the filtered cursor.

I hope I haven't confused you with this explanation. I'm writing it in a hurry.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi,

SELECT cert_code, min_remain FROM LM_Pkage;
WHERE min_remain > 0;
INTO CURSOR active
SELECT email, a.cert_code, min_remain FROM LM_Em2Cc e, active a;
WHERE e.cert_code = a.cert_code;
INTO CURSOR email

In addition to what Mike said, you might try one single statement e.g.

SELECT LMP.cert_code, LMP.min_remain, LME.eMail, LME.min_remain FROM LM_Pkage LMP ;
JOIN LM_Em2Cc LME on LME.cert_code = LMP.cert_code 
WHERE LMP.min_remain > 0;
INTO CURSOR crsEmail

I made a one-one translation - please check if you need the field min_remain twice

hth
MarK

 

[Mike Lewis]

May I also make a comment on the actual error message dialogue. I assume this is generated by your custom error-handler.

In my opinion, you should never give your end-users the choice of Abort, Retry, Ignore when displaying an error message. Allowing users to ignore an error will nearly always lead to further problems. Once an error has been detected, you can't be sure about anything within your program's environment. You don't know what variables are still in scope, what work areas are still open, or many similar things. Allowing the program to continue running on those circumstances is a bad idea.

The Retry option is fairly useless. If the program encounters an error in a particular line of code, the error will still be there when the same line is executed again.

The only time I would allow Retry after an error is if the error is something that is within the user's control. For example, if it results from a lack of disk space or a lack of write/update permission. In those cases you might give the user the opportunity to fix the problem and try again. Or, if it is a file/record locking issue, you might invite the user to wait until the lock is released and then try again. But in all other circumstances, there is no point in allowing a retry - it just raises users' hopes.

Finally - and less important - I prefer to avoid certain emotive terms, and "abort" is one of them. "Cancel" expresses the idea just as well without any emotional connotations.

For all the above reasons, when encountering a non-recoverable error, just politely tell the user that an error has occurred (don't scare them off with any technical information) and then close the application as gracefully as possible.

Apologies for this digression from the original question, but I hope you will find it useful.

Mike
_________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[jlg13]

First of all, Thank you to Mike, Mark & Koen for your posts.

I discovered which "new" indexes I added and removed, the error went away.

Your posts were very helpful in improving my understanding.

Mike, regarding your last post, I completely understand. This application was created by my dad for my business (He past away Dec 2018) so the user is "controlled". The office staff typically hits retry if the error is caused by a procedure that requires exclusive access. They do not hit Abort or ignore unless instructed to do so for the exact reasons you state. My dad builds 2 files any time an error occurs providing a ton of information such as all the mem variables, programs open, tables open, etc. My challenge these days is maintaining his work as I am not an official programmer yet having been exposed for 25 years, can at least maintain.

Needless to say this forum is valuable to someone like me as I can use all the help I can get. I am about to embark on actual coding so you'll probably hear back from me a few more times!

Again thank you all
Joe

 

[Olaf Doschke]

I discovered which "new" indexes I added and removed, the error went away.

The fact the cursor becomes a filter cursor is actually telling you the indexes are working very well for this query. No need to remove them again, just add the NOFILTER option. as you want to use the cursor in a further query, or as MarK did, get directly to the end result in one query.

The unfortunate nature of such cursors to not be available for further queries is because SQL always goes back to the dbf files, so it SQL would go back to the full data, not just the filtered data, because DBF() of a filter cursor alias will be the original dbf file.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name