470,579 Members | 2,192 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,579 developers. It's quick & easy.

SELECT NULL VALUES AS ZERO

Hi

I'm using DB2 and I'm trying to pull information from a table.

I've tried every function possible (IFNULL,COALESCE,CASE) to get a zero
every time the column is NULL.

The thing is I don't know if they are actually nulls or if they are
something like ' '

How can I select blank columns as zero?

Jan 24 '06 #1
2 31271

da********@gmail.com wrote:
Hi

I'm using DB2 and I'm trying to pull information from a table.

I've tried every function possible (IFNULL,COALESCE,CASE) to get a zero
every time the column is NULL.

The thing is I don't know if they are actually nulls or if they are
something like ' '

How can I select blank columns as zero?


Not sure what you are trying to do. Does this come close?

coalesce(nullif(val,''),'0')

Note that "zero" has to be char instead of int. Example:

db2 "with t (val) as (values (' '),(' a '),('b ')) select
coalesce(nullif(val,''),'0') from t"
1
---
0
a
b

HTH
/Lennart

Jan 24 '06 #2
IFNULL is probably not what you want here.

COALESCE retrieves the first NOT NULL value in its list. So, for
example, if you have a TABLE that includes amounts and NULLs, and want
NULLs to be represented by zeros:

SELECT COALESCE(amount, 0) FROM table

CASE can be used, but it may not be needed.

If COALESCE did not work for you, please provide the SQL statement
used, so we can take a better look.

B.

Jan 24 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Joshua Ruppert | last post: by
1 post views Thread by Marcus | last post: by
9 posts views Thread by madsgormlarsen | last post: by
2 posts views Thread by C. Lo | last post: by
8 posts views Thread by manning_news | last post: by
3 posts views Thread by Alexander Pucher | last post: by
10 posts views Thread by Toby Gallier | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.