Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

856 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