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



This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].