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.companycode = 933 --problem line
ELSE
tblCase.clientcode = @.myClient --problem line
END
And tblCase.status = 'Active'
thank you!!In SQL Server, CASE doesn't conditionally execute statements, it returns an
expression. You might try something like:
WHERE
tblClient.companycode =
CASE
WHEN @.myCompany = 933
THEN 933 ELSE tblClient.companycode END
AND
tblCase.clientcode =
CASE
WHEN @.myCompany < 933
THEN @.myClient ELSE tblCase.clientcode END
AND tblCase.status = 'Active'
--
Hope this helps.
Dan Guzman
SQL Server MVP
<paulmac106@.gmail.comwrote in message
news:1160689816.017843.61740@.i3g2000cwc.googlegrou ps.com...
Quote:
Originally Posted by
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.companycode = 933 --problem line
ELSE
tblCase.clientcode = @.myClient --problem line
END
>
And tblCase.status = 'Active'
>
thank you!!
>
Quote:
Originally Posted by
In SQL Server, CASE doesn't conditionally execute statements, it returns
an expression. You might try something like:
>
WHERE
tblClient.companycode =
CASE
WHEN @.myCompany = 933
THEN 933 ELSE tblClient.companycode END
AND
tblCase.clientcode =
CASE
WHEN @.myCompany < 933
THEN @.myClient ELSE tblCase.clientcode END
AND tblCase.status = 'Active'
Another alternative:
WHERE (NOT(@.myCompany = 933) OR tblClient.companycode = 933)
AND (NOT(@.myCompany <933) OR tblCase.clientcode = @.myClient)
AND tblCase.status = 'Active'|||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.clientcode = @.my_client)
AND Case.foobar_status = 'Active' -- status is too vague|||I don't understand the necessity of the diatribe, but thanks for the
suggestion.|||This first 2 suggestions worked perfectly. The third offering returned
unwanted records.
Thanks for your help. I really appreciate it.|||>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!|||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_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END|||paulmac106@.gmail.com wrote:
Quote:
Originally Posted by
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."
Quote:
Originally Posted by
btw, Boolean is right there in online books
>
Searched CASE function:
>
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
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/...stionNResponse/
0,289625,sid41_cid556536_tax301455,00.html]
about oddities with the MSSQL BIT type.
Documentation [http://msdn2.microsoft.com/en-us/library/ms177603.aspx]"|||>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".
Quote:
Originally Posted by
Quote:
Originally Posted by
>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.sql
No comments:
Post a Comment