RE: SQL to determine if two numeric columns have the same sign.

[lloen@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx]

> There's the obvious..
>
> select
>   case
>      when Fld1 <= 0 and Fld2 <= 0 then 'Yes'
>      when Fld1 >= 0 and Fld2 >= 0 then 'Yes'
>      else 'No'
>   end as Same_Sign
> from myFile

There is a time and place for being obvious.  It is likely that these
sorts of things have had a lot more attention paid to them by the
optimizer than the other solutions in this thread.

> And the slightly tricky  (Though I haven't quite convinced  myself
> that it'd work :)
> select
>   case
>      when abs(fld1) + abs(fld2) = abs(fld1 + fld2) then 'Yes'
>      else 'No'
>   end as Same_Sign
> from myFile

This one could have a lot of problems, depending.  What about overflow
and underflow in the sum on the right hand side?  What about the
potential for type conversions if fld1 and fld2 aren't the same type? 
If we're talking about 80M files, we are also talking about room for
some edge conditions to show up.  Note that the obvious solution does
not even raise these questions except for whacky stuff like NaN if fld1
or fld2 are float or double (but if you're worried about that, you're
even more worried about the above abs(fld1 + fld2) expression).

I think the expression idea is too tricky by half and it probably runs
slower than the obvious solution to boot, though probably not by much.

I would think of it this way:  Most of the cost of the statement is
likely to be fetching fld1 and fld2.  The math should be barely register
as a cost.  Why not be obvious?  What is gained by the trickery?



Larry Loen
www.applicationperformancegroup.com