Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Passing Multiple Values in Case Statement

Author  Topic 

kellog1
Starting Member

35 Posts

Posted - 2010-07-20 : 21:26:39
I am getting an error when I try to pass multiple values in CASE statement...

Here is my Sql query...

declare @CountySort char(1)
declare @FiscalYear char(4)
declare @SortByFiscalYear char(1)
set @CountySort = 'N'
set @SortByFiscalYear = 'Y'

select *
from County a
where PublicationId = 43
order by case @CountySort WHEN 'Y' THEN a.code Else '' end,
case @SortByFiscalYear WHEN 'Y' THEN (2004,2005,2006) else '' end

Help Please!!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-20 : 21:47:59
In SQL, CASE is an expression, not a statement. It can only return a single value.

Can you provide sample data and the expected output you want?
Go to Top of Page

kellog1
Starting Member

35 Posts

Posted - 2010-07-20 : 23:04:18
I am trying to sort @FiscalYear (2000..2016)in my report which is a multi-value parameter. Requirement is to create another parameter that says 'Sort by Fiscal Year' with values (Y/N). But When I use CASE expression in ORDER BY Clause then it does not work if multiples values are entered for @FiscalYear.

Modified query...

declare @CountySort char(1)
declare @FiscalYear char(4)
declare @SortByFiscalYear char(1)
set @CountySort = 'N'
set @SortByFiscalYear = 'Y'

select *
from County a
where PublicationId = 43
order by case @CountySort WHEN 'Y' THEN a.code Else '' end,
case @SortByFiscalYear WHEN 'Y' THEN (@FiscalYear) else '' end

Any clue?
Go to Top of Page

Sachin.Nand

2937 Posts

Posted - 2010-07-21 : 04:08:01
are 2004,2005 & 2006 ur columns?


Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH
Go to Top of Page

kellog1
Starting Member

35 Posts

Posted - 2010-07-21 : 11:25:16
No, They are values in the column.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-21 : 11:56:30
Wouldn't this work then:

select *
from County a
where PublicationId = 43
order by case @CountySort WHEN 'Y' THEN a.code Else '' end,
case @SortByFiscalYear WHEN 'Y' THEN a.FiscalYear else null end
Go to Top of Page

kellog1
Starting Member

35 Posts

Posted - 2010-07-21 : 23:55:11
quote:
Originally posted by robvolk

Wouldn't this work then:

select *
from County a
where PublicationId = 43
order by case @CountySort WHEN 'Y' THEN a.code Else '' end,
case @SortByFiscalYear WHEN 'Y' THEN a.FiscalYear else null end



It works fine if I select just single value...but I have a requirement where the user would like to select more than one value.
Go to Top of Page

Sachin.Nand

2937 Posts

Posted - 2010-07-22 : 00:27:46
quote:
Originally posted by kellog1

No, They are values in the column.



Never heard of data being sorted on values in columns.


Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-22 : 06:31:01
I think you're going to have to post sample data and the expected output so we can understand what you're trying to get.
Go to Top of Page
   

- Advertisement -