Case Statement in Where Clause

[paulmac106]

If you could help me with my syntax i would really appreciate
it. I'm trying to change the where clause on the fly, but it's
giving me a syntax error.

Hopefully from what I have below you can tell what i'm after

the first part should evaluate a boolean expression, then if true,
search using one field, otherwise
search using a different field

WHERE

Case WHEN @myCompany = 933
THEN tblClient.compa nycode = 933 --problem line
ELSE
tblCase.clientc ode = @myClient --problem line
END

And tblCase.status = 'Active'

thank you!!

[Dan Guzman]

In SQL Server, CASE doesn't conditionally execute statements, it returns an
expression. You might try something like:

WHERE
tblClient.compa nycode =
CASE
WHEN @myCompany = 933
THEN 933 ELSE tblClient.compa nycode END
AND
tblCase.clientc ode =
CASE
WHEN @myCompany < 933
THEN @myClient ELSE tblCase.clientc ode END
AND tblCase.status = 'Active'

--
Hope this helps.

Dan Guzman
SQL Server MVP
<pa********@gma il.comwrote in message
news:11******** *************@i 3g2000cwc.googl egroups.com...

If you could help me with my syntax i would really appreciate
it. I'm trying to change the where clause on the fly, but it's
giving me a syntax error.

Hopefully from what I have below you can tell what i'm after

the first part should evaluate a boolean expression, then if true,
search using one field, otherwise
search using a different field

WHERE

Case WHEN @myCompany = 933
THEN tblClient.compa nycode = 933 --problem line
ELSE
tblCase.clientc ode = @myClient --problem line
END

And tblCase.status = 'Active'

thank you!!

[Ed Murphy]

Dan Guzman wrote:

In SQL Server, CASE doesn't conditionally execute statements, it returns
an expression. You might try something like:

WHERE
tblClient.compa nycode =
CASE
WHEN @myCompany = 933
THEN 933 ELSE tblClient.compa nycode END
AND
tblCase.clientc ode =
CASE
WHEN @myCompany < 933
THEN @myClient ELSE tblCase.clientc ode END
AND tblCase.status = 'Active'

Another alternative:

WHERE (NOT(@myCompany = 933) OR tblClient.compa nycode = 933)
AND (NOT(@myCompany <933) OR tblCase.clientc ode = @myClient)
AND tblCase.status = 'Active'

[--CELKO--]

There is no CASE statement in SQL; there is a CASE expression.
Expressions return values. There is no BOOLEAN data type in SQL.
Fields are not anything like columns. Your entire mental model of SQL
is wrong and you are trying to make it work like a procedural language
that you already know.

Also, we prefer to follow ISO-11179 rules for data elements names, so
quit putting that silly, redundant "tbl-" on table names (unless
furniture is actually involved) and start using collective or plural
names (unless the table is not a set or class of entities, but a single
item).

Here is a guess at what you wanted

WHERE ( COALESCE (@my_company , Clients.company _code) = 933
OR Cases.clientcod e = @my_client)
AND Case.foobar_sta tus = 'Active' -- status is too vague

[paulmac106]

I don't understand the necessity of the diatribe, but thanks for the
suggestion.

[paulmac106]

This first 2 suggestions worked perfectly. The third offering returned
unwanted records.

Thanks for your help. I really appreciate it.

[--CELKO--]

>I don't understand the necessity of the diatribe,<<

EDUCATION ! Your approach is wrong and you are going to kill people or
ruin companies until you get a better mindset. Get over your ego and
become professional. It takes six years to become a Union Journeryman
Carpenter in New York State, but newbies expect to do databases
immeidtely after they get MS certificates. Would you like medicine to
work this way?

You are welcome!

[paulmac106]

OK, at least that one was funny.

No ego here, I have to admit you peeked my interest. I've been doing
database work for 10 years now. I've managed to avoid killing people
and ruining companies at least up till the time of this posting.

I do always like to learn new things or better ways to do things.

So, having said that, I'm really curious as to who "we" are that you
reference in your original post. And where can I go to get information
on things like "ISO-11179 rules for data elements names" or another
site to fix my mindset?

thanks,
Paul

btw, Boolean is right there in online books

Searched CASE function:

CASE
WHEN Boolean_express ion THEN result_expressi on
[ ...n ]
[
ELSE else_result_exp ression
]
END

[Ed Murphy]

pa********@gmai l.com wrote:

So, having said that, I'm really curious as to who "we" are that you
reference in your original post. And where can I go to get information
on things like "ISO-11179 rules for data elements names" or another
site to fix my mindset?

Googling "ISO-11179" turns up http://metadata-standards.org/11179/
and in particular http://metadata-standards.org/11179/#11179-5

"Part 5: Naming and Identification Principles, provides guidance for the
identification of administered items. Identification is a broad term
for designating, or identifying, a particular data item. Identification
can be accomplished in various ways, depending upon the use of the
identifier. Identification includes the assignment of numerical
identifiers that have no inherent meanings to humans; icons (graphic
symbols to which meaning has been assigned); and names with embedded
meaning, usually for human understanding, that are associated with the
data item's definition and value domain."

btw, Boolean is right there in online books

Searched CASE function:

CASE
WHEN Boolean_express ion THEN result_expressi on
[ ...n ]
[
ELSE else_result_exp ression
]
END

That's an expression, not a store-able data type. Again, Googling
(SQL "Boolean type") turns up http://troels.arvin.dk/db/rdbms/
and in particular http://troels.arvin.dk/db/rdbms/#data_types-boolean

Standard:

"The BOOLEAN type is optional (has feature ID T031), which is a bit
surprising for such a basic type. However, it seems that endless
discussions of how NULL is to be interpreted for a boolean value is
holding BOOLEAN from becoming a core type.

The standard says that a BOOLEAN may be one of the following literals:

* TRUE
* FALSE
* UNKNOWN or NULL (unless prohibited by a NOT NULL constraint)

The DBMS may interpret NULL as equivalent to UNKNOWN. It is unclear
from the specification if the DBMS must support UNKNOWN, NULL or both
as boolean literals. In this author's opinion, you should forget about
the UNKNOWN literal in order to simplify the situation and let the
normal SQL three-way logic apply.

It's defined that TRUE FALSE (true larger than false)."

MSSQL:

"Doesn't support the BOOLEAN type.

Possible alternative type: the BIT type which may have 0 or 1 (or NULL)
as value. If you insert an integer value other than these into a field
of type BIT, then the inserted value will silently be converted to 1.

Rudy Limeback has some notes
[http://searchoracle.techtarget.com/a...tionNResponse/
0,289625,sid41_ cid556536_tax30 1455,00.html]
about oddities with the MSSQL BIT type.

Documentation [http://msdn2.microsoft .com/en-us/library/ms177603.aspx]"

[--CELKO--]

>where can I go to get information on things like "ISO-11179 rules for data elements names" or another site to fix my mindset? <<

My books and Chris Date's stuff deal with the "mindset problem" -- I
like my books better, but his "What, not How" is a good intro to
thinking in declarations rather then procedures. You can Google
ISO-11179 specs, but my SQL PROGRAMMING STYLE has some practical tips
on using the principles and is eaier to read than "Standard-speak".

>btw, Boolean is right there in online books <<

And they are once again wrong. Standard SQL uses <search conditionin
the BNF; we were careful not to say <predicateor <Boolean Expression>
because of the 3-valued logic and the lack of inference rules in SQL.