Home » SQL & PL/SQL » SQL & PL/SQL » Display zero if negative value. (Oracle 8i, Windows Server 2003)
Display zero if negative value. [message #538709] Mon, 09 January 2012 02:17 Go to next message
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%"

Re: Display zero if negative value. [message #538710 is a reply to message #538709] Mon, 09 January 2012 02:19 Go to previous messageGo to next message
Littlefoot
Messages: 21678
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 ...

or
select decode(sign(gp), -1, 0, gp)
from ...
Re: Display zero if negative value. [message #538712 is a reply to message #538710] Mon, 09 January 2012 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 67887
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another way:
greatest(gp,0)


Regards
Michel
Re: Display zero if negative value. [message #684106 is a reply to message #538712] Wed, 07 April 2021 04:49 Go to previous message
Udays
Messages: 1
Registered: April 2021
Junior Member
Genius way it will simply avoid double calculation of expression
Previous Topic: Oracle Sql statements
Next Topic: Mark Only Selected Portion of a Column Data
Goto Forum:
  


Current Time: Mon Jun 21 23:57:35 CDT 2021