It's not always about speed. If your expression is complex, using derived table can make query more readable/maintainable because you wouldn't have to repeat the expression multiple times.
>Of course, no problem will be created if the expression is evaluated twice. I was just thinking in terms of speed of execution. But all in all a few extra seconds does not matter much. And I am not sure the derived table will make the query executed faster. Although it is nice to know how to do it. Thank you very much.
>
>>I think VFP will evaluate it twice. I'm not sure about SQL Server. What problem does it create for you if expression is evaluated more than once?
>>Anyway, if you don't want it to be evaluated twice for sure, use derived table
>>
>>
>>
>>* VFP
>>*SELECT IIF(FLD1 - FLD2 < 0, 0, FLD1 - FLD2) as NEWCOLUMN from MyTable
>>SELECT IIF(days < 0, 0, days) as newcolumn ;
>> FROM ( SELECT fld1-fld2 AS days FROM MyTable) dt1
>>
>>
>>*SELECT CASE WHEN FLD1 - FLD2 < 0 THEN 0 ELSE FLD1 - FLD2 END as NEWCOLUMN from MyTable
>>SELECT (CASE WHEN days < 0 THEN 0 ELSE days END) as newcolumn
>> FROM ( SELECT fld1-fld2 AS days FROM MyTable) dt1
>>
>>
>>
>>>>
>>>
>>>Do you know or do you think that SQL Server (and VFP too) evaluate the expression FLD1 - FLD2 twice or the engines are smart enough to only do it once? I am just curious if I should get the cursor with just the Days difference column and then process this cursor in VFP for negative values.
--sb--