Setting values for parameters in a select statement

[WCL01]

I'm trying to duplicate some code I currently run in sql into foxpro. I currently declare some variables in sql and within a select statement set those variables to a value.

example:
select @curprog = prog_id,@curservcat = serv_cat, @curconno = [5yr_conno],
@cur1yrstartdt = [1yr_startdt], @cur1yrenddt = [1yr_enddt]
from #test9
where id1 = @curid

I would like to try and duplicate this same thing within foxpro

I have already declared my variable, just not sure how to do something similar in foxpro as I'm pretty new to the language. Any help would be greatly appreciated.

 

[Mike Lewis]

Have you already got the Test9 table? (Note that you don't use # in front of temp. tables in VFP.)

If so, you could do this:

lcCurID = 123  && put the actual ID here
SELECT test9
LOCATE FOR lcCurID
curprog = prog_id
curservcat = serv_cat
curconno = x5yr_conno
cur1yrstartdt = x1yr_startdt
cur1yrenddt = x1yr_enddt

A few points:

- Column names can't begin with a digit (1 or 5). I've changed them by putting an "x" in front of their names.

- Variables don't have the preceding "@"

- Variables don't have to be declared in advance.

Basically, what we are doing here is obtaining the row from Test9 with the required ID, then assigning each of the relevant field values to the appropriate variables.

Hope this makes sense.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[WCL01]

It does help a little Mike, but I'm still a bit unclear on a few things.

the table I'm using does exist and is called era_proj_actuals within my foxpro database.

You say to put in

curid = 123 (put in an actual id here)
Select era_proj_actuals
locate for curid
curprog = prog_id
curservcat = serv_cat
curconno = x5yr_conno
cur1yrstartdt = x1yr_startdt
cur1yrenddt = x1yr_enddt

now I kind of understand this but how does it know what field to locate to select the particular row that has the curid value in it. In your example you set curid = 123 then say to select the table and locate for curid but not what field within that table to look for that curid value. Thanks again for the help.

 

[clipper01]

use the superb vfp text merge features , everything in << >> is just substituted eg fields , variables , expressions ; build up WHERE clauses etc

TEXT to lcSql pretext 15 noshow textmerge
select <<curprog>> as prog_id,<<curservcat>> as serv_cat, <<curconno>> as 5yr_conno,
<<cur1yrstartdt>> as 1yr_startdt , <<cur1yrenddt>> as 1yr_enddt
from #test9 where id1 = <<curid>>
endtext


then just use lcSql as your sql select statement , belive me , there is nothing as efficient in this in nay of the dotNet stuff

 

[Olaf Doschke]

Mike has forgotten to specify what to search for:

LOCAT FOR ID1 = curid

would locate the record, which has curid as the value of the field ID1, if the field name is id. You can't simply LOCATE FOR curid. Instead you could have an index on the primary key field, SET ORDER TO that index tag name and then SEEK curid.

That's perhaps what Mike mixed somehow, SEEK and LOCATE syntax.

But the general idea is, if you have a table open, you don't even need variables, you can adress the table fields themeselves via the field names or via tablename.fieldname.

The syntax without the tablename only works on the currently selected workarea, the syntax with fieldname also works, if that alias is not the current workarea. But both syntax(es?) need the table being opened.

And as there are many ways to do the same thing: What would resemble the syntax you know from sql server would also be:

curid= 123

select prog_id, serv_cat, x5yr_conno,x1yr_startdt, x1yr_enddt
from yourtable INTO Cursor curResult where id1 = m.curid

And then you can adress the fields as said, eg as curResult.prog_id

OR

select prog_id, serv_cat, x5yr_conno,x1yr_startdt, x1yr_enddt
from yourtable INTO Array arrResult where id1 = m.curid

Which puts the fields into an array arrResult[1,1] would be prog_id of the first result row.

The cursor options is the most used for SQLs in Foxpro. Don't be afraid, cursors in vfp don't have the disadvatages of cursors in sql server, they are not the exact same thing. Actually a foxpro cursor is a table, just a local table. Even an in-memory table, unless it get's too large for RAM, only then it's written to the temp dir.

So cursors are really the work horses of fopro data processing, not arrays or variables.

Bye, Olaf.

 

[Mike Lewis]

WCL01,

Yes, Olaf spotted my mistake.

I should have said:

LOCATE FOR ID1 = curid

That should work now.

VFP supports the concept of a "record pointer", which is not something you find in SQL. The record pointer points to the "current row" of a table. My LOCATE statement moves the record pointer to the row that has the specified ID.

Once you have established the current row as the one you are interested in, you can directly address its fields. In particular, you can store the values of the fields into variables, which is what my code is doing.

As Olaf suggested, there are other ways of moving the record pointer, including SEEK and SEEK(), but I wanted to keep it simple.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy