sql server programming: Case statement -- sql server programming

Re: Case statement

Steve Kass
3/14/2004 10:42:47 PM

Ajay,

I don't see why NULL in the value column would cause any problem. I
suspect you have empty strings or other strings that don't represent
numeric values. As others mentioned, you shouldn't store numeric values
as char(25). I'd also suggest writing your years in the query as
'2001', '2002', etc., since with 2001, 2002, etc., you are requiring an
implicit type conversion that's not necessary.

Here's a repro to show that NULL in the value column is fine:

create table tmp_table (
company_name char(33),
prd_yr char(4),
prd_month char(2),
value char(25)
)
insert into tmp_table values ('abc','2001','12',null)
insert into tmp_table values ('abc','2001','12',0)
insert into tmp_table values ('abc',null,'12',14)

select company_name,
sum (case prd_yr when 2001 then convert(numeric(25,5),value) else 0 end)
as yr1,
sum (case prd_yr when 2002 then convert(numeric(25,5),value) else 0 end)
as yr2,
sum (case prd_yr when 2003 then convert(numeric(25,5),value) else 0 end)
as yr3

from tmp_table
group by company_name
go

drop table tmp_table

SK

[quoted text, click to view]

Re: Case statement

ajmiester
3/14/2004 11:01:50 PM

Thank you Steve,
The table was created by someone else and the value field has (null)
values that are causing the error. I tried to insert the data into a tmp
table with a nuneric value field but that to failed at the (null) value. Any
suggestion how to fix that.

Ajay

[quoted text, click to view]

Re: Case statement

Steve Kass
3/15/2004 12:24:13 AM

Ajay,

NULL shouldn't be a problem. Is it possible that what's in the table
is the string 'NULL' or '(null)' ? If that's the case, how about
running this before processing the data?

update tmp_table set
value = null
where upper(value) like '%NULL%'

SK

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.