OraFAQ Forum: SQL & PL/SQL » Display zero if negative value.

Home » SQL & PL/SQL » SQL & PL/SQL » Display zero if negative value. (Oracle 8i, Windows Server 2003)

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

Display zero if negative value. [message #538709]

Mon, 09 January 2012 02:17

kumarvk
Messages: 214
Registered: July 2004

Senior Member

Hi Experts,

I am doing a Gross Profit percentage. I want to display zero if the GP% is have negative value.

for example

If GP% shows -75 I want to display as 0.

trunc((sum(total)-(sum(DECODE(po_pur,'',0,PO_PUR))+sum(DECODE(pl_pur,'',0,PL_PUR))+SUM(parts))) / SUM(DECODE(TOTAL,0,1,TOTAL))*100,2) "GP%"

Report message to a moderator

Re: Display zero if negative value. [message #538710 is a reply to message #538709]

Mon, 09 January 2012 02:19

Littlefoot
Messages: 21761
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Try CASE (I hope it existed in 8i ... if not, DECODE and SIGN functions will do that). Something like this:

select case when gp < 0 then 0 
            else gp
       end
from ...

select decode(sign(gp), -1, 0, gp)
from ...

Report message to a moderator

Re: Display zero if negative value. [message #538712 is a reply to message #538710]

Mon, 09 January 2012 02:22

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Another way:

greatest(gp,0)

Regards
Michel

Report message to a moderator

Re: Display zero if negative value. [message #684106 is a reply to message #538712]

Wed, 07 April 2021 04:49

Udays
Messages: 1
Registered: April 2021

Junior Member

Genius way it will simply avoid double calculation of expression

Report message to a moderator

Previous Topic:	Oracle Sql statements
Next Topic:	Mark Only Selected Portion of a Column Data

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 01 08:20:29 CDT 2023