SQL Select from 2 values of cursor

[Mike200]

Hi all I am working on a import script from a payroll software to a legacy foxpro app and need to figure out this last portion and I have literally exhausted myself searching online and could really use a hand. Never coded with VFP9 prior to this, so here goes...

LOCAL lcunion, lcclassid
CREATE CURSOR Tsheets (username C (45),;
empid C (6),;
fname C (45),;
lname C (45),;
num C (45),;
group C (45),;
workdate C (10),;
day C (3),;
start C (45),;
end C (45),;
timezone C (2),;
hoursr N (6, 2),;
jobname C (45),;
trackid C (4),;
classid C (2),;
wcid C (2),;
jobid C (45),;
unionid C (2),;
location C (45),;
notes C (254),;
approved C (45),;
glid C (3),;
craftid C (3),;
rater N (7, 3))

APPEND FROM (this.filename) TYPE CSV

lcunion = unionid
lcclassid = classid

REPLACE empid WITH ALLTRIM(empid),;
jobid WITH ALLTRIM(jobid),;
trackid WITH ALLTRIM(STRTRAN(trackid, '-', ' ')),;
workdate WITH STRTRAN(workdate, '-', '/'),;
classid WITH ALLTRIM(classid),;
glid WITH ALLTRIM('578'),;
craftid WITH ALLTRIM('ELE'),;
wcid WITH ALLTRIM(wcid),;
unionid WITH ALLTRIM(unionid),;
hoursr WITH(hoursr),;
rater WITH(rater) ALL

I need to be able to get the payrate from a specific lcunion and lcclass value placed in rater while it is looping over the csv file.
Ex:
REPLACE empid WITH ALLTRIM(empid),;
jobid WITH ALLTRIM(jobid),;
trackid WITH ALLTRIM(STRTRAN(trackid, '-', ' ')),;
workdate WITH STRTRAN(workdate, '-', '/'),;
classid WITH ALLTRIM(classid),;
glid WITH ALLTRIM('541'),;
craftid WITH ALLTRIM('PTR'),;
wcid WITH ALLTRIM(wcid),;
unionid WITH ALLTRIM(unionid),;
hoursr WITH(hoursr),;
rater WITH(SELECT Payrate FROM unclass WHERE Unionid = m.lcunion AND id = m.lcclassid) ALL

I realize this is not allowed here for example only.

Thank you in advance!

 

[mjcmkrsr]

Hi,
You might try something like below

LOCAL lcunion, lcclassid
[highlight #FCE94F]LOCAL ARRAY laRate[1][/highlight]

CREATE CURSOR Tsheets (username C (45),;
empid C (6),;
fname C (45),;
lname C (45),;
num C (45),;
group C (45),;
workdate C (10),;
day C (3),;
start C (45),;
end C (45),;
timezone C (2),;
hoursr N (6, 2),;
jobname C (45),;
trackid C (4),;
classid C (2),;
wcid C (2),;
jobid C (45),;
unionid C (2),;
location C (45),;
notes C (254),;
approved C (45),;
glid C (3),;
craftid C (3),;
rater N (7, 3))

APPEND FROM (this.filename) TYPE CSV

[highlight #FCE94F]SCAN[/highlight]

lcunion = unionid
lcclassid = classid 

[highlight #FCE94F]SELECT Payrate FROM unclass WHERE Unionid = m.lcunion AND id = m.lcclassid into array laRate
[/highlight]

[highlight #FCE94F]IF VARTYPE(laRate[1]) = "N"[/highlight]

REPLACE empid WITH ALLTRIM(empid),;
jobid WITH ALLTRIM(jobid),;
trackid WITH ALLTRIM(STRTRAN(trackid, '-', ' ')),;
workdate WITH STRTRAN(workdate, '-', '/'),;
classid WITH ALLTRIM(classid),;
glid WITH ALLTRIM('578'),;
craftid WITH ALLTRIM('ELE'),;
wcid WITH ALLTRIM(wcid),;
unionid WITH ALLTRIM(unionid),;
hoursr WITH(hoursr),;
[highlight #FCE94F]rater WITH laRate[1][/highlight]

[highlight #FCE94F]ELSE
= MessageBox("Error in laRate",16)
ENDIF[/highlight]

[highlight #FCE94F]ENDSCAN
[/highlight]

And of coure you'll have to delete the [highlight #EF2929]ALL[/highlight] parameter

hth
MarK

 

[GriffMG]

You don't need to trim the constants do you?
I don't understand why you are replacing hoursr with the same value
I am imagining that you are trimming the other values to get them ranged left?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike200]

Thanks that worked great!
Now i see what I did wrong, I never defined the local array or used the SCAN.
I probably would have got it eventually but this was much easier to ask for help being new to VFP.
BTW, first post ever asking for help.
Definitely worth the join!
Much respect!

Thanks again!

 

[Mike Gagnon]

Mike200
One suggestion when posting here, there are delimiters to surround your code so it shows up correctly. In the tool bar above the message there is something called "Pre". Makes your code more readable.



If you want to get the best response to a question, please check out FAQ184-2483 first.