unusual query ... handling comparison in SQL

[amolik]

Hi All

after searching a lot for comparing two values in SQL I found a query in one forum which was unusual and also give error.
-------------------
select sum(case when sal < 0 then sal end) neg ,
sum(case when sal > 0 then sal end) pos
from emp
/
select sum(case when sal < 0 then sal end) neg ,
*
ERROR at line 1:
ORA-00907: missing right parenthesis
-------------------

I havent seen 'case' statement in simple SQL before and also 'when' and 'then'. Is there any new development in Oracle latest versions. I guess you all can enlighten on this. Also is there any function like above to compare values in Simple SQL.

Amol

[pando]

that's not new, you can use case since 8i

[amolik]

well,

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

Amol

[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.