How to get output in less time in foxpro?

[Niki_S]

SELECT Batch_no
SCAN
TEXT TO stra NOSHOW
SELECT cBatchNo,nPoDtlID,cPoLine,nBatchInvId,nSuplID,cSuplName,cPaymentCurr,nInvValue,nConvRate,cTag FROM MAS.dbo.vAcp_All_InvDtl WHERE  nPoDtlID=?Batch_no.nPoDtlID  AND cBatchNo NOT in (select cBatchNo from MAS.dbo.Acp_FinalPayment)
ENDTEXT

Use In Select('tempOutstanding')

SELECT Batch_no 
SCAN 
	SQLEXEC(hndOps,stra,'_TempO') 
  If NOT Used('tempOutstanding')
     Select * From  _TempO Into Cursor tempOutstanding READWRITE 
  Else
     SELECT tempOutstanding
     APPEND FROM DBF('_TempO') 
  Endif
ENDSCAN 
ENDSCAN

This is my code and when I use this code it takes more time to get my output. How can I get my output in less time?
Thank you.

 

[foxincloud]

do one single query using the IN clause

 

[Niki_S]

I did it like this.

stra="SELECT cBatchNo,nPoDtlID,cPoLine,nPoHeadrID,nBatchInvId,nSuplID,cSuplName,cInvCurr,nInvValue,nConvRate,cTag "
stra=stra+" FROM MAS.dbo.vAcp_All_InvDtl WHERE  cBatchNo NOT in (select cBatchNo from MAS.dbo.Acp_FinalPayment) and nPoDtlID in (select nPoDtlID from Batch_no ) "
SQLEXEC(hndOps,stra,'PO')

Batch_no is a cursor. When I run my form this it says invalid object name Batch_no .
How to do this in proper way?
Thank you

 

[Chris Miller]

As I said in a previous thred, instead of fetching Batch_no at all, put in the query which get's Batch_No.

Instead of in (select nPoDtlID from Batch_no ) write in your query:

WHERE  cBatchNo NOT in (select cBatchNo from MAS.dbo.Acp_FinalPayment) and nPoDtlID in (Select nPoDtlID from ...)

Or, just use a list of IDs instead of a subquery.

Chriss

 

[Niki_S]

I tried those ways also. But there are not working.

https://www.tek-tips.com/viewthread.cfm?qid=1811087

This is my previous thread link. I got an error there. Can you please tell me how to fix that?