Solved

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

Posted on 2008-06-25
4
378 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

929 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

20 Experts available now in Live!

Get 1:1 Help Now