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.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham
