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

Case statement

Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1Not the way you're doing it. You can have something along the lines of:
UPDATE t1
SET
f1_status = CASE
WHEN f1_status = 0 THEN 1
ELSE f1_status END
, f1_created = CASE
WHEN f1_status = 0 THEN getdate()
ELSE f1_created END
WHERE t1id = 1
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:5df3ff92c5d28@.uwe...
Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||The issue would become very straightforward and simple only if one stops
using the phrase 'a CASE statement' because there is no such a thing, and
starts using 'a CASE expression.'
Linchi
"cbrichards via SQLMonster.com" wrote:
> Is it possible to have multiple expressions, or dml expressions in a case
> statement similar to the following?
> DECLARE @.path_f1 nvarchar(200)
> DECLARE @.path_f2 nvarchar(200)
> SELECT
> @.path_f1 = output_path_file1,
> @.path_f2 = output_path_file2
> FROM t1
> WHERE t1id = 1
> SELECT
> CASE
> WHEN f1_status = 0 THEN
> UPDATE t1
> SET f1_status = 1, f1_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f1, null
> WHEN f1_status = 1 AND f2_status = 0 THEN
> UPDATE t1
> SET file2_status = 1, file2_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f2, null
> ELSE
> RAISERROR ('Error Message', 16,1)
> END
> FROM t1
> WHERE t1id = 1
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1
>

Case statement

Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
Not the way you're doing it. You can have something along the lines of:
UPDATE t1
SET
f1_status = CASE
WHEN f1_status = 0 THEN 1
ELSE f1_status END
, f1_created = CASE
WHEN f1_status = 0 THEN getdate()
ELSE f1_created END
WHERE t1id = 1
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5df3ff92c5d28@.uwe...
Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
|||The issue would become very straightforward and simple only if one stops
using the phrase 'a CASE statement' because there is no such a thing, and
starts using 'a CASE expression.'
Linchi
"cbrichards via droptable.com" wrote:

> Is it possible to have multiple expressions, or dml expressions in a case
> statement similar to the following?
> DECLARE @.path_f1 nvarchar(200)
> DECLARE @.path_f2 nvarchar(200)
> SELECT
> @.path_f1 = output_path_file1,
> @.path_f2 = output_path_file2
> FROM t1
> WHERE t1id = 1
> SELECT
> CASE
> WHEN f1_status = 0 THEN
> UPDATE t1
> SET f1_status = 1, f1_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f1, null
> WHEN f1_status = 1 AND f2_status = 0 THEN
> UPDATE t1
> SET file2_status = 1, file2_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f2, null
> ELSE
> RAISERROR ('Error Message', 16,1)
> END
> FROM t1
> WHERE t1id = 1
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200603/1
>

Case statement

Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1Not the way you're doing it. You can have something along the lines of:
UPDATE t1
SET
f1_status = CASE
WHEN f1_status = 0 THEN 1
ELSE f1_status END
, f1_created = CASE
WHEN f1_status = 0 THEN getdate()
ELSE f1_created END
WHERE t1id = 1
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5df3ff92c5d28@.uwe...
Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1|||The issue would become very straightforward and simple only if one stops
using the phrase 'a CASE statement' because there is no such a thing, and
starts using 'a CASE expression.'
Linchi
"cbrichards via droptable.com" wrote:

> Is it possible to have multiple expressions, or dml expressions in a case
> statement similar to the following?
> DECLARE @.path_f1 nvarchar(200)
> DECLARE @.path_f2 nvarchar(200)
> SELECT
> @.path_f1 = output_path_file1,
> @.path_f2 = output_path_file2
> FROM t1
> WHERE t1id = 1
> SELECT
> CASE
> WHEN f1_status = 0 THEN
> UPDATE t1
> SET f1_status = 1, f1_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f1, null
> WHEN f1_status = 1 AND f2_status = 0 THEN
> UPDATE t1
> SET file2_status = 1, file2_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f2, null
> ELSE
> RAISERROR ('Error Message', 16,1)
> END
> FROM t1
> WHERE t1id = 1
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200603/1
>sql

Case expressions may only be nested to level 10

I'm running into this issue when I try to query a table (well a view) that was poorly designed and has many fields in the table as YesNo fields instead of a single field that just contains a value. (Fields A-N in the table for example)

So my query looks like:

CASE
WHEN A = 1 THEN 'A'
WHEN B = 1 THEN 'B'
WHEN C = 1 THEN 'C'
WHEN D = 1 THEN 'D'
WHEN E = 1 THEN 'E'
WHEN F = 1 THEN 'F'
WHEN G = 1 THEN 'G'
WHEN H = 1 THEN 'H'
WHEN I = 1 THEN 'I'
WHEN J = 1 THEN 'J'
WHEN K = 1 THEN 'K'
WHEN L = 1 THEN 'L'
WHEN M = 1 THEN 'M'
WHEN N = 1 THEN 'N'
END AS GeneratedField

Is there some other way I can approach this? I can't change the table as it isn't mine, but I need to run a report that looks at those fields and in GeneratedField places the result for the field that was checked.

Thanks.

Yes. Case Expression can be nested upto 10 Level.

But your expression is not looking like a nested expression.

Can you post the exact expression you are using.. Is any one of these A-N columns hold the value as 1 and reset 0.

|||

Ars_maxer,

There is another way of doing it, it's complicated than simpe case statement, though. Use Unpivot.

Here it is.

Code Snippet

declare @.tb table (
id int,
A int,
B int,
C int,
D int
);
insert into @.tb values(1, 1,0,0,0)
insert into @.tb values(2, 0,1,0,0)
insert into @.tb values(3,0,0,1,0)
insert into @.tb values(4,1,0,0,0)

select id, genvalue, vvalue from
( select id, A,B,C,D from @.tb)p
UNPIVOT
(vvalue for genvalue in (A,B,C,D) ) as unpvt
where vvalue =1

id genvalue vvalue
1 A 1
2 B 1
3 C 1
4 A 1

|||

This is a little hoakie, but here we go:

Code Snippet

declare @.a int, @.b int, @.c int, @.d int, @.e int

set @.a = 0

set @.b = 0

set @.c = 1

set @.d = 0

set @.e = 0

selectcoalesce(replace(convert(char(1),nullif(@.a,0)),'1','A'),

replace(convert(char(1),nullif(@.b,0)),'1','B'),

replace(convert(char(1),nullif(@.c,0)),'1','C'),

replace(convert(char(1),nullif(@.d,0)),'1','D'),

replace(convert(char(1),nullif(@.e,0)),'1','E')

)

|||

I'm not doing anything that isn't shown in my SQL statement there (Other than a SELECT and FROM and WHERE). However, nothing unusual.

Now if I run that SQL with say 8 statements then it works just fine.

I've read that this appears to be an issue and that others have run into it before. In some cases they wrapped the whole thing in a stored procedure on the linked server or their server and ran it that way.

In my case I'm executing this against a linked server and can't make a sproc there.

I'll play with the examples provided above and see if those will address the issue for me or not.

Thanks!