Home » SQL & PL/SQL » SQL & PL/SQL » Selecting 0 instead of negative value (Oracle 10g 10.1.0.4.2)
Selecting 0 instead of negative value [message #441678] Tue, 02 February 2010 10:36 Go to next message
mike1976
Messages: 26
Registered: June 2005
Location: Happaugue, NY
Junior Member
This should be an easy one....

I was wondering if there was a way to select a number; and if it was negative(<0), if the select statement would just return 0 instead of a negative number.


I know you can use NVL to translate a null value into a non null value as specified by NVL.

I'm hoping there is a similar function i can use to acomplish this with negative numbers.

Thank you!!
Re: Selecting 0 instead of negative value [message #441681 is a reply to message #441678] Tue, 02 February 2010 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
decode(sign(mynumber),-1,0,mynumber)


This is one of many ways you can do it.

Regards
Michel

[Updated on: Wed, 03 February 2010 08:08]

Report message to a moderator

Re: Selecting 0 instead of negative value [message #441741 is a reply to message #441678] Wed, 03 February 2010 00:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
greatest(0, your_number)

[Updated on: Wed, 03 February 2010 08:08] by Moderator

Report message to a moderator

Re: Selecting 0 instead of negative value [message #441798 is a reply to message #441741] Wed, 03 February 2010 02:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
case when number < 0 then 0
     else number
     end
Re: Selecting 0 instead of negative value [message #441799 is a reply to message #441798] Wed, 03 February 2010 02:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
(number + abs(number))/2
Re: Selecting 0 instead of negative value [message #441860 is a reply to message #441799] Wed, 03 February 2010 08:01 Go to previous message
joy_division
Messages: 4616
Registered: February 2005
Location: East Coast USA
Senior Member
ok, since we are coming up with all different ways, I'll throw in a very inefficient and silly way to do it (only because I want to add one too).
decode(substr(to_char(mynumber),1,1),'-',0,mynumber)
Previous Topic: ORA-01792: maximum number of columns in a table or view is 1000 (merged again by CM)
Next Topic: Error multiple 1PC XAResources
Goto Forum:
  


Current Time: Mon Sep 26 11:11:11 CDT 2016

Total time taken to generate the page: 0.29365 seconds