Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Problem with MAX function in SQL statement
Message
General information
Forum:
Visual FoxPro
Category:
Coding, syntax & commands
Environment versions
Visual FoxPro:
VFP 9
OS:
Windows XP SP2
Database:
Visual FoxPro
Miscellaneous
Thread ID:
01065034
Message ID:
01065042
Views:
18
No you cannot use VFP MAX() function in SELECT. You can emulate it with IIF() or ICASE().

>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
--sb--
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform