First record from a select statement 2

[androidc]

After reading thread184-635869, it reminded me of something i noted once. The following produces strange (well, i think it's strange) results

CREATE CURSOR blah ( name c(10) )
FOR i = 1 TO 10
INSERT INTO blah VALUES ("blah" + ALLT(STR(i)))
NEXT

_num = 0

SELECT name, nextnum() FROM blah


FUNCTION nextnum
_num = _num + 1
RETURN _num


The first record in the query has 2, when, as best i can figure, it should be 1. The range should be 1 to 10, but instead we see 2 to 11. The first record is calculated twice, but only one record?

If this is normal, what is the reason for this? I've had to code around it once, not really a problem, but i'm somewhat curious..

Thanks

 

[craigsboyd]

androidc,

Interesting to be sure. According to debug stepping it calls the nextnum function once at the beginning before beginning to create the resultant dataset. Almost like VFP is doing a trial run to make sure the function can be called and/or what the return value will be for the function. Guess you'd have to initialize the _num variable to -1 to make sure you got the expected results. Strange to be sure.

Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[rgbean]

Yes, VFP (and FoxPro before it), needs to know the column type and width for each column in the cursor/table. So it executes any UDF() or in-line calculation to determine these values. Then once the cursor/table columns are defined, it does the SELECT. That's one reason you don't want to use an expression like ALLTRIM(Lastname) as LName, if your first person is "An" - the field for all names will only be two characters wide!

Rick

 

[jimoo]

You can trick the UDF by adding a plFirstTime Variable to handle the creating of the cursor. This example is very raw but will give the idea:

CREATE CURSOR blah ( name c(10) )
FOR i = 1 TO 10
INSERT INTO blah VALUES ("blah" + ALLT(STR(i)))
NEXT

_num = 0
plFirstTime = .T.
SELECT name, nextnum() FROM blah


FUNCTION nextnum
IF plFirstTime = .T.
plFirstTime = .F.
RETURN 01
ELSE
_num = _num + 1
RETURN _num
ENDIF


Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com