Wrong syntax in where smth in case ?

[Matik]

Hello all,

I belive, my problem is probably very easy to solve, but still, I
cannot find solution:

declare @i int
declare @z int


create table bubusilala (
[bubu] [int] NOT NULL ,
[gogo] [int] NOT NULL ,
[lala] [varchar] (3) NOT NULL )

insert into bubusilala (bubu,gogo,lala ) values (1,2,'ala')
insert into bubusilala (bubu,gogo,lala ) values (10,20,'aca')
insert into bubusilala (bubu,gogo,lala ) values (100,200,'bbb')
insert into bubusilala (bubu,gogo,lala ) values (11,21,'ccc')
insert into bubusilala (bubu,gogo,lala ) values (12,22,'abc')
insert into bubusilala (bubu,gogo,lala ) values (13,23,'cbd')

set @i = 10
set @z = 2

select * from bubusilala
where bubu in (
case when @i > @z then (1,2)
when @i < @z then (10,13) end)
and gogo like '%a%'

I get error, that statement is wrong in case near ','.
I supose, it is not possible, to get from case a group of values.
But why then, this works:


select * from bubusilala
where bubu in (
case when @i > @z then (1)
when @i < @z then (select gogo from bubusilala) end)
and gogo like '%a%'

This data are totaly simplified.

agrh ... any ideas??

Thank You in advance,

Mateusz

[Simon Hayes]

Re: Wrong syntax in where smth in case ?


"Matik" <marzec@sauron. xo.pl> wrote in message
news:1115392390 .575154.154400@ o13g2000cwo.goo glegroups.com.. .[color=blue]
> Hello all,
>
> I belive, my problem is probably very easy to solve, but still, I
> cannot find solution:
>
> declare @i int
> declare @z int
>
>
> create table bubusilala (
> [bubu] [int] NOT NULL ,
> [gogo] [int] NOT NULL ,
> [lala] [varchar] (3) NOT NULL )
>
> insert into bubusilala (bubu,gogo,lala ) values (1,2,'ala')
> insert into bubusilala (bubu,gogo,lala ) values (10,20,'aca')
> insert into bubusilala (bubu,gogo,lala ) values (100,200,'bbb')
> insert into bubusilala (bubu,gogo,lala ) values (11,21,'ccc')
> insert into bubusilala (bubu,gogo,lala ) values (12,22,'abc')
> insert into bubusilala (bubu,gogo,lala ) values (13,23,'cbd')
>
> set @i = 10
> set @z = 2
>
> select * from bubusilala
> where bubu in (
> case when @i > @z then (1,2)
> when @i < @z then (10,13) end)
> and gogo like '%a%'
>
> I get error, that statement is wrong in case near ','.
> I supose, it is not possible, to get from case a group of values.
> But why then, this works:
>
>
> select * from bubusilala
> where bubu in (
> case when @i > @z then (1)
> when @i < @z then (select gogo from bubusilala) end)
> and gogo like '%a%'
>
> This data are totaly simplified.
>
> agrh ... any ideas??
>
> Thank You in advance,
>
> Mateusz
>[/color]

CASE always returns a single value, so you can't use it in the way you want.
The easiest solution in a simple case is probably just to use IF ... THEN
...., but if your real query is more complicated or you have more possible
combinations of @i and @z, you might find an auxiliary table useful:

create table aux (
bubu int,
scenario int)

insert into aux (bubu, scenario) select 1, 1
insert into aux (bubu, scenario) select 2, 1
insert into aux (bubu, scenario) select 10, 2
insert into aux (bubu, scenario) select 13, 2

select *
from bubusilala b
join aux a
on a.bubu = b.bubu
where a.scenario = case when @i > @z then 1
when @i < @z then 2 end
and lala like '%a%'

You would probably need to modify this to work correctly, depending on what
the keys of your tables are, but this approach might be easier to maintain
than a series of IF... ELSE... blocks.

Simon

[Matik]

Re: Wrong syntax in where smth in case ?

Simon,

Thank you very much for the answer, but unfortunately this does not
solve my problem.

The two parameter @i and @z can be various (this is a problem). But the
possible result in case, lets say you can imagin, is STATIC.

Let's say for two conditions:
Con1: @i < @z
Con2: @i > @z

possible values can be:
Con1: ONLY 1
Con2: ONLY 3 and 4

I need it, to make (corresponding to this two parameters) optimize
select statement, wher depending of this two values I will select once:
- all records, which have Collumn1 equal 1,
- all records, which have Collumn1 equal 3 or 4

I dont think that the solution with aux table, will help me in that
..... or maybe, if using like this:

select * from bubusilala
where bubu in (
case when @i > @z then (1)
when @i < @z then (select gogo from bubusilala) end)
and gogo like '%a%'

query will use all values which are in bubusilala table (or aux table)

Greatings

Mateusz

[Erland Sommarskog]

Re: Wrong syntax in where smth in case ?

Matik (marzec@sauron. xo.pl) writes:[color=blue]
> I supose, it is not possible, to get from case a group of values.
> But why then, this works:
>
>
> select * from bubusilala
> where bubu in (
> case when @i > @z then (1)
> when @i < @z then (select gogo from bubusilala) end)
> and gogo like '%a%'[/color]

(1) is a scalar value. The SELECT query will work if it returns only
one value, or if it is never invoked. Would it execute and return more
than one row, you will get an error. Since gogo is not like 'a%', the
subselect is never invoked.

This repro should give you something to work from:


create table bubusilala (
[bubu] [int] NOT NULL ,
[gogo] [int] NOT NULL ,
[lala] [varchar] (3) NOT NULL )

insert into bubusilala (bubu,gogo,lala ) values (1,2,'ala')
insert into bubusilala (bubu,gogo,lala ) values (10,20,'aca')
insert into bubusilala (bubu,gogo,lala ) values (100,200,'bbb')
insert into bubusilala (bubu,gogo,lala ) values (11,21,'ccc')
insert into bubusilala (bubu,gogo,lala ) values (12,22,'abc')
insert into bubusilala (bubu,gogo,lala ) values (13,23,'cbd')

declare @i integer, @z integer
set @i = 10
set @z = 2

select * from bubusilala
where case when @i > @z and bubu in (1, 2) OR
@i < @z and bubu in (10, 13)
then 1
else 0
end = 1
and lala like '%a%'
go
drop table bubusilala


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp