Returning Alternative Value - 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

General SQL Server Forums

New to SQL Server Programming

Returning Alternative Value

Author

Topic

bd528
Starting Member

23 Posts

Posted - 2011-07-15 : 03:57:26

I was wondering if it was possible to have an alternative value returned from a query, rather than the actual query result.
For example, if the query result was 1000, the words "One Thousand" were returned.
Thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-15 : 04:03:37

Yes. You can use a User Defined Function to do that.

Search this site for amount to words function.

KH
[spoiler]Time is always against us[/spoiler]

bd528
Starting Member

23 Posts

Posted - 2011-07-15 : 04:10:25

Sorry, that was a poor choice of example. I'm wanting "NNH" to be displayed if the actual value "01" is found.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-15 : 04:15:42

select
col1,
col2,
case
when col3='01' then 'NNH'
when col3='02' then 'XYZ'
else 'ABC'
end as col3,
col4
from table
where ...

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-15 : 04:16:27

this is what you want ?


create table tbl
(
    col1 varchar(10),
    col2 varchar(10)
)

insert into tbl select '01', 'NNH'

select t.col2
from   yourquery q
       inner join tbl t on q.somecol = t.col1

KH
[spoiler]Time is always against us[/spoiler]

bd528
Starting Member

23 Posts

Posted - 2011-07-15 : 04:29:38

When using the select/case option, does that change the db values, or just the query results? I only have limited access to the database, and the select/case option seems to be causing a problem.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-15 : 04:40:13

select/case changes the query result. it does not affect the DB values.

KH
[spoiler]Time is always against us[/spoiler]

bd528
Starting Member

23 Posts

Posted - 2011-07-15 : 04:41:12

Got it working :-

case account.Acctno
when '00' then 'NHH'
else account.Acctno
end,

Thanks for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources