select round(CASE QtyFld < 0 THEN 0 ELSE QtyFld END * PriceFld, 2) as TotalPriceFld
from invoice
(not tested, but should works)
>Hi Sergey
>
>What would one use in T-SQL to do as he asked?
>
>>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
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.