Home » RDBMS Server » Performance Tuning » how to avoid negative values to number datatype in oracle (Oracle 11g)
how to avoid negative values to number datatype in oracle [message #603015] Wed, 11 December 2013 00:28 Go to next message
na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore
Member

Hi

how to avoid negative values to number datatype while inserting into number data type.

SQL> drop table sam_800
  2  /
drop table sam_800
           *
ERROR at line 1:
ORA-00942: table or view does not exist


Elapsed: 00:00:00.36
SQL> create table sam_800(a number(5,2)) tablespace warehouse_small_data;

Table created.

Elapsed: 00:00:00.18
SQL> insert into sam_800 values(100.00);

1 row created.

Elapsed: 00:00:00.18
SQL> insert into sam_800 values(-100.00);

1 row created.

Elapsed: 00:00:00.18
SQL> insert into sam_800 values(-100.585);

1 row created.

Elapsed: 00:00:00.18
SQL> select * from sam_800;

         A
----------
       100
      -100
   -100.59

Elapsed: 00:00:00.36
Re: how to avoid negative values to number datatype in oracle [message #603022 is a reply to message #603015] Wed, 11 December 2013 00:50 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
What do you want to do with the negative values? If you want to reject the INSERT, you could use a check constraint. Or you could make them positive them with MOD(). Or you could use CASE to convert them to anything you like.
Re: how to avoid negative values to number datatype in oracle [message #603023 is a reply to message #603015] Wed, 11 December 2013 00:50 Go to previous messageGo to next message
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
Senior Member
My initial reaction is...."just don't insert negative numbers"

My second reaction would be add a (check) constraint to the table.
Have a look here for ome examples.
Re: how to avoid negative values to number datatype in oracle [message #603024 is a reply to message #603022] Wed, 11 December 2013 01:07 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
John Watson wrote on Wed, 11 December 2013 07:50
you could make them positive them with MOD()

MOD? Why not ABS?
Re: how to avoid negative values to number datatype in oracle [message #603027 is a reply to message #603024] Wed, 11 December 2013 01:11 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Littlefoot wrote on Wed, 11 December 2013 07:07
John Watson wrote on Wed, 11 December 2013 07:50
you could make them positive them with MOD()

MOD? Why not ABS?
Why not? Because I'm a rubbish programmer!
Re: how to avoid negative values to number datatype in oracle [message #603028 is a reply to message #603027] Wed, 11 December 2013 01:21 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm afraid I would't agree with that statement.
Re: how to avoid negative values to number datatype in oracle [message #603029 is a reply to message #603028] Wed, 11 December 2013 01:23 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Littlefoot wrote on Wed, 11 December 2013 07:21
I'm afraid I would't agree with that statement.
You will after seeing my next two solutions:
orclz> select case sign(a) when -1 then -1*a else a end from sam_800;

CASESIGN(A)WHEN-1THEN-1*AELSEAEND
---------------------------------
                              100
                              100
                           100.59

orclz> select sqrt(a*a) from sam_800;

 SQRT(A*A)
----------
       100
       100
    100.59

orclz>


Re: how to avoid negative values to number datatype in oracle [message #603030 is a reply to message #603029] Wed, 11 December 2013 01:37 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Not THAT perfect, but - match this!
SQL> WITH test AS (SELECT 100 col FROM DUAL
  2                UNION
  3                SELECT -200 FROM DUAL
  4                UNION
  5                SELECT -0.343 FROM DUAL)
  6  SELECT col,
  7         DECODE (SUBSTR (TO_CHAR (col), 1, 1),
  8                 '-', to_number(SUBSTR (TO_CHAR (col), 2)),
  9                 col)
 10            result
 11    FROM test;

       COL     RESULT
---------- ----------
      -200        200
     -,343       ,343
       100        100

SQL>
Re: how to avoid negative values to number datatype in oracle [message #603031 is a reply to message #603030] Wed, 11 December 2013 01:50 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Cool! I'm still doing arithmetic:
orclz> select case when a+a > a then a else -1*a end from sam_800;

CASEWHENA+A>ATHENAELSE-1*AEND
-----------------------------
                          100
                          100
                       100.59

orclz>

Re: how to avoid negative values to number datatype in oracle [message #603034 is a reply to message #603031] Wed, 11 December 2013 02:31 Go to previous messageGo to next message
na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore
Member

All the solutions given, how to avoid negative values while fetching from table

My question was how to avoid inserting negative values into the tables
Re: how to avoid negative values to number datatype in oracle [message #603037 is a reply to message #603034] Wed, 11 December 2013 02:44 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You have not said what you want to do with any negatives.
If you want to prevent the insert, martijn has given you the answer.
If you want adjust the data, apply any of the solutions given in your INSERT statement.

Re: how to avoid negative values to number datatype in oracle [message #603061 is a reply to message #603031] Wed, 11 December 2013 04:18 Go to previous messageGo to next message
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
Senior Member
okay...I had a few minutes to spare :
SQL> with tbl as 
       (select 100 a from dual 
        union all 
        select -100 from dual
        union all
        select 0.5 from dual
        union all
        select -0.5 from dual)
      select a, case when exp(a)<1 then -1*a else a end from tbl;

         A CASEWHENEXP(A)<1THEN-1*AELSEAEND
---------- --------------------------------
       100                              100
      -100                              100
        .5                               .5
       -.5                               .5

SQL>
Re: how to avoid negative values to number datatype in oracle [message #603065 is a reply to message #603061] Wed, 11 December 2013 04:32 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
And yet another ludicrous technique!
orclz> select a,to_number(replace(to_char(a),'-')) from sam_800;

         A TO_NUMBER(REPLACE(TO_CHAR(A),'-'))
---------- ----------------------------------
       100                                100
      -100                                100
   -100.59                             100.59

orclz>

Re: how to avoid negative values to number datatype in oracle [message #603069 is a reply to message #603065] Wed, 11 December 2013 04:48 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
If it is only to get rid of the "-" sign, then why not LTRIM?

SQL> with tbl as
  2         (select 100 a from dual
  3          union all
  4          select -100 from dual
  5          union all
  6          select 0.5 from dual
  7          union all
  8          select -0.5 from dual)
  9  SELECT LTRIM(a,'-') FROM tbl;
 
LTRIM(A,'-')
----------------------------------------
100
100
.5
.5
Previous Topic: look like same query, but huge query plan different.
Next Topic: how avoid merge join cartesian.
Goto Forum:
  


Current Time: Thu Mar 28 15:30:56 CDT 2024