Help with Case Statement/Convert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Case Statement/Convert

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-12-02 : 08:12:15

I am trying to hardcode 'N/A' when a null value appears in the 'dormant_date' column.

When I run the below query, null is still appearing in the 'dormant_date' column.

select symbol,
'dormant_date' = case
when CONVERT(varchar,coalesce(c.dormant_date , '')) = '' then 'N/A' else
c.dormant_date
end
from billing_invoices bi

Currently, my data looks something like this when I run the above query:

L8 NULL
L8 NULL
L8 NULL
L99 2007-03-08 00:00:00.000

Any thoughts?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-02 : 08:17:01

1 If you use front end application, show 'N/A' there
2 Otherwise

select symbol,
coalesce(convert(char(10),c.dormant_date,101), 'N/A') as dormant_date
from billing_invoices bi

Madhivanan

Failing to plan is Planning to fail

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-12-02 : 08:31:12

Perfect, thanks Madhivanan!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-02 : 08:36:32

quote:
Originally posted by qman

Perfect, thanks Madhivanan!

Note that now the dates are actually characters that you may not treat it as a date

Madhivanan

Failing to plan is Planning to fail

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-12-02 : 09:23:40

Thanks, the results are just for analysis purposes.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-02 : 09:26:56

quote:
Originally posted by qman

Thanks, the results are just for analysis purposes.

Then, no problem

Madhivanan

Failing to plan is Planning to fail

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-12-02 : 09:30:03

Now I am getting a "Arithmetic overflow error converting expression to data type int."

select c.symbol, sum(bi.volume) as vol
from invoices bi
inner join company c on bi.company_id = c.company_id
group by c.symbol

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-12-02 : 09:32:47

I got it to work by casting the sum() to sum(cast(bi.volume as bigint))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-02 : 09:35:15

quote:
Originally posted by qman

Now I am getting a "Arithmetic overflow error converting expression to data type int."

select c.symbol, sum(bi.volume) as vol
from invoices bi
inner join company c on bi.company_id = c.company_id
group by c.symbol

You are supposed to post here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=136653

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources