Putting the largest value from a field into a local variable 1

[AndrewMozley]

There is a table, XARCN.dbf with a field FOLIO, of the form XX9999.

I would like to extract the value of the largest ‘folio’ beginning ‘AB’ into a variable. Believe that I can extract it into a cursor with something like :

SELECT MAX(VAL(SUBSTR(FOLIO,3))) FROM XARCN WHERE LEFT(Folio,2) = "AB"

Is there a way of putting this value directly into a variable, without making use of a cursor?

Thank you.

 

[Nigel Gomm]

Andrew



select xarcn
calculate MAX(VAL(SUBSTR(FOLIO,3))) for LEFT(Folio,2) = "AB" to m.lnMaxVal

or

SELECT MAX(VAL(SUBSTR(FOLIO,3))) FROM XARCN WHERE LEFT(Folio,2) = "AB" into array lazy

2 caveats :
calculate will move the record pointer in your table xarcn
the array lazy will not be created if no records matched the where clause; so either preset it or check _TALLY afterwards.

hth

n

 

[AndrewMozley]

That looks good, Nigel. Xarcn is just in the data environment for this private data session, so I will use your first option.

 

[AndrewMozley]

I am not as good at following instructions as I thought !

Let us suppose that I have a table with ten records, and the Folio field contains :
NJ0001, SL0001, NJ0003, SC0001, NJ0004, NJ0005, SL0006, NJ0008, SL0003, PC0002

I want to find the largest folio number of a particular 2-character sequence. So I issue this instruction :

SELECT MAX(FOLIO) FROM XARCN WHERE LEFT(FOLIO,2) = "NJ" INTO ARRAY DOG

For sequence ‘NJ’ that returns (effectively) Dog[1,1] = "NJ0008", which is what I want. But for a sequence which does not exist :

SELECT MAX(FOLIO) FROM XARCN WHERE LEFT(FOLIO,2) = "PL" INTO ARRAY DOG

It certainly returns the array Dog[1,1]. But I am not sure how to detect that this is not a proper array. Do I need to test something like VARTYPE(LEN(DOG]) ?

Thanks again.

 

[Nigel Gomm]

SELECT MAX(VAL(SUBSTR(FOLIO,3))) FROM XARCN WHERE LEFT(Folio,2) = "PL" into array lazy
if _tally > 0
   m.lnMaxPL = nvl(lazy(1),0)
endif

or

local array lazy(1)
lazy(1) = 0
SELECT MAX(VAL(SUBSTR(FOLIO,3))) FROM XARCN WHERE LEFT(Folio,2) = "PL" into array lazy
m.lnMaxPL = nvl(lazy(1),0)

if nothing matches your scope the result could be null; NVL() will fix that for you


n

 

[Chris Miller]

I didn't test, but either you have a 1 length array with .f. or .null,
or the array isn't created and you can check with TYPE('DOG',1), which will b 'A', if the array exists.

The second parameter of TYPE() is newer, it might not be available with an older VFP version.
What you can always do is create the array before the query and set DOG[1]="", VFP will not touch this, if there is no result or overwrite it with the folio code.

And after the query, don't check DOB[1,1], just DOG, as if it was a normal single value variable, that always gets the first array element, no matter if 1d or 2d array.


Chriss

 

[Chris Miller]

To add to Nigel's answer, you can also check what _TALLY is. If it's 0, there was no result record.

What you could also do, to have an overview of all codes with

SELECT LEFT(folio,2) as foliogroup, MAX(folio) FROM XARCN group by 1


Chriss

 

[Mike Lewis]

What you can always do is create the array before the query and set DOG[1]=""

That would be my preferred solution (except that I would set it to 0 rather than a space, given that 0 is a valid answer to the query).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Mike, thanks for seconding it. But isn't the type of the folio field char? Andrew didn't extract the number from it.

The most general advice here is to set the array element to a vlue of the expected type, but never possible from the actual data.
So you never need a type or existance check and can still detect, that there really was no result.

In this case for example, you'd know ther is no folio for the 2 letter combination and so the next one would be 001.

Chriss

 

[Mike Lewis]

I don't think so, Chris. Isn't he getting[tt] MAX([highlight #FCE94F]VAL([/highlight]SUBSTR(FOLIO,3)[highlight #FCE94F])[/highlight])[/tt] ?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Mike, that was Nigels suggestion, look into Andrews post.

Chriss

 

[Mike Lewis]

Chris, I was looking at Andrew's very first post, which does contain the VAL(). But I see his third post is just extracting the character string.

Not to worry. No doubt he knows which he one he will end up with.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Nigel Gomm]

Mike, Chris

i just assumed max(folio) was a typo given the first post and put the val(substr(folio,3)) back in for my examples.

n