Gurwinder Singh Saini

• Feb 15th, 2007

 

pn is table having +ve and -ve values SQL> select * from pn ; NUM---------- 1 2 3 -1 -4 -56 rows selected.SQL> -----Seperate positive & negative valuseSQL> SELECT (CASE WHEN num<0 THEN num ELSE 0 END) neg, (CASE WHEN num>0 THEN num ELSE 0 END) pos FROM pn ; NEG POS---------- ---------- 0 1 0 2 0 3 -1 0 -4 0 -5 06 rows selected.

svj2000

 Profile Answers by svj2000 

• Mar 16th, 2007

 

table temp with a column num of type number

select

sum(case when num > 0 then num else null end) pos,

sum(case when num < 0 then num else null end) neg

from temp

Rina_S

 Profile Answers by Rina_S 

• Dec 24th, 2007

 

E.g. SELECT * FROM TAB1;

 COL1 C
----- -
   -1 A
   -2 A
    1 B
    2 B
SELECT DECODE(SIGN(COL1), -1, col1, null) A, DECODE(SIGN(COL1), 1, COL1, NULL) B FROM TAB1;

    A          B
----- ----------
   -1
   -2
               1
               2

daravipg

 Profile Answers by daravipg 

• Dec 7th, 2008

 

select sum(decode(sign(num), 1, num,0)) pos, sum(decode(sign(num), -1, num,0)) neg
from Table_name