Help with CASE statement

I need some help with a case statement that I'm trying to write. I wrote thi
s
query a couple of ws ago, but then my sql server had a hardware failure
and I lost all of my stored procs and now I can't remember how I implemented
it. I'm trying to write a dynamic "IN" statement using a case statement, but
I can't get the syntax quite right. Here's an example of what I'm trying to
do (using different data). I realize this could be simplified using a decode
table, but that isn't an option in this case. My problem is how I'm handling
the part of the statement after each "THEN" statement (I might have used a
Convert statement, but I don't remember). I think it's reading my values as
one long string (i.e.-'Apples, Oranges, Bananas) instead of separate values
(i.e.-'Apples', 'Oranges', 'Bananas'). Thanks in advance for your help.
SELECT column1
FROM tblMyTable
WHERE column2 IN(
CASE @.inputVariable
WHEN 'Fruit' THEN 'Apples' + ',' + 'Oranges' + ', ' + 'Bananas'
WHEN 'Vegetable' THEN 'Corn' + ', ' + 'Green Beans'
END
)You cannot use in like that..
try this
SELECT column1
FROM tblMyTable
WHERE (@.inputVariable = 'fruit' and
column2 IN( 'Apples' ,'Oranges' ,'Bananas'))
or
(@.inputVariable = 'Vegetable' and column2 IN('Corn' ,'Green Beans'))
Hope this helps.
"Dustin" wrote:

> I need some help with a case statement that I'm trying to write. I wrote t
his
> query a couple of ws ago, but then my sql server had a hardware failure
> and I lost all of my stored procs and now I can't remember how I implement
ed
> it. I'm trying to write a dynamic "IN" statement using a case statement, b
ut
> I can't get the syntax quite right. Here's an example of what I'm trying t
o
> do (using different data). I realize this could be simplified using a deco
de
> table, but that isn't an option in this case. My problem is how I'm handli
ng
> the part of the statement after each "THEN" statement (I might have used a
> Convert statement, but I don't remember). I think it's reading my values a
s
> one long string (i.e.-'Apples, Oranges, Bananas) instead of separate value
s
> (i.e.-'Apples', 'Oranges', 'Bananas'). Thanks in advance for your help.
> SELECT column1
> FROM tblMyTable
> WHERE column2 IN(
> CASE @.inputVariable
> WHEN 'Fruit' THEN 'Apples' + ',' + 'Oranges' + ', ' + 'Bananas'
> WHEN 'Vegetable' THEN 'Corn' + ', ' + 'Green Beans'
> END
> )|||Hi, Justin
The best way would be to use a table for this, but since you say that
it's not an option, try something like this:
SELECT column1
FROM tblMyTable
WHERE @.inputVariable='Fruit' AND column2 IN
('Apples','Oranges','Bananas')
OR @.inputVariable='Vegetable' AND column2 IN ('Corn','Green Beans')
Razvan|||Dustin,
CASE is an expression (not a statement) that returns a scalar value. It
cannot return a set of values. In your code the CASE expression generates a
single character string value made of the concatenated elements. e.g., when
@.inputVariable = 'Fruit', you logically get:
SELECT column1
FROM tblMyTable
WHERE column2 IN('Apples, Oranges, Bananas');
Which is logically equivalent to:
SELECT column1
FROM tblMyTable
WHERE column2 = 'Apples, Oranges, Bananas';
Which probably isn't what you're after.
Here are a couple of options (not tested); I suspect the former will perform
better:
IF @.inputVariable = 'Fruit'
SELECT column1
FROM tblMyTable
WHERE column2 IN('Apples', 'Oranges', 'Bananas');
ELSE IF @.inputVariable = 'Vegetable'
SELECT column1
FROM tblMyTable
WHERE column2 IN('Corn', 'Green Beans');
SELECT column1
FROM tblMyTable
WHERE (@.inputVariable = 'Fruit'
AND column2 IN('Apples', 'Oranges', 'Bananas'))
OR (@.inputVariable = 'Vegetable'
AND column2 IN('Corn', 'Green Beans'));
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Dustin" <Dustin@.discussions.microsoft.com> wrote in message
news:475EA188-4D13-4613-8478-E9C268572184@.microsoft.com...
>I need some help with a case statement that I'm trying to write. I wrote
>this
> query a couple of ws ago, but then my sql server had a hardware failure
> and I lost all of my stored procs and now I can't remember how I
> implemented
> it. I'm trying to write a dynamic "IN" statement using a case statement,
> but
> I can't get the syntax quite right. Here's an example of what I'm trying
> to
> do (using different data). I realize this could be simplified using a
> decode
> table, but that isn't an option in this case. My problem is how I'm
> handling
> the part of the statement after each "THEN" statement (I might have used a
> Convert statement, but I don't remember). I think it's reading my values
> as
> one long string (i.e.-'Apples, Oranges, Bananas) instead of separate
> values
> (i.e.-'Apples', 'Oranges', 'Bananas'). Thanks in advance for your help.
> SELECT column1
> FROM tblMyTable
> WHERE column2 IN(
> CASE @.inputVariable
> WHEN 'Fruit' THEN 'Apples' + ',' + 'Oranges' + ', ' + 'Bananas'
> WHEN 'Vegetable' THEN 'Corn' + ', ' + 'Green Beans'
> END
> )|||of course with the braces in place ;)
--
"Razvan Socol" wrote:

> Hi, Justin
> The best way would be to use a table for this, but since you say that
> it's not an option, try something like this:
> SELECT column1
> FROM tblMyTable
> WHERE @.inputVariable='Fruit' AND column2 IN
> ('Apples','Oranges','Bananas')
> OR @.inputVariable='Vegetable' AND column2 IN ('Corn','Green Beans')
> Razvan
>|||I believe it will work fine without the extra parenthesis, but the
parenthesis will make it easier to read and leave no question as to what is
intended.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:57F875E5-4A0C-4DBA-956D-7EE9640E5D1A@.microsoft.com...
> of course with the braces in place ;)
> --
>
>
> "Razvan Socol" wrote:
>|||boy.. thats a news to me.. Always thought AND and OR had the same precedence
.
thanks for pointing that out. Though I will never use it without braces :)
--
"Jim Underwood" wrote:

> I believe it will work fine without the extra parenthesis, but the
> parenthesis will make it easier to read and leave no question as to what i
s
> intended.
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:57F875E5-4A0C-4DBA-956D-7EE9640E5D1A@.microsoft.com...
>
>|||I never use it without parenthesis either. It just gets too confusing, and
too easy to get the wrong results. That and I still forget sometimes that
AND has a higher precedence than OR, so the parens protect me from myself.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:7EAE1F57-00B1-4002-8B32-E6BCC8DDCB84@.microsoft.com...
> boy.. thats a news to me.. Always thought AND and OR had the same
precedence..
> thanks for pointing that out. Though I will never use it without braces :)
> --
>
>
> "Jim Underwood" wrote:
>
is
that|||Thank you all for your help. I was able to solve the problem using the
following suggested syntax:
SELECT column1
FROM tblMyTable
WHERE (@.inputVariable = 'Fruit'
AND column2 IN('Apples', 'Oranges', 'Bananas'))
OR (@.inputVariable = 'Vegetable'
AND column2 IN('Corn', 'Green Beans'));

Help with a stored procedure

Hi,

Im very new to this posting business so if Im posting in the wrong place or make a faux pa, I apologise

Ive been stuck for a couple of days with the following error, any help would be great!

I created a simple stored procedure which worked fine, then added parameters and it all fell to pieces.

Incorrect syntax near 'StoredProcedure1'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near 'StoredProcedure1'.

ALTER PROCEDUREdbo.StoredProcedure1

@.MYValuevarchar(50)

AS

BEGIN

SELECT[User].*FROM[User]WHERE[User].UserName = @.MyValueEND

>>>>>>>>>>>>>>>>>>>>>

And I call the procedure here:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"SelectCommand=StoredProcedure1><SelectParameters><asp:SessionParameterName="MYValue"SessionField="name"/></SelectParameters>

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

I've tried fiddling with pretty much everything, but nothing has worked yet.

Cheers

Dan

Can you test this:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT[User].*FROM[User]WHERE[User].UserName = @.MyValue" >

<SelectParameters>

<asp:SessionParameterName="MYValue"SessionField="name" type="string"/>

</SelectParameters>

</asp:SqlDataSource>

Or you can test this to see whether your SP works:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="StoredProcedure1" SelectCommandType="StoredProcedure" >

<SelectParameters>

<asp:SessionParameterName="MYValue"SessionField="name" type="string"/>

</SelectParameters>

|||

Whoah, that second suggestion fixed all! Im so stoked,

Cheers mate

Dan

Help with a delete query

Hi

i need help with this task i can't think how to do better.
I have a table with MyField1, MyField2, Cost, SomeOtherField

For each couple of MyField1, MyField2 i have several rows.
I need to delete some rows so that:
- for each couple of MyField1, MyField2 i have at most @.MaxNumber rows remaining and
- for each pair MyField1,MyField2 the Cost in the remaining rows is never more than a certain coefficient @.K times the cost of the least costly row of the pair (each MyField1,MyField2 has its own minimum cost).

I did this easily with a Cursor but i needed better performances.
I tried with this:

delete from MyTable from MyTable as p1 where
0 = (select (1 + sign(@.MaxNumber -1-count(*))) * (1 + sign(min(p2.Cost) * @.K- p1.Cost) )
from MyTable p2 where p2.MyField1 = p1.MyField1 and p2.MyField2 = p1.MyField2 and p2.Cost< p1.Cost)

What's happening here ? for each row i count how many rows are there with the same MyFields that have cost less than @.K*MinValue , i multiply the two difference between the value i need and the real value and only when one of these values are zero i know that the product is zero and so i can choose the row to be deleted. Note that the double "FROM" in the query is no mistake.
This is terribly involuted, it works better than the cursor but i am sure there must be a better way to do this !!

Could You please help me ?

Thankx

Wentuhi,

can you post a sample data?|||

Try This...

Code Snippet

Delete

From mytable

From mytable as Main

Join

(

Select p1.*

From mytable p1

Join mytable p2 Onp2.MyField1 = p1.MyField1

and p2.MyField2 = p1.MyField2

and p2.Cost< p1.Cost

Group By

p1.MyField1, p1.MyField2, p1.Cost

Having

(1 + sign(@.MaxNumber -1-count(*))) * (1 + sign(min(p2.Cost) * @.K- p1.Cost)) =0

) as Sub

OnSub.MyField1 = Main.MyField1 and Sub.MyField2 = Main.MyField2 and Sub.Cost = Main.Cost

|||

Manni's query looks better; nonetheless, I put together a mockup. The mockup used the SMALL_ITERATOR and DBO.RAND() objects that can be found here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1330536&SiteID=1

The table definition that I used is:

create table dbo.myTable
( rid integer,
myField1 integer,
myField2 integer,
cost numeric (9,2),

constraint pk_myTable primary key (myField1, myField2, cost, rid)
)
go

insert into dbo.myTable
select iter,
1 + (iter/35),
5*dbo.rand(),
33*dbo.rand() + 33*dbo.rand()
+ 34*dbo.rand()
from small_iterator (nolock)
where iter <= 40
select * from dbo.myTable

And the query is:

--select * from myTable where myField1 = 1 and myField2 = 1 order by cost
/*
rid myField1 myField2 cost
-- -- --
8 1 1 32.97
6 1 1 42.40
25 1 1 44.39
3 1 1 50.12
34 1 1 56.03
5 1 1 58.37
17 1 1 64.41
13 1 1 65.66
24 1 1 75.05
*/

declare @.maxRows integer set @.maxRows = 4
declare @.boundary numeric (6,2) set @.boundary = 1.5

;with theMinCost as
( select myField1,
myField2,
@.boundary * min(cost) as maxCost
from myTable
group by myField1, myField2
), seqTable as
( select row_number () over
( partition by myField1, myField2
order by cost, rid
) as seq,
myField1,
myField2,
cost,
rid
from myTable
), candidates as
( select a.myField1,
a.myField2,
a.seq,
a.cost,
b.maxCost,
a.rid
from seqTable a
inner join theMinCost b
on a.myField1 = b.myField1
and a.myField2 = b.myField2
and ( a.seq > @.maxRows or
a.cost > b.maxCost
)
)

--select * from theMinCost where myField1=1 and myField2=1
/*
myField1 myField2 maxCost
-- -- -
1 1 49.4550
*/

/*
select * from candidates
where myField1 = 1
and myField2 = 1

myField1 myField2 seq cost maxCost rid
-- -- -- -- --
1 1 4 50.12 49.4550 3
1 1 5 56.03 49.4550 34
1 1 6 58.37 49.4550 5
1 1 7 64.41 49.4550 17
1 1 8 65.66 49.4550 13
1 1 9 75.05 49.4550 24
*/

delete from myTable
from myTable a
inner join candidates b
on a.myField1 = b.myField1
and a.myField2 = b.myField2
and a.rid = b.rid

select * from myTable where myField1 = 1 and myField2 = 1 order by cost

/*
rid myField1 myField2 cost
- -- -- --
8 1 1 32.97
6 1 1 42.40
25 1 1 44.39
*/

|||Wentu,

If you are using SQL Server 2005, try this. It may be efficient and relatively easy to understand and maintain. (I'm using Kent's table structure.)

Code Snippet

with WithMins(rid, myField1, myField2, minCost, cost) as (
select
rid,
myField1,
myField2,
min(cost) over (
partition by myField1, myField2
) as minCost,
cost
from myTable
), Cheaps(rid, myField1, myField2, cost, minCost, n) as (
select
rid,
myField1,
myField2,
cost,
minCost,
row_number() over (
partition by myField1, myField2
order by myField1, myField2, cost
-- cost <= @.K*minCost rows are numbered lowest
) as n
from WithMins
)
delete from Cheaps
where cost > @.K*minCost
or n > @.MaxNumber

Steve Kass
Drew University
http://www.stevekass.com
|||:-) (Admiring the work)|||Hi all

let me understand all Your codes, try them , and i'll let You know.
I want to thankx all of You for Your efforts, time and help !
c ya soon

Wentu