This week's book giveaway is in the Kotlin forum.
We're giving away four copies of Kotlin in Action and have Dmitry Jemerov & Svetlana Isakova on-line!
See this thread for details.
Win a copy of Kotlin in Action this week in the Kotlin forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

A little SQL help with SUM()  RSS feed

 
Paul Duer
Ranch Hand
Posts: 98
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi guys, I have query with a sum() in it, on a DB2 7.0 box.
One of my sum() records returns a null because there are no values to add, but I want it to send back a zero so that it gets printed on a report.
I tired using CASE WHEN to catch the null and print the '0' but I get an erorr, any ideas?
 
Anurag Gupta
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In your Query you could use an equivalent of Decode in Oracle, if there is any in DB2.
 
Jeanne Boyarsky
author & internet detective
Sheriff
Posts: 37222
519
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul,
How are you getting a null? Are you using rs.getString(column)?
rs.getInt(column) returns 0 if the value is null and the expected value otherwise.
 
Braj Prasad
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try using the following case statement :
select col1, col2,
case
when sum(colName) is NULL then 0
elsesum(colName)
end as Total
from table.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!