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

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.AveMonthVolume = 0 or a.AveMonthVOlume is null

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

Thanks,

Alex.Alex,

Try this:

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

Shervin

"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0310091122.fc83cd5@.posting.google.co m...
> 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.AveMonthVolume = 0 or a.AveMonthVOlume is null
> It didn't work... still TONS of nulls and zero's. Is there a trick to
> this?
> Thanks,
> Alex.|||Alex (alex@.totallynerd.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, sommar@.algonet.se

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