mssql: case statements with multiple columns

A standard case statement in MSSQL server looks like:
...WHERE s.idMarket =
CASE @.Mode
WHEN 'Sales' THEN 1
ELSE 2
END
but I want a similar statement to use multiple values of 'idMarket' for
each value of '@.Mode'
e.g.
...WHERE s.idMarket =
CASE @.Mode
WHEN 'Sales' THEN 1, 3, 5, 6
ELSE 2, 4, 7, 8, 9
END
is this possible through an mssql query?
cheers.
fiddlewidawiddumYou can't use case that way.
For this may be you can rephrase the where clause this way..
WHERE (@.Mode = 'Sales' and s.idMarket in (1, 3, 5, 6)) or (@.Mode <> 'Sales'
and s.idMarket in (2, 4, 7, 8, 9))
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Stimp" wrote:

> A standard case statement in MSSQL server looks like:
> ...WHERE s.idMarket =
> CASE @.Mode
> WHEN 'Sales' THEN 1
> ELSE 2
> END
> but I want a similar statement to use multiple values of 'idMarket' for
> each value of '@.Mode'
> e.g.
> ...WHERE s.idMarket =
> CASE @.Mode
> WHEN 'Sales' THEN 1, 3, 5, 6
> ELSE 2, 4, 7, 8, 9
> END
> is this possible through an mssql query?
> cheers.
> --
> fiddlewidawiddum
>|||On Mon, 19 Jun 2006 Omnibuzz <Omnibuzz@.discussions.microsoft.com> wrote:
> You can't use case that way.
> For this may be you can rephrase the where clause this way..
> WHERE (@.Mode = 'Sales' and s.idMarket in (1, 3, 5, 6)) or (@.Mode <> 'Sales
'
> and s.idMarket in (2, 4, 7, 8, 9))
that actually works great.. thanks!
--
fiddlewidawiddum|||>> A standard case statement in MSSQL server looks like: <<
There is no CASE statement in SQL; there is a CASE expression. An
expression returns a scalar value of a known data type.
Again, you missed the concept "expression" and "scalar" so the
question is wrong. Then even if it would have worked, you would have
to use IN() and not = in the predicate. Besides not bothering to learn
SQL, why did you violate ISO-11179 by putting "id-" as a prefix? Why
did you use a vague nmae like "mode" --mode of what'
Try something like this:
..WHERE CASE
WHEN S.market_id IN (1, 3, 5, 6)
AND @.mode = 'sales'
THEN 'T'
WHEN S.market_id IN (2, 4, 7, 8, 9)
AND @.mode <> 'sales'--mode of what'
THEN 'T' ELSE 'F' END = 'T';