[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Convert a SQL value to Boolean

Posted on 2007-10-19
6
Medium Priority
?
4,155 Views
Last Modified: 2008-01-09
In my "IIF", I am trying to convert the value to boolean and getting an error.

CREATE    PROCEDURE ssrSP_getCourse_Campus
 @CourseNum as varchar(10),
 @SessionNum as int,
 @yrId as int=5
AS
select
 sc.schoolId as CampusId,
 sc.schcode,
 sc.SchoolName,
 cc.courseNumber,
 (select count(*)
      from course_type_session ct
       join schoolSession ss on ct.schoolSessionId = ss.schoolSessionId
       join Session s on ss.SessionId = s.Id
       join course c on c.Id = ct.courseId
      where c.coursenumber = cc.courseNumber
       and c.schoolCode = sc.schCode
       and s.SchYearId  = @yrId
       and s.SessionNo = @SessionNum) as CntSession,
             
IIf((select count(*)
      from course_type_session ct
       join schoolSession ss on ct.schoolSessionId = ss.schoolSessionId
       join Session s on ss.SessionId = s.Id
       join course c on c.Id = ct.courseId
      where c.coursenumber = cc.courseNumber
       and c.schoolCode = sc.schCode
       and s.SchYearId  = @yrId
       and s.SessionNo = @SessionNum) > 0,"No", "Yes")  as Assigned
 
from school sc
 join course_catalog cc on sc.academiclevelid = cc.Academiclevelid
where cc.coursenumber = @CourseNum
 and sc.SSCampus = 1
 and sc.Active = 1
 and sc.schoolYearId = @yrId
GO
0
Comment
Question by:keithwilson1
  • 2
  • 2
  • 2
6 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20111710
'IIF' wont work with SQL, you need to make use of Case statement

Case when exists (select 1  
      from course_type_session ct
       join schoolSession ss on ct.schoolSessionId = ss.schoolSessionId
       join Session s on ss.SessionId = s.Id
       join course c on c.Id = ct.courseId
      where c.coursenumber = cc.courseNumber
       and c.schoolCode = sc.schCode
       and s.SchYearId  = @yrId
       and s.SessionNo = @SessionNum) Then 'False' else 'True' End as Assigned
0
 

Author Comment

by:keithwilson1
ID: 20111942
I need to do a count and test it to be greater than ZERO
0
 
LVL 35

Accepted Solution

by:
James0628 earned 1000 total points
ID: 20112974
That's basically what that code does.  If the inner SELECT finds something, it will output the value 1, so the WHEN EXISTS in the CASE will be true and Assigned will be "False".  If the SELECT does not find anything, it won't output anything, so the WHEN EXISTS will be false and Assigned will be "True".  To get your Yes and No, just replace False with No and True with Yes.

 If you needed to look for other values, like > 5, then you would have to use Count or something to get a count of the items found, but if you just want to know if anything was found, WHEN EXISTS will do it.

 James
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:keithwilson1
ID: 20123816
So what is wrong with this code?
I am getting Error 170 Invalid syntax near '>'
CASE
      WHEN exists (select count(*)
      from course_type_session ct
       join schoolSession ss on ct.schoolSessionId = ss.schoolSessionId
       join Session s on ss.SessionId = s.Id
       join course c on c.Id = ct.courseId
      where c.coursenumber = cc.courseNumber
       and c.schoolCode = sc.schCode
       and s.SchYearId  = @yrId
       and s.SessionNo = @SessionNum) > 0 THEN 'Yes'
      ELSE 'No'
   END as Assigned
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 1000 total points
ID: 20123835
CASE
      WHEN exists (select 1
      from course_type_session ct
       join schoolSession ss on ct.schoolSessionId = ss.schoolSessionId
       join Session s on ss.SessionId = s.Id
       join course c on c.Id = ct.courseId
      where c.coursenumber = cc.courseNumber
       and c.schoolCode = sc.schCode
       and s.SchYearId  = @yrId
       and s.SessionNo = @SessionNum) THEN 'Yes'
      ELSE 'No'
   END as Assigned
0
 
LVL 35

Expert Comment

by:James0628
ID: 20127805
You've probably figured this out, but just in case ...

 The error was because you were trying to do two different tests, EXISTS and >, on the results of one SELECT statement.  You have to use one or the other, and aneeshattingal's code with EXISTS seems more efficient that producing and testing a count.

 Glad I could help.

 James
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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

829 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