Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-06-25
4
Medium Priority
?
425 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 200 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

824 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