469,936 Members | 2,466 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,936 developers. It's quick & easy.

Wrong syntax in where smth in case ?

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

Jul 23 '05 #1
3 1359

"Matik" <ma****@sauron.xo.pl> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
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


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
Jul 23 '05 #2
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

Jul 23 '05 #3
Matik (ma****@sauron.xo.pl) writes:
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%'


(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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

699 posts views Thread by mike420 | last post: by
6 posts views Thread by Andy Wawa | last post: by
2 posts views Thread by sunny076 | last post: by
4 posts views Thread by dharmadam | last post: by
177 posts views Thread by C# Learner | last post: by
16 posts views Thread by danu | last post: by
3 posts views Thread by Rob R. Ainscough | last post: by
5 posts views Thread by Ryan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.