Accessing Data in a Cursor 1

[shawnwmorris]

Newbie question: How do I access the data in VFP 9.0 from the following SQL?

SELECT SUM(reinsprem) AS CurrVal FROM reinstrn WHERE date >= DATE(2017,08,01) AND Policy Like PolNum AND code NOT like 'MBR1' INTO CURSOR A1

All I need to do is get the returned value of CurrVal in the cursor A1

Thanks so much for any help

 

[Tore Bleken]

[pre]lnCurvall = A1.CurrVal[/pre]

If this value is all you need, this approach is much easier:

[pre]
SELECT SUM(reinsprem) FROM reinstrn WHERE date >= DATE(2017,08,01) AND Policy Like PolNum AND code NOT like 'MBR1' INTO Array laCurVal
lnCurvall = laCurVal && You don't need this line, only used here for demonstration. Just call the array directly
*Since the array will only have one element, you don't need to specify any index.[/pre]

 

[shawnwmorris]

Okay so I've updated my SQL to look as follows:
SELECT SUM(reinsprem) FROM reinstrn WHERE date >= DATE(2017,08,01) AND Policy Like PolNum AND code NOT like 'MBR1' INTO Array laCurVal
and have tested with this line:
MESSAGEBOX(laCurVal + CHR(10) + NewVal)
The program fails there on the test line, I get an operator/operand type mismatch

 

[Tore Bleken]

Because, the value is numeric! Try [pre]MESSAGEBOX(Transform(laCurVal) + CHR(10) + NewVal)[/pre]

 

[Mike Lewis]

Hi Shawn,

The problem here is that your laCurVal is numeric, whereas CHR(10) is a character string. NewVal might also be numeric - that's not clear from your code. You can't add a numeric value to a character string - hence the error.

The solution is to convert laCurVal (and possibly NewVal) to character fields. You can do that by wrapping them in a TRANSFORM().

But keep in mind that this only affects your message box, which is only there for testing purposes. The error doesn't mean that there is anything wrong with your SQL.

You could also simply do a separate message box for laCurVal and NewVal respectively, without the CHR(10). That would avoid the error message you are seeing.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[shawnwmorris]

Great that worked!. Now when I use that number in an SQL statement to update another table will I have to use the transform function

 

[Tore Bleken]

Transform converts more or less any data type into text. Whether that's necessary or not in order to update, depends on the data type in the target table. If the target is numeric then no conversion is necessary.

 

[shawnwmorris]

Right that makes sense. Thanks guys for the help.

 

[Olaf Doschke]

Just notice a query INTO ARRAY only is simpler in case of having such a one value result. It could also be used for a whole result set with multiple rows and columns, but addressing a cursor normally is simpler, especially since a cursor actually is a local albeit temporary DBF, you have exclusive access to.

A cursor also is kept in memory like an array. A cursor crsResult has a filename DBF("crsResult"), but that is just virtual as long as the cursor memory doesn't outgrow allocated memory.

And the best advantage is you can do further queries on that cursor, which isn't possible with an array. You can also update the resulting cursor when you query INTO CURSOR crsResult READWRITE. Otherwise, it is a read-only result with one exception, which would be leading too far off for now. It won't happen with a calculated result value anyway.

So INTO CURSOR is the usual target for a query. An array has the advantage of random access of row/col, of course, but VFP won't create associated arrays as PHP does, which you could access with field names instead of array element index, but a cursor simply has the access like a record object with cursorname.fieldname - what could be simpler? The fieldname only? Yes, that is an option, too.

So finally, the simplest answer to your question is CurrVal.

Bye, Olaf.

 

[Mike Lewis]

Just to add to what Olaf posted .... Arrays are especially useful with queries that only return a single value. For example:

[tt]SELECT COUNT(*) FROM MyTable INTO ARRAY laResult[/tt]

This will result in an array containing just one cell (one row, one column). So you can retrieve the result by referencing [tt]laResult(1)[/tt], for example:
[tt]
MESSAGEBOX(laResult(1))[/tt]

I generally find that easier than using [tt]INTO CURSOR[/tt] in cases like that.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Tore Bleken]

Mike,

as I wrote earlier, for single row single column arrays, like the one returned from this Select statement, you don't need to specify the index. MESSAGEBOX(laResult() is enough.

 

[GriffMG]

You would probably want another right hand bracket though

B-)

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. ​

 

[shawnwmorris]

After reading all of the responses, I have decided to go with the Cursor Method that Olaf posted because it makes the most sense. There is only 1 value that I am pulling and there wouldn't be a need to an array. Thanks again to all who helped.

 

[Tore Bleken]

And that was also my very first suggestion.