Changing NULL to zero

20 Jan 2006 8:49 PM

CL

Greetings folks,

I have a dilemma that I thought was going to be a slam dunk. I have a
table with several integer and money columns that contain about 50%
null values. I need to sum them, and have the nulls treated as zero.

Secondly, I need to insert all of the values into another table. If
there happens to be a null in the source columns, it needs to become a
zero in the destination. (I know someone is going to fuss about the
storing of calculated values. Not my choice, I don't get a say-so).

The tables have millions of records, and there are roughly 40 int and
money columns. So I'm looking for a solution that will perform well,
and will not require me to update those 40 columns individually. Any
suggestions?

thanks,

CL

20 Jan 2006 9:09 PM

Jim Underwood

--isnull will return the column if it is not null, and the second value (in
this case 0) if it is null
select isnull(Column1, 0) from table
or
--Coalesce returns the first non-null value in the list. I think if you
have only one value you want to use isnull, but here is is anyway...
select coalesce(column1,0) from table

Show quote

"CL" <clhawkin***@yahoo.com> wrote in message
news:1137790173.259283.202260@g49g2000cwa.googlegroups.com...
> Greetings folks,
>
> I have a dilemma that I thought was going to be a slam dunk. I have a
> table with several integer and money columns that contain about 50%
> null values. I need to sum them, and have the nulls treated as zero.
>
> Secondly, I need to insert all of the values into another table. If
> there happens to be a null in the source columns, it needs to become a
> zero in the destination. (I know someone is going to fuss about the
> storing of calculated values. Not my choice, I don't get a say-so).
>
> The tables have millions of records, and there are roughly 40 int and
> money columns. So I'm looking for a solution that will perform well,
> and will not require me to update those 40 columns individually. Any
> suggestions?
>
> thanks,
>
> CL
>