Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Positive value to negative and negative to positive in oracle

Tags:

sql

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

like image 819
Pravin Satav Avatar asked Nov 22 '13 07:11

Pravin Satav


People also ask

How do I make a negative number positive in Oracle?

In Oracle, use ABS function to change negative value to positive value. This function takes as an argument any numeric data type and returns the absolute value.

How do I change a positive number to a negative in SQL?

As Tyson said, just multiply by -1. positive values become negative ( 1 * -1 = -1). negative values become positive (-1 * -1 = 1).

What is CHR 10 in Oracle?

CHR(10) is used to insert line breaks, CHR(9) is for tabs, and CHR(13) is for carriage returns.

What is the (+) in Oracle SQL?

The plus sign is Oracle syntax for an outer join. There isn't a minus operator for joins. An outer join means return all rows from one table. Also return the rows from the outer joined where there's a match on the join key. If there's no matching row, return null.


2 Answers

What about multiplying with -1 ?

select a
,      -1 * a "changed value"
from   test_tab;
like image 95
Alen Oblak Avatar answered Sep 29 '22 18:09

Alen Oblak


how about just putting a negative sign

SELECT - -15 as inverse;

-->15

SELECT  -15 as inverse;

-->-15
like image 32
vhadalgi Avatar answered Sep 29 '22 18:09

vhadalgi