×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
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
midrange.com
