Case statement in a where clause

Hi All,
I have a bit of a delema here, that I know someone knows how to do it
properly.
If the value of DSP_IN_HOUSE = 1, I need the where clause to say:
where DSP_IN_HOUSE_IN = F.CollectorDesc
the code I am using is:
left outer join tlkCollector F
on case when DSP_IN_HOUSE = 1 then DSP_IN_HOUSE_IN = F.CollectorDesc
when DSP_IN_HOUSE = 0 then DSP_RE_OPEN_IN = F.CollectorDesc
end
Is this possible without building the where clause dynamically?
TIA,
JoeCASE is an expression that returns a value; it is not a logic flow operator
that allows you to change the meaning and semantics of a query. Assuming
F.CollectorDesc, DSP_IN_HOUSE_IN and DSP_RE_OPEN_IN (curse the people who
named your columns!) are the same data type:
ON F.CollectorDesc = CASE DSP_IN_HOUSE
WHEN 1 THEN DSP_IN_HOUSE_IN
WHEN 0 THEN DSP_RE_OPEN_IN
END
By the way, you should really get in the habit of using aliases on all of
the tables in your join/where clauses. Leaving them out just because
they're the only table with such a column is confusing for other readers /
maintainers of the code, and could bite you if such a column is added to one
of the tables later.
A
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:B8391A53-D820-4EF7-8BA8-751A341E0EED@.microsoft.com...
> Hi All,
> I have a bit of a delema here, that I know someone knows how to do it
> properly.
> If the value of DSP_IN_HOUSE = 1, I need the where clause to say:
> where DSP_IN_HOUSE_IN = F.CollectorDesc
> the code I am using is:
> left outer join tlkCollector F
> on case when DSP_IN_HOUSE = 1 then DSP_IN_HOUSE_IN = F.CollectorDesc
> when DSP_IN_HOUSE = 0 then DSP_RE_OPEN_IN = F.CollectorDesc
> end
> Is this possible without building the where clause dynamically?
> TIA,
> Joe
>|||You cannot use case statments like that. A case statement returns a value.
Honestly, when you run into problems like this in your query you know it is
time to reconsider your design :-)
John
"jaylou" wrote:

> Hi All,
> I have a bit of a delema here, that I know someone knows how to do it
> properly.
> If the value of DSP_IN_HOUSE = 1, I need the where clause to say:
> where DSP_IN_HOUSE_IN = F.CollectorDesc
> the code I am using is:
> left outer join tlkCollector F
> on case when DSP_IN_HOUSE = 1 then DSP_IN_HOUSE_IN = F.CollectorDesc
> when DSP_IN_HOUSE = 0 then DSP_RE_OPEN_IN = F.CollectorDesc
> end
> Is this possible without building the where clause dynamically?
> TIA,
> Joe
>|||Thank you for the help! I did know this but my brain went on vacation for
some reason. I guess I needed an extra pair of eyes look at what I thought
looked right. :)
I normally use aliases when I create SPs this is a one time converstion from
an Access Application into a .net App with a SQL Backend.
And YES I do curse them out all the time. this is part of my very large
project of cleaning up all tables and procedure here.
Aren't I a lucky one' :)
"Aaron Bertrand [SQL Server MVP]" wrote:

> CASE is an expression that returns a value; it is not a logic flow operato
r
> that allows you to change the meaning and semantics of a query. Assuming
> F.CollectorDesc, DSP_IN_HOUSE_IN and DSP_RE_OPEN_IN (curse the people who
> named your columns!) are the same data type:
>
> ON F.CollectorDesc = CASE DSP_IN_HOUSE
> WHEN 1 THEN DSP_IN_HOUSE_IN
> WHEN 0 THEN DSP_RE_OPEN_IN
> END
>
> By the way, you should really get in the habit of using aliases on all of
> the tables in your join/where clauses. Leaving them out just because
> they're the only table with such a column is confusing for other readers /
> maintainers of the code, and could bite you if such a column is added to o
ne
> of the tables later.
> A
>
> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
> news:B8391A53-D820-4EF7-8BA8-751A341E0EED@.microsoft.com...
>
>sql