Accessing multiple tables in SQLEXEC statement, SQL Pass Through-CodePudding

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!

CodePudding user response：

You can join both tables, but instead of SELECT * you should always neme the columns that you only need.

that helps also in future.

lnResult1_2 = SQLEXEC(m.hConn, 'select table1.*,table2.* from table1, table2 where table2.item_id = table1_data.item_id AND  status like ?m.seeStatus')

I can't find any code related to joins, but

lnResult1_2 = SQLEXEC(m.hConn, 'select table1.*,table2.* from table1 JOIN table2 ON table2.item_id = table1_data.item_id WHERE  status like ?m.seeStatus')

should also work, but as i said i can't find any code that uses explicit join, so you can give it a try and give a short response

CodePudding user response：

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).