Huh? I can't do SQL off a Cursor created by SQL, how come?

[FoxNovice]

Runnin another SQL off a cursor just created by an SQL is not given a second thought in DOS Foxpro.

This isn't allowed in VFP 5.0+. Why ???????

Thanks

(VFP)FoxNovice

 

[rgbean]

If you are speaking of SQL Queries, I believe you can. While you might run into some problems if the original SQL SELECT isn't a true cursor, but a FILTERED set of records from a single underlying table, this can easily be 'fixed' by adding the NOFILTER clause (or a dummy constant field - depending on your version of VFP).

Can you provide a simple sample of where it doesn't work for you?

Rick

 

[FoxNovice]

Hi Rick

I have a database with several tables in it. A DOS program i have is Creating several cursors and then pulling from each to create it's final cursor for a Report.

I did a simple SQL select against one of the tables and use "into cursor tbl2"

When I tried another SQL against "tbl2" to creat a subset of records (really just to look at) ig an error saying "tbl2" needed to be created with "into table"

Well I did that and I'm still getting the same error. clearly I'm doing something wrong

=======================================================
select * from pict ;
where between(datestamp,ctot('01/01/2002 11:59:00PM'),ctot('01/06/2002 07:00:00AM')) ;
order by datestamp ;
into cursor pict2

select * from pict2 where shift = 3

ERROR:
'pict2' must be created with "SELECT...INTO TABLE"
=======================================================

Thanks

FoxNovice

 

[chpicker]

The problem you're running toto is that FoxPro "cheats". It sees that your SELECT statement is extremely simple, so instead of actually pulling the data and creating a cursor out of it, the table is simply opened AGAIN with a filter. Try this instead:

=======================================================
select * from pict ;
    where between(datestamp,;
      ctot('01/01/2002 11:59:00PM'),;
      ctot('01/06/2002 07:00:00AM')) ;
    order by datestamp ;
    into cursor pict2 NOFILTER

select * from pict2 where shift = 3
=======================================================

Note the NOFILTER clause in the SELECT statement.

If you're using an older version of VFP, instead of adding NOFILTER just change the first line to:

select *,.T. as dummyfield from pict

 

[rgbean]

Try:
select * from pict ;
where between(datestamp, ;
ctot('01/01/2002 11:59:00PM'),;
ctot('01/06/2002 07:00:00AM')) ;
order by datestamp ;
into cursor pict2 NOFILTER

OR

select *, " " as dummy from pict ;
where between(datestamp, ;
ctot('01/01/2002 11:59:00PM'),;
ctot('01/06/2002 07:00:00AM')) ;
order by datestamp ;
into cursor pict2

Rick

 

[FoxNovice]

Thanks chpicker

That seemed to do the trick, Now I'll have to spend some time going thru the book seeing if i can get a better understanding.

So I should just use the "NOFILTER" when I am creating cursors that I want to use again in another SQL....

Thanks
(VFP)FoxNovice

P.S. I am using VFP5.0 Pro

 

[FoxNovice]

Thanks rick..

I see you went to the command window and debugged it..thanks for your time invested in my problem

(VFP)FoxNpovice

 

[chpicker]

It would be a good idea. It can help to learn WHY VFP does it, but it's not necessary. Including NOFILTER doesn't cause any problems. It just ensures that you end up with a new cursor rather than a FILTERED table.

Ian

 

[FoxNovice]

Gotcha ;o)

Thanks..

I'll beeee baaak...lol

(VFP)FoxNovice