Geek Answers Handbook

Using decoding to check negative and positive values

Hi, is there a way to use decoding to check positive and negative values ​​in sql?

eg.

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

if the logic is encoded in if else, 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

Thanks.

+7
source share
8 answers

You need a 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;
+7
source

You can use the "sign" with decoding. The sign will return -1 for any negative value, 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;
+6
source

You can use like this,

 SELECT supplier_name, decode(supplier_id, 10000, 'IBM', 10001, 'Microsoft', 10002, 'Hewlett Packard', 'Gateway') result FROM suppliers;

The above decrypted statement is equivalent to the following IF-THEN-ELSE statement:

 IF supplier_id = 10000 THEN result := 'IBM'; ELSIF supplier_id = 10001 THEN result := 'Microsoft'; ELSIF supplier_id = 10002 THEN result := 'Hewlett Packard'; ELSE result := 'Gateway'; END IF;

So your final code should be something like this

 select decode(money_return, money_return<0, abs(money_return), money_return>0, money_return*10, money_return) result from cash_t;
+1
source

I use the "largest" and "smallest" functions found positive values ​​or negative value.

Example.

select decoding (highest (value, 0), 0, Negative, Positive) from double

or

select decoding (smallest (value, 0), 0, "Positive", "Negative") from double

thanks

+1
source

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;
+1
source 
 select CASE money_return 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;
0
source

If this is the only formula you use, you can do:

 select (11 *abs(money_return) + 9 *money_return)/2 as money_return from cash_t

SQL Fiddle to check the results.

Now it is very, very fragile and will not work if the scenario changes.

0
source 
 SELECT decode( money_return - Abs(money_return), 0, money_return * 10, abs(money_return) ) FROM cash_t;

If the value is positive, subtracting from it will return 0 and the first condition will be applied. Otherwise, this is negative, and the second condition applies. 0 It fits into the first condition and returns 0 (as 0 * 10).

0
source

More articles:


All Articles