×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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

SQL Select from 2 values of cursor

SQL Select from 2 values of cursor

SQL Select from 2 values of cursor

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

RE: SQL Select from 2 values of cursor

Hi,
You might try something like below

CODE -->

LOCAL lcunion, lcclassid
LOCAL ARRAY laRate[1]

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

SCAN

lcunion = unionid
lcclassid = classid 

SELECT Payrate FROM unclass WHERE Unionid = m.lcunion AND id = m.lcclassid into array laRate


IF VARTYPE(laRate[1]) = "N"

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 laRate[1]

ELSE
= MessageBox("Error in laRate",16)
ENDIF

ENDSCAN
 

And of coure you'll have to delete the ALL parameter

hth
MarK

RE: SQL Select from 2 values of cursor

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 Smileing

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.

RE: SQL Select from 2 values of cursor

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

RE: SQL Select from 2 values of cursor

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: How to get the best response from the forum first.

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