unusual query ... handling comparison in SQL

[slimdave]

Originally posted by amolik
well,

then that's ok. I am using 8.0.5 . Is there any other method of comparing in 8.0.5 ????

Amol

You can use DECODE(), and compare the SIGN() of "a minus b" to -1, 0, and 1 to perform comparisons of a less-than b, and a greater-then b etc., although the syntax is obviously a little clunky for this kind of operation.

To mimic the example you give using decode is easier, however, because the comparison is with zero. you could do ...

Select
   Sum(Least(sal,0)) neg ,
   Sum(Greatest(sal,0)) pos
from emp
/

It differs from the original where no records are found that meet the condition -- the original would return null, the substitute would return "0", but that shouldn't be much of a problem.