unusual query ... handling comparison in SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: unusual query ... handling comparison in SQL

  1. #1
    Join Date
    Dec 2000
    Posts
    255

    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

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    that's not new, you can use case since 8i

  3. #3
    Join Date
    Dec 2000
    Posts
    255
    well,

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

    Amol

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width