Using decode to check for negative and positive values

Question

Hi is there a way to use decode to check for positive and negative values in sql?

e.g.

select decode(money_return, **<0**, abs(money_return), **>0**, 
money_return*10, money_return) from cash_t;

if the logic is coded in if else statement it will be something like this:

if(money_return<0){money_reutrn = abs(money_return);}
else if(money_return>0){money_reutrn = money_return*10;}
else {money_return = money_return;}
end

Thank you.

Answer 1

You can use 'sign' with a decode. Sign will return -1 for any negative, 0 for 0, and 1 for any positive:

select decode(sign(money_return), -1, abs(money_return), 
                                   1, money_return*10, 
                                   money_return) 
from cash_t;

Answer 2

You need case statement

select CASE 
 WHEN money_return < 0 THEN abs(money_return)
 WHEN money_return > 0 THEN money_return*10
 ELSE money_return END money_return from cash_t;

Answer 3

Use SIGN(). It returns "0" for 0, "1" for positive and "-1" for negative values.

SELECT
    DECODE(
        SIGN(money_return),
        0,
        money_return,
        1,
        money_return * 10,
        - 1,
        ABS(money_return)
    )
FROM
    CASH_T;