×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)

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

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

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

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

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

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

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

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

Hi,

Quote:


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.

CODE -->

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

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

3
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

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

(OP)
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

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

Quote (jlg13)

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close