CODEWITHSUNDEEP
sqloracle-database
Pravin Satav
Pravin Satav

Reputation: 702

Positive value to negative and negative to positive in oracle

I have a requirement to convert postive value to negative and negative to positive and if its 0 then leave as it is. Im able to do it in sql, just need to know if there is any better way/alternate way to do it?

create table test_tab 
(a number);

insert into test_tab values (10);
insert into test_tab values (-10);
insert into test_tab values (0);
insert into test_tab values (10.15);
insert into test_tab values (-10.15);

select a , decode(sign(a),-1,abs(a),1,-abs(a),0) "changed value" from test_tab;

Database Oracle - 11g

Upvotes: 8

Views: 55119

Answers (4)

Dalton Bohning
Dalton Bohning

Reputation: 53

How about using subtraction:

SELECT 0 - a "changed value"
from test_tab;

Upvotes: 5

vasin1987
vasin1987

Reputation: 2012

select a , decode(sign(a),-1,abs(a),1,-abs(a),0) -a from test_tab;

that's all you need

Upvotes: 1

vhadalgi
vhadalgi

Reputation: 7189

how about just putting a negative sign

SELECT - -15 as inverse;

-->15

SELECT  -15 as inverse;

-->-15

Upvotes: 8

Alen Oblak
Alen Oblak

Reputation: 3325

What about multiplying with -1 ?

select a
,      -1 * a "changed value"
from   test_tab;

Upvotes: 21

Related Questions