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 3 1491
"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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: mike420 |
last post by:
I think everyone who used Python will agree that its syntax is
the best thing going for it. It is very readable and easy
for everyone to learn. But, Python does not a have very good
macro...
|
by: Andy Wawa |
last post by:
Hi,
on a simple HTML (not an ASP!)-Site I try to connect to a sql server
(MS):
<html>
<title>Test</title>
<head>
<script language="javascript">
<!--
Function showForm(){
|
by: Penny |
last post by:
Hi all,
My browser throws this Select Case block back at me pointing out a syntax
error on the line: 'Case < 251', between the word 'Case' and the '<' symbol.
***************************
...
|
by: sunny076 |
last post by:
Hi,
I am confused with the syntax for NOT in MYSQL where clause and wonder
if an expert in MYSQL can enlighten me. There are possibly two places
NOT can go in:
select * from employee_data...
|
by: dharmadam |
last post by:
update prescriber_cross_ref
set preferred_in = 'Y' where prescriber_id =
(with temp1 as
(select A.prescriber_id,A.prescriber_dea_nb,A.prescriber_ama_nb,A.prescriber_aoa_nb
from...
| |
by: C# Learner |
last post by:
Why is C syntax so uneasy on the eye?
In its day, was it _really_ designed by snobby programmers to scare away
potential "n00bs"? If so, and after 50+ years of programming research,
why are...
|
by: danu |
last post by:
I have a structure :
typedef struct{
char magicNum;
int width;
int height;
int maxGrey;
int pixels;
} ImageT;
|
by: Rob R. Ainscough |
last post by:
What sick saddistic person came up with the JavaScript language syntax? And
was it the same person (or group of people) that came up with the HTML
syntax??
OMG, has no one noticed this hopeless...
|
by: Ryan |
last post by:
{"POINTID":77902,"MAPID":762,"LONG":-122.21654892,"LAT":"37.1834331019","CITY":"Boulder
Creek","STATE":"CA","DIST":5745.4}
I get an "invalid label" error...
I'm kinda new to this. Thanks!
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |