CASE statement and boolean expressions

SQL 2000.
The CASE syntax is described like this:
Simple CASE function:
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
Input_expression is "any valid Microsoft SQL Server expression".
So you can have:
Select CASE 2 & 4 When 0 Then 'No' Else 'Yes' End
This has the input_expression "2 & 4" which uses the bitwise "and"
operator "&".
But the Input_expression apparently can't be a logical comparison
(Boolean expression) using "=" or "<". This doesn't work:
Select CASE 2 < 4 When True Then 'Less' Else 'More' End
Why can't input_expression really be "any valid Microsoft SQL Server
expression"? Isn't a comparison a valid SQL Server boolean expression?
One legal value for expression is defined in BOL as
expression { binary_operator } expression
So 2 < 4 should be a valid input_expression for the CASE statement.
What's the problem here?
Thanks.
David Walker> Select CASE 2 < 4 When True Then 'Less' Else 'More' End
There is no such thing as a boolean, so you can't say CASE SOMETHING WHEN
TRUE
Try
SELECT CASE WHEN 2 < 4 THEN 'Less' ELSE 'More' END|||The problem is that SQL Server doesn't have a proper Boolean datatype at
all. Booleans are only valid in certain places and they can't be mixed with
other datatypes. The documentation could be clearer but in this case you
need the searched CASE expression:
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
David Portas
SQL Server MVP
--|||>> Isn't a comparison a valid SQL Server boolean expression? <<
Back to the Basics for you! An expression returns a scalar value of
some data type. There are no Boolean datatypes in SQL. They would
screw up the 3VL. Ergo, there are no Boolean expressions in SQL.
In fact, if you look at the BNF for the standards, the WHERE and ON
clauses use <search conditions>, not predicates. You think you are
still writing C or a related language where everything returns a value.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in
news:1122931388.782308.16220@.g47g2000cwa.googlegroups.com:

> Back to the Basics for you! An expression returns a scalar value of
> some data type. There are no Boolean datatypes in SQL. They would
> screw up the 3VL. Ergo, there are no Boolean expressions in SQL.
> In fact, if you look at the BNF for the standards, the WHERE and ON
> clauses use <search conditions>, not predicates. You think you are
> still writing C or a related language where everything returns a
> value.
>
I don't write in C, but yes, I think that "2 < 4" should have a value,
and return a value of True or False (most likely True, but you know...)
Obviously in SQL it doesn't work that way.
I still think that BOL says that for CASE, "input_expression is any
valid Microsoft SQL Server expression", in fact, I'm sure of it, since
I copied this from BOL. I also think that BOL has a Boolean subheading
under Expressions. This subheading just describes NOT as "negating a
Boolean expression". So according to this, MS SQL *does* have Boolean
expressions, according to this. Of course, there is no Boolean
datatype, but there ARE Boolean expressions!
But let's talk about expressions. Expressions/Overview says that
Expression is "A combination of symbols and operators that Microsoft
SQL Server evaluates to obtain a single data value". This could be
re-worded to say "a combination of symbols and operators that SQL Server
is able to evaluate to a native datatype" or something like that. It
doesn't actually say this.
The definition of Expressions/Overview can easily be read to think that
"4 < 6" would be a valid SQL expression. Maybe the only thing stopping
it is the lack of a Boolean datatype?
Nowhere does BOL say that input_expression in the Case statement can't
be a Boolean expression, or can't be an expression that uses a
comparison operator!
Expressions/Overview says that expression can be expression {
binary_operator } expression, and says that "{binary_operator} is an
operator that defines the way two expressions are combined to yield a
single result. binary _operator can be an arithmetic operator, the
assignment operator (=), a bitwise operator, a comparison operator, a
logical operator, the string concatenation operator (+), or a unary
operator."
So for one thing, since = is a comparison operator and an assignment
"operator" both, there's some ambiguity here. I know assignments can't
be used just anywhere, and apparently = as a comparison operator can't
be used just anywhere, and I suppose any of the other comparison
operators can't be used in a CASE statement.
Maybe all of these forms shouldn't be defined as valid expressions,
lumping them all together, if the CASE statement is going to say that
input_expression is "any valid Microsoft SQL Server expression".
Of course since what I was trying to do doesn't work, then either the
documentation is wrong (or unclear), or ... I suppose the documentation
is just poorly done.
Thanks.
David Walker|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
news:#RoSnctlFHA.3552@.TK2MSFTNGP10.phx.gbl:

> There is no such thing as a boolean, so you can't say CASE SOMETHING
> WHEN TRUE
> Try
> SELECT CASE WHEN 2 < 4 THEN 'Less' ELSE 'More' END
>
Thanks. See my reply to Joe Celko's comment. Obviously there's no
Boolean datatype, but there are Boolean expressions. But it seems they
can't be used in the CASE statement.
David Walker|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
news:u6ydnSfjYZK4G3PfRVn-ig@.giganews.com:

> The problem is that SQL Server doesn't have a proper Boolean datatype
> at all. Booleans are only valid in certain places and they can't be
> mixed with other datatypes. The documentation could be clearer but in
> this case you need the searched CASE expression:
> CASE
> WHEN Boolean_expression THEN result_expression
> [ ...n ]
> [
> ELSE else_result_expression
> ]
> END
>
Thanks, that helps.
David|||> Thanks. See my reply to Joe Celko's comment. Obviously there's no
> Boolean datatype, but there are Boolean expressions.
I disagree. In any case, you'll have to work around these so-called
shortcomings, because SQL Server / T-SQL aren't going to change overnight in
order to support the syntax you'd prefer.|||>> I don't write in C, but yes, I think that "2 < 4" should have a value, an
d return a value of True or False (most likely True, but you know...) Obviou
sly in SQL it doesn't work that way. <<
That is a C family convention -- C++, C#, Java, etc. Other languages do
not follow it.
is just poorly done. <<
The documentation people are screwed up. Hey, kid, I spent a decade of
my life on ANSI X3H2 writting standards; I know the BNF. Hell,
Microsoft still confuses fields and columns in their documentation!
The problems are that (1) all datatypes must be NULL-able (2) NULLs
propagate (3) the rules of 3VL would have to handle {TRUE, FALSE
UNKNOWN, NULL} and the truth tables are self-contradictory. Boolean
data types would destroy the foundations of SQL.|||> The problems are that (1) all datatypes must be NULL-able (2) NULLs
> propagate (3) the rules of 3VL would have to handle {TRUE, FALSE
> UNKNOWN, NULL} and the truth tables are self-contradictory. Boolean
> data types would destroy the foundations of SQL.
I was always taught that Null = Unknown. It is because of this synomous rela
tion
that Null logic makes sense. For example Null = X is always Null because it
equates to Unknown = X. There is no way to logical way to determine the resu
lt
of that statement and thus it returns Unknown or more specifically Null.
So, given that **three** value logic has three values, how is it that boolea
n
types would "destroy the foundation of SQL"?
Thomas