Is there a way to use the MAX function in a SQL statement (as opposed to the documented MAX "field function" that calculates the largest value in a column). To be more specific, for any particular record, I need to multiply the value in a field by the value in another field. However, the value in the first field will sometimes be negative 1 and in that case I don't want to multiply negative one by the value in the other field, I want to multiply 0 by the value in the other field.
For example, here's just the straight multiplication within in the SQL statement:
select round(QtyFld * PriceFld, 2) as TotalPriceFld;
from invoice;
into cursor TestCursor
But if I change QtyFld to max(0, QtyFld) then I get an error during compile that says "function is missing ')'". VFP seems to be thinking I want to use the MAX "field function." Here's the code that doesn't work.
select round(max(0, QtyFld) * PriceFld, 2) as TotalPriceFld;
from invoice;
into cursor TestCursor
There are other approaches, but I was wondering how you might use MAX as detailed here. Is there a way to make VFP know "what I really meant?"
Thanks,
Russell Campbell