Hi,

Im using sqlpassthrough to query tables in sql for a report. Im encountering some problems while executing my codes. The program consist of 3 tables. Namely: Items,Itemledger, customers

&& I first query the ITEMS table to get the result set that I want
csql _items = [Select * from items where itemid = '] + cItemid + [']
nResult = sqlexec(nConn,csql_items,'cur_items')

&& Then I scan the items and within the scan process I query the Itemledger
select cur_items
scan
csql_itmldgr = [Select * from Itemledger where itemid = '] + cItemid + [']
nResult = sqlexec(nConn,csql_itmldgr,'cur_ldgr')

&& I then scan the cur_ldgr to validate a case statement
select cur_ldgr
scan
do case
case vopt = 'DS'
vdcno = cur_ldgr.dcno
case vopt = 'RS'
vdcno = cur_ldgr.cdcno
case vopt = 'SS'
vdcno = cur_ldgr.sdcno
endcase

csql_customers = [Select * from customers where dcno = ] + str (vdcno)
nresult = sqlexec(nconn,csql_customers,'cur_customers')

endscan
endscan

the itemledger consist of millions of records, scanning it and creating the cur_customers cursor in every record consumes so much computer memory. Is there any other way prevent this? Does creating a cursor in sqlpassthrough consumes too much memory if called from a repeated action like scan? Do remote views run or queries much faster than sqlpassthrough?

Thanks in advance