[SOLVED] Accessing multiple tables in SQLEXEC statement, SQL Pass Through

Accessing multiple tables in SQLEXEC statement, SQL Pass Through

I create a cursor called 'table1_data' from the statement:

lnResult1 = SQLEXEC(m.hConn, 'select * from table1 where status like ?m.seeStatus','table1_data')

Now I want to access this table in my next SQLEXEC statement:

lnResult2 = SQLEXEC(m.hConn, 'select * from table2 where table2.item_id = table1_data.item_id','table2_data')

however I get the error: The multi-part identifier table1_data.item_id could not be bound. Any help much appreciated!

Solution

In first case table1_data is a local cursor and you cannot use it in second call which is made to server. However since table1_data is also coming from server side you could instead do this:

*** Using text ... endtext just to make the SQL more readable

local lcSQL 
text to m.lcSQL noshow
select * from table2
where exists (select * from table1
              where table2.item_id = table1_data.item_id and 
                    table1.status like ?m.seeStatus);
endtext

lnResult1 = SQLEXEC(m.hConn, 'select * from table1 where status like ?m.seeStatus','table1_data')
lnResult2 = SQLEXEC(m.hConn, m.lcSQL,'table2_data')

EDIT: BTW you can return 2 results from a single SQLExec() call however it is not worth it here in this case. Two separate queries as above is just fine (and remember you could make them updatable per table).