How to combine two cursors in foxpro9

[Niki_S]

I have two cursors like this.

stra="SELECT  vAcp_InvDtl.nPoDtlID,vAcp_InvDtl.nBatchInvId,Acp_AdvancePayment.cUpdatedby as cUser ,Acp_AdvancePayment.cBatchNo,Acp_AdvancePayment.nAdvanceNumber,Acp_AdvancePayment.dAdvanceDate,"
stra=stra+" Acp_AdvancePayment.cPaymentNumber,Acp_AdvancePayment.cPaymentCurr as cBatchCurr ,Acp_AdvancePayment.nPaymentValue as nAdvanceValue ,"
stra=stra+" vAcp_InvDtl.cSuplName as cSupplier ,vAcp_InvDtl.cFtyCD as cFact ,vAcp_InvDtl.cOrderType as cType  "
stra=stra+" FROM MAS.dbo.Acp_AdvancePayment inner join  MAS.dbo.vAcp_InvDtl on  Acp_AdvancePayment.cBatchNo=vAcp_InvDtl.cBatchNo "
stra=stra+"where  Acp_AdvancePayment.dAdvanceDate>= ?thisform.txtFrom.Value AND Acp_AdvancePayment.dAdvanceDate<= ?thisform.txtTo.Value and "
stra=stra+" vAcp_InvDtl.cOrderType=?thisform.cboOrderType.value "
stra=stra+" group by Acp_AdvancePayment.cUpdatedby,Acp_AdvancePayment.cBatchNo,Acp_AdvancePayment.nAdvanceNumber,Acp_AdvancePayment.dAdvanceDate,vAcp_InvDtl.nPoDtlID,"
stra=stra+" Acp_AdvancePayment.cPaymentNumber,Acp_AdvancePayment.cPaymentCurr,Acp_AdvancePayment.nPaymentValue,vAcp_InvDtl.cSuplName,vAcp_InvDtl.cFtyCD,vAcp_InvDtl.cOrderType,vAcp_InvDtl.nBatchInvId "
stra=stra+" order by Acp_FinalPayment.cBatchNo"
SQLEXEC(hndOps,stra,'_All1')
			
stra="SELECT  vAcp_InvDtl.nPoDtlID,vAcp_InvDtl.nBatchInvId,Acp_FinalPayment.cUpdatedby  as cUser,Acp_FinalPayment.cBatchNo,Acp_FinalPayment.dPaymentDate as dAdvanceDate ,Acp_FinalPayment.cPaymentNumber ,"
stra=stra+" Acp_FinalPayment.cPaymentCurr as cBatchCurr ,Acp_FinalPayment.nPaymentValue as nAdvanceValue , "
stra=stra+" vAcp_InvDtl.cSuplName as cSupplier ,vAcp_InvDtl.cFtyCD as cFact ,vAcp_InvDtl.cOrderType as cType  "
stra=stra+" FROM MAS.dbo.Acp_FinalPayment inner join MAS.dbo.vAcp_InvDtl on Acp_FinalPayment.cBatchNo= vAcp_InvDtl.cBatchNo where  "
stra=stra+" Acp_FinalPayment.dPaymentDate>= ?thisform.txtFrom.Value AND Acp_FinalPayment.dPaymentDate<= ?thisform.txtTo.Value "
stra=stra+" group by Acp_FinalPayment.cUpdatedby,Acp_FinalPayment.cBatchNo,Acp_FinalPayment.dPaymentDate,Acp_FinalPayment.cPaymentNumber,"
stra=stra+" Acp_FinalPayment.cPaymentCurr,Acp_FinalPayment.nPaymentValue,vAcp_InvDtl.cSuplName,vAcp_InvDtl.cFtyCD,vAcp_InvDtl.cOrderType, "
stra=stra+" vAcp_InvDtl.nPoDtlID,vAcp_InvDtl.nBatchInvId "
stra=stra+" order by Acp_FinalPayment.cBatchNo"
SQLEXEC(hndOps,stra,'_All2')

Now I want to combine these two into one cursor with all records without duplicating.
How can I do this?
Thank you

 

[Ljupce Zagorac]

Hi Niki,

The easiest way would be to create a cursor using UNION and then pull the data.
So just combine two queries (your variable "stra" has in one UNION in between).
The second "stra" will look like
stra=stra+" UNION " then the rest ...

Thanks
Ljupce

 

[Niki_S]

when I used Union it says selects are not union compatible.number or fields do not match.
In my cursors there have different fields. As an example,

cursor1
batchno     paymentno     paymentval      advanceno
123         567u          234             8765
345         246i          56              5646851
288         3874l         32              484

cursor2
batchno     paymentno     paymentval
123         645           3845
357         25165         845

I need my final cursor like this.

batchno     paymentno     paymentval      advanceno
123         567u          234             8765
345         246i          56              5646851
288         3874l         32              484
123         645           3845
357         25165         845

I did something like this.

select * from cursor1 ;
union ;
select * from cursor2 ;
into cursor cursor3

how can I take this output?
thank you

 

[Niki_S]

And I used this too. But they said incorrect syntax near 'union'

stra="SELECT  vAcp_InvDtl.nPoDtlID,vAcp_InvDtl.nBatchInvId,Acp_AdvancePayment.cUpdatedby as cUser ,Acp_AdvancePayment.cBatchNo,Acp_AdvancePayment.nAdvanceNumber,Acp_AdvancePayment.dAdvanceDate,"
stra=stra+" Acp_AdvancePayment.cPaymentNumber,Acp_AdvancePayment.cPaymentCurr as cBatchCurr ,Acp_AdvancePayment.nPaymentValue as nAdvanceValue ,"
stra=stra+" vAcp_InvDtl.cSuplName as cSupplier ,vAcp_InvDtl.cFtyCD as cFact ,vAcp_InvDtl.cOrderType as cType  "
stra=stra+" FROM MAS.dbo.Acp_AdvancePayment inner join  MAS.dbo.vAcp_InvDtl on  Acp_AdvancePayment.cBatchNo=vAcp_InvDtl.cBatchNo "
stra=stra+"where  Acp_AdvancePayment.dAdvanceDate>= ?thisform.txtFrom.Value AND Acp_AdvancePayment.dAdvanceDate<= ?thisform.txtTo.Value and "
stra=stra+" vAcp_InvDtl.cOrderType=?thisform.cboOrderType.value "
stra=stra+" group by Acp_AdvancePayment.cUpdatedby,Acp_AdvancePayment.cBatchNo,Acp_AdvancePayment.nAdvanceNumber,Acp_AdvancePayment.dAdvanceDate,vAcp_InvDtl.nPoDtlID,"
stra=stra+" Acp_AdvancePayment.cPaymentNumber,Acp_AdvancePayment.cPaymentCurr,Acp_AdvancePayment.nPaymentValue,vAcp_InvDtl.cSuplName,vAcp_InvDtl.cFtyCD,vAcp_InvDtl.cOrderType,vAcp_InvDtl.nBatchInvId "
stra=stra+" order by Acp_FinalPayment.cBatchNo "
stra=stra+" UNION " 
stra=stra+" SELECT  vAcp_InvDtl.nPoDtlID,vAcp_InvDtl.nBatchInvId,Acp_FinalPayment.cUpdatedby  as cUser,Acp_FinalPayment.cBatchNo,Acp_FinalPayment.dPaymentDate as dAdvanceDate ,Acp_FinalPayment.cPaymentNumber ,"
stra=stra+" Acp_FinalPayment.cPaymentCurr as cBatchCurr ,Acp_FinalPayment.nPaymentValue as nAdvanceValue , "
stra=stra+" vAcp_InvDtl.cSuplName as cSupplier ,vAcp_InvDtl.cFtyCD as cFact ,vAcp_InvDtl.cOrderType as cType  "
stra=stra+" FROM MAS.dbo.Acp_FinalPayment inner join MAS.dbo.vAcp_InvDtl on Acp_FinalPayment.cBatchNo= vAcp_InvDtl.cBatchNo where  "
stra=stra+" Acp_FinalPayment.dPaymentDate>= ?thisform.txtFrom.Value AND Acp_FinalPayment.dPaymentDate<= ?thisform.txtTo.Value "
stra=stra+" group by Acp_FinalPayment.cUpdatedby,Acp_FinalPayment.cBatchNo,Acp_FinalPayment.dPaymentDate,Acp_FinalPayment.cPaymentNumber,"
stra=stra+" Acp_FinalPayment.cPaymentCurr,Acp_FinalPayment.nPaymentValue,vAcp_InvDtl.cSuplName,vAcp_InvDtl.cFtyCD,vAcp_InvDtl.cOrderType, "
stra=stra+" vAcp_InvDtl.nPoDtlID,vAcp_InvDtl.nBatchInvId "
stra=stra+" order by Acp_FinalPayment.cBatchNo"
SQLEXEC(hndOps,stra,'_ACPgrn')

What is the syntax error in this code?

 

[TamarGranor]

UNION requires both queries to have the matching lists of fields. You need to make sure they have the same number of fields and that the fields are type-compatible (that is, similar enough in type for the engine not to complain about it).

Tamar

 

[Niki_S]

I have one uncommon field in my cursor 1. So how can I add that to the same cursor?
Can't I join all these into one and get all the records into one cursor or if we think we created a cursor and append those cursors records into new cursor?

 

[Niki_S]

I resolved my issue. Thank you for the helps