Displaying a boolean as a phrase

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 2000 Forums

Transact-SQL (2000)

Displaying a boolean as a phrase

Author

Topic

daveyboy
Starting Member

24 Posts

Posted - 2005-11-07 : 07:02:03

I have a table that displays a true/false to indicate whether someone is a homeowner.
Instead of displaying true/false, how can i get SQL 2000 to output Homeowner or Tennent instead

Thanks for any help offered

my query in case you are interested


select b.forename + ' ' + b.surname as 'operator name', c.name as 'broker name', homeowner, count (*) as 'total submitted'
from [customerdata].[dbo].[shortenquiry] as a
left join [customerdata].[dbo].[adminusers] as b on a.assignedToBrokerByOperatorID = b.id
left join [tconfiguration].[dbo].[companies] as c on a.assignedToBrokerID = c.id
where assignedToBrokerDateTime between convert(char(12),getdate(),106) + '00:00:00' and convert(char(12),getdate(),106) + '23:59:59'
group by b.forename + ' ' + b.surname , c.name, homeowner

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-07 : 07:10:15

somthing like this ..

Select Case When <BoolCol> = 'True' Then 'HomeOwner'
When <BoolCol> ='False' Then Tennent End As <AliasName>
From <TableName>

Hope this helps you

Complicated things can be done by simple thinking

daveyboy
Starting Member

24 Posts

Posted - 2005-11-07 : 07:30:42

Excuse my ignorance, but where in the code do i insert this?

sorry, newbiwe here................

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-07 : 07:34:01

Replace the column with that code in your select statement
Which column do you want to show?

Madhivanan

Failing to plan is Planning to fail

daveyboy
Starting Member

24 Posts

Posted - 2005-11-07 : 07:49:52

I just want to replace the homeowner boolean in the select statement with the 'Home Owner' and 'Tennant Figures'

i would have thought it would be

 select b.forename + ' ' + b.surname as 'operator name', c.name as 'broker name', homeowner Case When <BoolCol> = 'True' Then 'HomeOwner' When <BoolCol> ='False' Then Tennent End As <AliasName>, count (*) as 'total submitted'

but no!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-07 : 07:55:57

Try this

select b.forename + ' ' + b.surname as 'operator name', c.name as 'broker name', Case When homeowner = 'True' Then 'HomeOwner' else 'Tennent' End As Home_Owner, count (*) as 'total submitted'

Madhivanan

Failing to plan is Planning to fail

daveyboy
Starting Member

24 Posts

Posted - 2005-11-07 : 09:28:08

Thanks guys, i went with

select b.forename + ' ' + b.surname as 'operator name', c.name, count (*) as 'total submitted',

Case homeowner 

How can i name teh homeowner column

When 0 Then 'Tennant' End 

From [customerdata].[dbo].[shortenquiry] as a

left join [customerdata].[dbo].[adminusers] as b on a.assignedtobrokerbyoperatorid = b.id

left join [configuration].[dbo].[companies] as c on a.assignedtobrokerid = c.id

where submitteddatetime between convert(char(14), getdate(),102) + '00:00:00' and convert(char(14), getdate(),102) + '23:59:59'

and statusid = 4

group by b.forename + ' ' + b.surname, c.name, homeowner

order by b.forename + ' ' + b.surname

BUT, and this is my last question ;-)

How do i name to homeowner column?

Thanks for your help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-07 : 09:31:06

When 0 Then 'Tennant' End as yourName

Madhivanan

Failing to plan is Planning to fail

daveyboy
Starting Member

24 Posts

Posted - 2005-11-07 : 09:59:41

Thanks, works great

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources