Convert to Character - SQL Server Forums

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)

Convert to Character

Author

Topic

oahu9872
Posting Yak Master

112 Posts

Posted - 2008-07-10 : 13:02:39

I have a select query that contains the following field enclosed in a case statement:

case when ts.prstatus = 3 then 33 else ts.prstatus end as ts.prstatus

This compiles and runs fine as long as I use a numeric value. What I want though is to say when the result is 3, return a text string, although I am not getting that to work.

What is the best way to do this, can I use some sort of to_char or convert function in my case statement, or create a separate column that reads this result and returns a string? Any help would be appreciated. For reference, here is my entire query...

select distinct ts.prresourceid,srm.full_name,te.practsum/3600 hrs,ts.prid,case when ts.prstatus = 3 then 33 else ts.prstatus end as ts.prstatus,tp.prstart,tp.prfinish,ass.prtaskid,tsk.prname,tsk.prprojectid,prj.name from
niku.prtimesheet ts,niku.prtimeentry te,niku.prtimeperiod tp,niku.prassignment ass,niku.prtask tsk,niku.srm_resources srm, niku.srm_projects prj
where prtimesheetid = ts.prid
and ts.prtimeperiodid = tp.prid
and ass.prresourceid = ts.prresourceid
and tsk.prid = ass.prtaskid
and ts.prresourceid = srm.id
and tsk.prprojectid = prj.id
and ts.prresourceid in (select
record_id from niku. prj_obs_associations
where table_name = 'SRM_RESOURCES'
and unit_id in
(select id from niku.prj_obs_units where type_id = 5000002 and unique_name like '%eclipse%'))

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-10 : 13:36:54

case
when ts.prstatus = 3 then '33'
else convert(varchar(5),ts.prstatus)
end as prstatus

Jim

P.S If te.practsum is an integer, do this
te.practsum*1.0/3600

oahu9872
Posting Yak Master

112 Posts

Posted - 2008-07-10 : 13:45:37

That works great, thanks for the help.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-10 : 13:51:54

If ts.[prStatus] is a string type of some kind then your case statement is implitly translating a number into an int.

you want to do this I think.


CASE ts.[prStatus]
    WHEN '3' THEN '33'
    ELSE ts.[prStatus]
END

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-10 : 13:52:46

never mind me -- the world moved on and answered your question while I was away.

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources