Replace zeros and nulls with 1 in table -- Using case, but not working

[Alex]

Hi folks,

I'm doing calculations based on data in a table, but the data has some
zeros in the field I'm dividing by. I'm trying to write a script to
replace any field with 0 or null with 1, but it's not working. HEre's
what I've got:

Update A Set A.deptcode = A.deptcode,
A.type = A.Type,
A.Volume = (case A.Volume
When Null Then 1
When 0 then 1
Else A.Volume
End)
From Data_Unsorted A Join Data_Unsorted B On
A.deptcode = B.deptcode and A.type = B.Type

My table is data_unsorted and deptcode and type are my primary keys
Volume is the item I want to put 1 if null or zero, and I'd thing the
above statement would work, but it doesn't. This table has 383 rows,
and it says it updates 383 rows, but when I run the following query to
test:

select a.deptcode, a.type, a.volume
from data_unsorted a
where a.AveMonthVolum e = 0 or a.AveMonthVOlum e is null

It didn't work... still TONS of nulls and zero's. Is there a trick to
this???

Thanks,

Alex.

[Shervin Shapourian]

Alex,

Try this:

update YourTable
set Col = 1
where Col = 0 or Col is null

Shervin
"Alex" <al**@totallyne rd.com> wrote in message
news:2b******** *************** **@posting.goog le.com...

Hi folks,

I'm doing calculations based on data in a table, but the data has some
zeros in the field I'm dividing by. I'm trying to write a script to
replace any field with 0 or null with 1, but it's not working. HEre's
what I've got:

Update A Set A.deptcode = A.deptcode,
A.type = A.Type,
A.Volume = (case A.Volume
When Null Then 1
When 0 then 1
Else A.Volume
End)
From Data_Unsorted A Join Data_Unsorted B On
A.deptcode = B.deptcode and A.type = B.Type

My table is data_unsorted and deptcode and type are my primary keys
Volume is the item I want to put 1 if null or zero, and I'd thing the
above statement would work, but it doesn't. This table has 383 rows,
and it says it updates 383 rows, but when I run the following query to
test:

select a.deptcode, a.type, a.volume
from data_unsorted a
where a.AveMonthVolum e = 0 or a.AveMonthVOlum e is null

It didn't work... still TONS of nulls and zero's. Is there a trick to
this???

Thanks,

Alex.

[Erland Sommarskog]

Alex (al**@totallyne rd.com) writes:

Update A Set A.deptcode = A.deptcode,
A.type = A.Type,
A.Volume = (case A.Volume
When Null Then 1
When 0 then 1
Else A.Volume
End)
From Data_Unsorted A Join Data_Unsorted B On
A.deptcode = B.deptcode and A.type = B.Type

You compare A.Volume to NULL, but NULL is never equal to NULL or
anything else. Write the CASE expresssion as.

CASE WHEN volume IS NULL THEN 1
WHEN volume = 0 THEN 1
ELSE volume
END

or

CASE coalesce(volume , 0) WHEN 0 THEN 1 ELSE volume END

The coalesce function returns the first non-NULL value in the list.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp