×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to combine two cursors in foxpro9

How to combine two cursors in foxpro9

How to combine two cursors in foxpro9

(OP)
I have two cursors like this.

CODE

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

RE: How to combine two cursors in foxpro9

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

RE: How to combine two cursors in foxpro9

This approach will let you keep your SQL formatting allowing you to jump between FoxPro and SSMS.
TEXT to stra textmerge noshow
SELECT
vAcp_InvDtl.nPoDtlID,vAcp_InvDtl.nBatchInvId,Acp_AdvancePayment.cUpdatedby as cUser, Acp_AdvancePayment.cBatchNo, Acp_AdvancePayment.nAdvanceNumber, Acp_AdvancePayment.dAdvanceDate, Acp_AdvancePayment.cPaymentNumber,Acp_AdvancePayment.cPaymentCurr as cBatchCurr ,Acp_AdvancePayment.nPaymentValue as nAdvanceValue ,
vAcp_InvDtl.cSuplName as cSupplier ,vAcp_InvDtl.cFtyCD as cFact ,vAcp_InvDtl.cOrderType as cType
FROM MAS.dbo.Acp_AdvancePayment
inner join MAS.dbo.vAcp_InvDtl on Acp_AdvancePayment.cBatchNo=vAcp_InvDtl.cBatchNo
where
Acp_AdvancePayment.dAdvanceDate>= ?thisform.txtFrom.Value
AND Acp_AdvancePayment.dAdvanceDate<= ?thisform.txtTo.Value
and vAcp_InvDtl.cOrderType=?thisform.cboOrderType.value
group by Acp_AdvancePayment.cUpdatedby,Acp_AdvancePayment.cBatchNo,Acp_AdvancePayment.nAdvanceNumber,Acp_AdvancePayment.dAdvanceDate,vAcp_InvDtl.nPoDtlID,
Acp_AdvancePayment.cPaymentNumber,Acp_AdvancePayment.cPaymentCurr,Acp_AdvancePayment.nPaymentValue,vAcp_InvDtl.cSuplName,vAcp_InvDtl.cFtyCD,vAcp_InvDtl.cOrderType,vAcp_InvDtl.nBatchInvId
order by Acp_FinalPayment.cBatchNo
ENDTEXT
SQLEXEC(hndOps,stra,'_All1')

Mike Yearwood - Former FoxPro MVP

RE: How to combine two cursors in foxpro9

(OP)
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,

CODE

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

CODE

cursor2
batchno     paymentno     paymentval
123         645           3845
357         25165         845 

I need my final cursor like this.

CODE

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.

CODE

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

how can I take this output?
thank you

RE: How to combine two cursors in foxpro9

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

CODE

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?

RE: How to combine two cursors in foxpro9

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

RE: How to combine two cursors in foxpro9

The selected fields have to match in each union. So make a common set of fields...
text to lccommonfields textmerge noshow
vAcp_InvDtl.nPoDtlID,vAcp_InvDtl.nBatchInvId,Acp_AdvancePayment.cUpdatedby as cUser ,Acp_AdvancePayment.cBatchNo,Acp_AdvancePayment.nAdvanceNumber,Acp_AdvancePayment.dAdvanceDate,
Acp_AdvancePayment.cPaymentNumber,Acp_AdvancePayment.cPaymentCurr as cBatchCurr ,Acp_AdvancePayment.nPaymentValue as nAdvanceValue ,
vAcp_InvDtl.cSuplName as cSupplier ,vAcp_InvDtl.cFtyCD as cFact ,vAcp_InvDtl.cOrderType as cType
endtext

Add the common fields to each union

TEXT to stra textmerge noshow
SELECT <<m.lcCommonFields>>
FROM MAS.dbo.Acp_AdvancePayment
inner join MAS.dbo.vAcp_InvDtl on Acp_AdvancePayment.cBatchNo=vAcp_InvDtl.cBatchNo
where Acp_AdvancePayment.dAdvanceDate>= ?thisform.txtFrom.Value AND Acp_AdvancePayment.dAdvanceDate<= ?thisform.txtTo.Value and
vAcp_InvDtl.cOrderType=?thisform.cboOrderType.value
group by Acp_AdvancePayment.cUpdatedby,Acp_AdvancePayment.cBatchNo,Acp_AdvancePayment.nAdvanceNumber,Acp_AdvancePayment.dAdvanceDate,vAcp_InvDtl.nPoDtlID,
Acp_AdvancePayment.cPaymentNumber,Acp_AdvancePayment.cPaymentCurr,Acp_AdvancePayment.nPaymentValue,vAcp_InvDtl.cSuplName,vAcp_InvDtl.cFtyCD,vAcp_InvDtl.cOrderType,vAcp_InvDtl.nBatchInvId
UNION
SELECT <<m.lcCommonFields>>
from blah
etc
etc
order by Acp_FinalPayment.cBatchNo
ENDTEXT

SQLEXEC(hndOps,stra,'_All1')

Mike Yearwood - Former FoxPro MVP

RE: How to combine two cursors in foxpro9

(OP)
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?

RE: How to combine two cursors in foxpro9

(OP)
I resolved my issue. Thank you for the helps bigsmile

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close