How to insert data into a cursor using SCAN ENDSCAN?

[Niki_S]

Hi,

I have a cursor with several designations as below,

cItem
ACCOUNT ASSISTANT
ASSISTENT OFFICE
​

In here if ACCOUNT ASSISTANT the results are ,

cFact nEmpNo cFullName
SGL 1 ABC Perera
SGL 2 XYZ Silva
SGL 3 DFG Perera
​

And if ASSISTENT OFFICE the results are,

cFact nEmpNo cFullName
SGL 4 Nikini
SGL 5 Hashen
SGL 6 Samekshi
​

And my final result should be like this,

cFact nEmpNo cFullName
SGL 1 ABC Perera
SGL 2 XYZ Silva
SGL 3 DFG Perera
SGL 4 Nikini
SGL 5 Hashen
SGL 6 Samekshi
​

For this I did something like this.

CREATE CURSOR Emp_Dtl(cFact C(5) Null,nEmpNo N(15,0) Null,cFullName C(100) Null,cDepartment C(50) Null,dJoin D Null,dResign D Null,Months int Null, Period c(100) Null)


SELECT Selecteditems
SCAN

stra = "SELECT cFact,nEmpNo,cFullName "
stra = stra + "from HRIS.dbo.vHRIS_Deletion where cDesignation = ?Selecteditems.cItem AND cResignCat = 'RESIGN' "
SQLEXEC(hndOps,stra,'Emp_Detl')

SELECT Emp_Dtl
replace cFact WITH Emp_Detl.cFact
replace nEmpNo WITH Emp_Detl.nEmpNo
replace cFullName WITH Emp_Detl.cFullName

ENDSCAN
​

From this I can't get any records. How can I get my final output as I mentioned above?

Thank you.

 

[Chris Miller]

Well, in this case of scanning two records and use each one to get 4 records from MSSQL Server, you can do it in one go without the SCAN..ENDSCAN with an IN clause:

Text to star NOSHOW
SELECT cFact,nEmpNo,cFullName from HRIS.dbo.vHRIS_Deletion 
where cDesignation IN ('ACCOUNT ASSISTANT','ASSISTENT OFFICE') AND cResignCat = 'RESIGN'
Endtext
SQLEXEC(....)

The problem you face is that every SQLEXEC creates a new result, it doesn't add to an already existing cursor, if you reuse the same result name 'EMP_Detl'. Whatever you already had is dropped and the new partial result is the only thing you get.

So you would not do that, use a result cursor name to only hold the partial result and APPEND them to a full cursor.

And overall, this is not a preferred way to do it. You're actually doing an alternative to joining the VFP data you have in Selecteditems with MSSQL data and that's a bad way to do this, you better had an actual JOIN in the SQL so you get all data at one go. So you bring the selecteditems into a TEMP table to join it with SQL, not with multiple SQLEXECs and APPENDs.

Also, you better start by having a foreign key and not a name to join with. You know, joins of tables are always best done by primarykeyfield=foreignkeyfield.


Chriss