can I use 2 cursors in an sql select?

[GarryC]

Hi guys,

I am using cusor 'c_query' against which I run a SELECT joined to a table, and output to the original cursor. I have a long list of these which are all run based on conditions. This works fine if the cursor is joined to a table but in the following case 'div_sel' is also a cursor. When I run cursor joined to cursor I get a 'not a table error'. Is this possible or do I have to create a 'div_sel' table first.

IF RECCOUNT("div_sel&quot > 0
SELECT c_query.*;
FROM c_query INNER JOIN Div_sel ;
ON ALLTRIM(c_query.division) == ALLTRIM(div_sel.division);
INTO CURSOR c_query
ENDIF

Cheers, Garry.

 

[ramani]

Hi Garry

When a cursor is created, depending on if there are selected records, the curcor gets created or dont get created. This can create a problem. To avoid this, you can create a DBF instead of a cursor and then deleted them.

One more important point is that when cursors are created, there is no surity that it stays as a nonexistent cursor table... meaning.. this can just be a filtered AGAIN opened table as per the choice of SQL. So to force this type of cursor available, we have to code...

SLECT .... FROM myTAble INTO CURSOR cursor1
IF _tally > 0
cAlias1 = ALIAS()
ELSE
cAlias1 = ""
ENDIF

... other code...

IF cALias1 = ""
SELECT ... FROM table1 INTO cursor2
ELSE
SELECT a.*;
FROM (cALias1) A INNER JOIN Div_sel B ;
ON ALLTRIM(a.division) == ALLTRIM(b.division);
INTO CURSOR cAlias2
ENDIF

.. etc...

By using the a or b as alias name inside the SQL query solves the naming convention.




____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

When you ask VFP questions, please add VFP version.

 

[GarryC]

Not quite sure if this is the answer I am looking for. I will try to clarify my question.

I have a cursor 'c_query' from a previous SQL SELECT TO CURSOR c_query.

I also have a cursor 'sel_div' that was created using CREATE CURSOR and then records appended from an array.

Both have records. What I want to do is filter records by using 'c_query' as the left table and 'sel_div' as the right JOINED by the field 'division'. The output goes TO CURSOR c_query (same name as the original left side in) replacing the original 'c_query'.

I repeat the filter using 'c_query' as the input and the output a number of times. When the left side is a cursor and the right side a table it works fine, but when I use a cursor for both left and right sides is fails with an error 'not a table'.

I am trying to avoid if possible creating a table for 'sel_div' and deleting it.

 

[Mike Lewis]

GarryC,

In general, there is no problem in creatng a cursor (by whatever means) and using it in a SELECT.

I think your problem is more likely to be related to the fact that you are overwriting a cursor in the same command as you are selecting from it. I've never seen anything documented that says you can't do that, but it seems to be asking for trouble to try to do so.

Is there any reason why you can't just send the results to a completely new cursor?

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[ramani]

HI Garry

I have the sane view as Mike Lewis has. You cannot create a cursor with the same name as its participating tables or cursors.

When you have to work with variables and so, need to have the same name, I would suggest use the same variable name pointing to a different cursors.. I dont know if you get my point.. For example..

SELECT * FROM myTable INTO CURSOR SYS(3)

cALias = ALIAS()

Now.. you can start using this cALias whereever you need.
Next time you want the newcursor out of the above..

SELECT * FROM (cALias) INTO CURSOR SYS(3)
cALias = ALIAS()

This way, the variable cAlias is kept same, whereas SQL sees it as different cursors.






____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

When you ask VFP questions, please add VFP version.

 

[GarryC]

OOPS! Feal like a bit of a idiot. Commented out code to test and accidently commented out the select that created the right side 'c_query' cursor. So 'not a table' was correct, the cursor wasn't there. Works fine now.

I have used this same cursor 'in' as 'out' for years and works a treat but i just upgraded from v6 to v8 and thought something may have changed. I use it all the time to build simple query generators. I first create a cursor with all the data. I then present a form with a multitude of drop down picklist options. When the user hits a 'run' button the code tests to see if the picklists contains data and filters the data in a SELECT against the cursor. Same cursor each time means you can either have a list of SELECTS or loop through the picklists and conditionaly run only those that have values selected. Create a SELECT using the CURSOR from the previous SELECT output, as the input for the next SELECT. It also means the filtering is progressive so if you take a field that will have the most effect first, ie 'Division', we have 2 so filtering division 1st cuts my data by half, then the next select 'state' again reduces my data by one fifth, etc. Depending on the amount of data, this method can work extreamly fast as the SELECT becomes very small very fast. The end result is a CURSOR with the same name as the original but with all the data filtered. Its often faster than building a query string and running a single complex query or the difference is so small its not noticable. And best of all its very very simple and easy to code!

Thanks for everyones replies. Maybe the above will be of use if you have never tried it before.
Cheers, Garry