SELECT NULL VALUES AS ZERO

[dansalmada@gmail.com]

Hi

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

I've tried every function possible (IFNULL,COALESC E,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?

[lennart@kommunicera.umea.se]

Re: SELECT NULL VALUES AS ZERO


dansalmada@gmai l.com wrote:[color=blue]
> Hi
>
> I'm using DB2 and I'm trying to pull information from a table.
>
> I've tried every function possible (IFNULL,COALESC E,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?[/color]

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

[Brian Tkatch]

Re: SELECT NULL VALUES AS ZERO

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.