Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Boolean Expressions in SQL Select list

I want to create a SQL Select to do a unit test in MS SQL Server 2005. The basic idea is this:

select 'Test Name', foo = 'Result'
from bar
where baz = (some criteria)

The idea being that, if the value of the "foo" column is "Result", then I'd get a value of true/1; if it isn't, I'd get false/0.

Unfortunately, T-SQL doesn't like the expression; it chokes on the equals sign.

Is there some way of evaluating an expression in the SQL select list and getting a returnable result? (Or some other way of achieving the unit testing that I want?)


EDIT: 3 great, answers, all built around CASE. I'll accept feihtthief's as he's got the least rep and thus needs it the most. :-) Thanks to everyone.

like image 823
Craig Walker Avatar asked Jan 30 '09 22:01

Craig Walker


People also ask

How do you write a Boolean expression in SQL?

You can insert a boolean value using the INSERT statement: INSERT INTO testbool (sometext, is_checked) VALUES ('a', TRUE); INSERT INTO testbool (sometext, is_checked) VALUES ('b', FALSE); When you select a boolean value, it is displayed as either 't' or 'f'.

What type of results can a Boolean expression have in SQL?

A Boolean expression can consist of Boolean data, such as the following: BOOLEAN values ( YES and NO , and their synonyms, ON and OFF , and TRUE and FALSE )

What are the 4 Boolean operators?

Boolean operators are specific words and symbols that you can use to expand or narrow your search parameters when using a database or search engine. The most common Boolean operators are AND, OR, NOT or AND NOT, quotation marks “”, parentheses (), and asterisks *.

What are the boolean values in SQL?

In standard SQL, a Boolean value can be TRUE , FALSE , or NULL .


3 Answers

Use the case construct:

select 'Test Name', 
    case when foo = 'Result' then 1 else 0 end 
    from bar where baz = (some criteria)

Also see the MSDN Transact-SQL CASE documentation.

like image 58
feihtthief Avatar answered Oct 16 '22 18:10

feihtthief


SELECT 'TestName', 
    CASE WHEN Foo = 'Result' THEN 1 ELSE 0 END AS TestResult
FROM bar 
WHERE baz = @Criteria
like image 44
Joel Coehoorn Avatar answered Oct 16 '22 20:10

Joel Coehoorn


Use CASE:

SELECT 'Test Name' [col1],
  CASE foo
    WHEN 'Result' THEN 1
    ELSE 0
  END AS [col2]
FROM bar
WHERE baz = (some criteria)
like image 17
John Avatar answered Oct 16 '22 20:10

John