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

REPLACE Arguments

Hi,

I'm trying to create a view that shows columns based on user access level. I would like to be able to test for different values without having to add a REPLACE command for each value.

For example,

My UserLevelID can be 1, 2, 3, 4 or 5. 5 is an Officer level so I have the following command:

REPLACE(m.UserLevelID, 5, 'Yes'') AS 'Officer',

The problem is that I want the field to be blank for this column if the value is 1, 2, 3 or 4. I've tried the following, but none work.

REPLACE(REPLACE(m.UserLevelID, 5, 'Yes''), IN(1,2,3,4), '') AS 'Officer'

REPLACE(REPLACE(m.UserLevelID, 5, 'Yes''), NOT 5, '') AS 'Officer'

REPLACE(REPLACE(m.UserLevelID, 5, 'Yes''), < 5, '') AS 'Officer'

Any suggestions? I know I can add a REPLACE function five times, but I have a few more uses for this so I would like to find the easiest method.

Thanks,

Lee

not context or sample DDL or data but how about something like

SELECT

CASE CAST(m.UserLevelID AS varchar(10))

WHEN '5' THEN 'Officer'

ELSE ''

END

|||

If you are going to reuse it, a more compact form is something like:

substring (' Yes', 1+3*(UserLevelId/5), 3)

as in the example:

declare @.test table ( UserLevelId integer )
insert into @.test values (1)
insert into @.test values (2)
insert into @.test values (3)
insert into @.test values (4)
insert into @.test values (5)

select UserLevelId,
substring (' Yes', 1+3*(UserLevelId/5), 3) as Indicator
from @.test

-- UserLevelId Indicator
-- --
-- 1
-- 2
-- 3
-- 4
-- 5 Yes

|||

You could have a small table with the UserLevedID and the replacement values, and then JOIN against that table. If you have several such 'replacements' (or lookups as they are often called), then you could add an additional column to specify the 'group' of replacements.

SET NOCOUNT ON

CREATE TABLE MyTable
( LookupGroup int,
UserLevelID int,
UserLevel varchar(20),
)
GO

A lot better and more robust than attempting to 'hard-code' a potentially changing list of values.

repeat group footer based on quantity

I am trying to find a way to make a report repeat a group footer based on the quanitity of the item. Short of inserting up to 100 group footers, copying the fields, and conditionally supressing the footers I don't need based on the quantity I haven't been able to find a reasonable solution.

Any ideas.

Crystal Reports 10Why do you want to do this?