Home » SQL & PL/SQL » SQL & PL/SQL » identify negative values (10g)
identify negative values [message #435029] Sun, 13 December 2009 22:16 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

Is there any way to identify negative values in a number columns.

I can actually do a substr to check if the first character is '-', but just thinking if there is any other better approach ..
Re: identify negative values [message #435031 is a reply to message #435029] Sun, 13 December 2009 22:26 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>Is there any way to identify negative values in a number columns.
WHERE COL1 < 0

>I can actually do a substr to check if the first character is '-'
NUMBER datatype never contains '-' character, CHR(29).
To use SUBSTR first you need to TO_CHAR(<number>)
do not mix datatypes. The "-" is a data presentation feature.

>but just thinking if there is any other better approach ..
Better, based on which metric & what value?
Re: identify negative values [message #435034 is a reply to message #435031] Sun, 13 December 2009 22:36 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
thank you ,

i will make use of the WHERE COL1 < 0 condition
Re: identify negative values [message #435045 is a reply to message #435029] Mon, 14 December 2009 00:16 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
I can actually do a substr to check if the first character is '-'


Can you show output of DESCRIBE statement for that table?

regards,
Delna
Re: identify negative values [message #435046 is a reply to message #435045] Mon, 14 December 2009 00:20 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
The object is a view, when i describe the underlying table, the column type is number
Re: identify negative values [message #435050 is a reply to message #435046] Mon, 14 December 2009 00:42 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
If data type of that column is number then it is quite simple to identify negative values from that table (And now you know how to do that).
No need to convert value of that column into CHAR type and checking for '-' in front of it.

regards,
Delna
Re: identify negative values [message #435053 is a reply to message #435029] Mon, 14 December 2009 00:56 Go to previous messageGo to next message
shettypravs
Messages: 9
Registered: August 2008
Junior Member
Use the oracle inbuilt SIGN function
Re: identify negative values [message #435062 is a reply to message #435053] Mon, 14 December 2009 01:55 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Hay ajitpal.s,

there are 2 aspects of your problem we are discussing..

1) you want to limit your resultset by applying filter on -ve values. ( AS BlackSwan said )
2) you want to apply some analycts on -ve values in your result set.

create table nve_test ( col1 number ,col2 number );

insert into nve_test (col1,col2) values (-1,1);
insert into nve_test (col1,col2) values (-2,2);
insert into nve_test (col1,col2) values (-3,3);
insert into nve_test (col1,col2) values (4,4);
insert into nve_test (col1,col2) values (5,5);
insert into nve_test (col1,col2) values (-6,-6);

commit;

following is resultsets
Frist Case..
select * from nve_test where col1 < 0;  -- only -ve values


      COL1       COL2
---------- ----------
        -1          1
        -2          2
        -3          3
        -6         -6


select * from nve_test where col1 > 0; -- only +ve values 


      COL1       COL2
---------- ----------
         4          4
         5          5

Second case

select col1, case when col1 < 0 then COL2 * -1 end Val from  nve_test;

      COL1        VAL
---------- ----------
        -1         -1
        -2         -2
        -3         -3
         4
         5
        -6          6

6 rows selected.



I hope this helps. Razz



Re: identify negative values [message #435067 is a reply to message #435029] Mon, 14 December 2009 02:54 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I was astounded by the fact that the original poster did not know how to test a number for being negative. If that reflects the level of his/her analytical capacities, software development might be a tad beyond the reachable.
But reading all the responses after BlackSwan pointed out the obvious simply made me drop my jar.
What's next? Explanation that the earth is round? Also true and also not particular relevant.
Previous Topic: PL/SQL any on help me 4 thissss
Next Topic: Writing File in PL/SQL (newline)
Goto Forum:
  


Current Time: Sun Sep 25 18:01:20 CDT 2016

Total time taken to generate the page: 0.07361 seconds