Solved

Simple IF...Else Statement to convert 1/0 to Yes/No

Posted on 2008-06-25
4
388 Views
Last Modified: 2011-10-03
I am new to T-SQL and am using reporting services to create reports from the database. However, the database shows a field called Active as 1 or 0. I want it to say Yes if 1 is shown and No if 0 is shown. I dont want to update the database to Yes or No but keep that 1/0 although when i want to report that field, i want to show Yes/no

This is my query as of now.

IF ( SELECT active from dbo.user where active ='1') = '1'
BEGIN
PRINT 'YES'
Select active from dbo.user where active = '1'
END
ELSE
IF (Select active from dbo.user where active ='0') ='0'
BEGIN
PRINT 'NO'
END

The Error says - MSG 512 Subquery returned more than 1 value. This is not permitted when the subquery follows =,!=,... or when the subquery is used as an expression.

I am sorry as i am new to this and probably have it all wrong. Please advice it will be greatly appreciated.
0
Comment
Question by:nsehmi
4 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21867907
IF exists( SELECT active from dbo.user where active ='1')
BEGIN
PRINT 'YES'
Select active from dbo.user where active = '1'
END
ELSE
IF exists(Select active from dbo.user where active ='0')
BEGIN
PRINT 'NO'
END
0
 
LVL 2

Accepted Solution

by:
pierky earned 50 total points
ID: 21867909
Try this...
SELECT
   CASE active
      WHEN 1 THEN 'Yes'
      ELSE 'No'
   END AS active
FROM
   user

Open in new window

0
 
LVL 7

Expert Comment

by:60MXG
ID: 21867965
Try to change '=' to 'is'
0
 

Author Closing Comment

by:nsehmi
ID: 31470671
Thanks so much. It was so much easier then expected!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now