OraFAQ Forum: SQL & PL/SQL » Selecting 0 instead of negative value

Home » SQL & PL/SQL » SQL & PL/SQL » Selecting 0 instead of negative value (Oracle 10g 10.1.0.4.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Selecting 0 instead of negative value [message #441678]

Tue, 02 February 2010 10:36

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!!

Report message to a moderator

Re: Selecting 0 instead of negative value [message #441681 is a reply to message #441678]

Tue, 02 February 2010 10:46

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

Frank
Messages: 7901
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

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

case when number < 0 then 0
     else number
     end

Report message to a moderator

Re: Selecting 0 instead of negative value [message #441799 is a reply to message #441798]

Wed, 03 February 2010 02:54

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

(number + abs(number))/2

Report message to a moderator

Re: Selecting 0 instead of negative value [message #441860 is a reply to message #441799]

Wed, 03 February 2010 08:01

joy_division
Messages: 4963
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)

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 18 12:52:04 CDT 2025