wood burning stoves*
The moose likes Oracle/OAS and the fly likes Converting Negative value to zero Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Converting Negative value to zero" Watch "Converting Negative value to zero" New topic
Author

Converting Negative value to zero

Eshwar Prasad
Ranch Hand

Joined: Mar 21, 2008
Posts: 202

I have salary column in my employee table. I have a requirement that while calculating the sum of values in these column, all the negative amounts should be turned to zero.

For example.

SALARY
----------
100
-50
200
-10

Generally when we query as SELECT SUM(SALARY) FROM EMP we get output as 240, but i want to convert -50 and -10 to zero before totalling such that my sum is 100+0+200+0=300

Please let me know if this can be handled through query.

Eshwar Prasad
Ranch Hand

Joined: Mar 21, 2008
Posts: 202

Can anyone please help me on this.
Agador Paloi
Ranch Hand

Joined: Jan 24, 2006
Posts: 118
Eshwar -

Your query would look something like this:




Agad
Badal Chowdhary
Ranch Hand

Joined: Apr 26, 2011
Posts: 34

One more way is to use case statement. case statements are like if-elseif-else blocks. You could check for any condition and assign a value appropriately. Using decode is also an option for simpler condition checks.



Blog: http://badalchowdhary.wordpress.com/
Twitter: http://twitter.com/badalrocks
 
jQuery in Action, 2nd edition
 
subject: Converting Negative value to zero