-
unusual query ... handling comparison in SQL
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
-
that's not new, you can use case since 8i
-
well,
then that's ok. I am using 8.0.5 . Is there any other method of comparing in 8.0.5 ????
Amol
-
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 ...
Code:
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|