SELECT NULL VALUES AS ZERO

[dansalmada]

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?

[lennart]

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

[Brian Tkatch]

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.