Using the MAX(a,b) function in a SQL query

[StewartUK]

I have found that using the function MAX(a,b) causes a "Function name is missing )" error, presumably because the SQL expects to see MAX(fieldname) AS NewField.

I've used IIF(a>b,a,b) to do the same thing, but I would assume MAX(a,b) would be faster. Is there a way to use the MAX(a,b) function in a SQL query?

Thanks,

Stewart

 

[Mike Lewis]

Stewart,

I think the problem is that there are two MAX() functions in VFP: the native function, which is the one you were using; and the SQL version, which only takes one argument. I would guess that using the native function in a context where the SQL version is also allowed will confuse VFP slightly.

I would go for the IIF(). It might be very slightly slower, but unless your table is really ginormous, I doubt it will be significant.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[Mike Lewis]

Stewart,

Just to add to my previous post:

If you really want to use MAX(a,b), you could write a wrapper function which calls it. Just write a function which receives two parameters and passes those directly to MAX(), and which returns the result from MAX().

That way, you won't get the confusion between the two versions of MAX(). But the overhead of an extra function call might wipe out any gain in performance over IIF().

Might be worth experimenting.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[StewartUK]

Thanks Mike,

I started experimenting with a function but gave up pretty fast! I'll stick with the IIF - I guess I can see that VFP wouldn't really be able to know which I meant!

Stewart