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.
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 awhere PublicationId = 43order by case @CountySort WHEN 'Y' THEN a.code Else '' end,case @SortByFiscalYear WHEN 'Y' THEN (2004,2005,2006) else '' endHelp 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? |
|
|
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 awhere PublicationId = 43order by case @CountySort WHEN 'Y' THEN a.code Else '' end,case @SortByFiscalYear WHEN 'Y' THEN (@FiscalYear) else '' endAny clue? |
|
|
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 |
|
|
kellog1
Starting Member
35 Posts |
Posted - 2010-07-21 : 11:25:16
|
No, They are values in the column. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-07-21 : 11:56:30
|
Wouldn't this work then:select *from County awhere PublicationId = 43order by case @CountySort WHEN 'Y' THEN a.code Else '' end,case @SortByFiscalYear WHEN 'Y' THEN a.FiscalYear else null end |
|
|
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 awhere PublicationId = 43order 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. |
|
|
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 |
|
|
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. |
|
|
|
|
|
|
|