SELECT with CASE :( - SQL Server Forums

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)

SELECT with CASE :(

Author

Topic

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2010-05-06 : 09:17:51

Hey Guru's,

Bad feeling about this one. :( Suspect I'm missing something really dumb:

When I pass @OrderBy as value 2 in the following query:



@DateFrom datetime,
@RegionID int,
@ResortID int,
@OrderBy int = 0

...blah blah blah

SELECT A.AccommodationID, A.AccommodationName
FROM Accommodations A INNER JOIN Resorts ON A.ResortID = Resorts.ResortID INNER JOIN Regions R ON R.RegionID = Resorts.RegionID INNER JOIN AccommodationRates AR ON A.AccommodationID = AR.AccommodationID WHERE (R.RegionID = @RegionID OR @RegionID = 0) AND (A.ResortID = @ResortID OR @ResortID = 0) AND AR.FromDate > @DateFrom 
	ORDER BY 
	
	CASE WHEN @OrderBy = 0 THEN A.Rating
		 WHEN @OrderBy = 1 THEN AR.Price
		 WHEN @OrderBy = 2 THEN	Resorts.ResortName
		 ELSE A.Rating
	END

It throws:

Error converting data type nvarchar to numeric.

PAssing 0 or 1 works fine.

apodemus
Starting Member

30 Posts

Posted - 2010-05-06 : 09:23:44

You shouldn't use different type fields as result in CASE clause, you can convert all values to varchar if you want to use something like this. But remember that varchar are ordering different than integers and if you cast integer as varchar leading zeros will be needed.

apodemus

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-06 : 09:24:25

[code]
CASE WHEN @OrderBy = 0 THEN A.Rating
WHEN @OrderBy = 1 THEN AR.Price
WHEN @OrderBy = 2 THEN Resorts.ResortName
ELSE A.Rating
END
[/code]
all of the possible value return from the CASE must be of the same data type. Convert those numeric to string

KH
[spoiler]Time is always against us[/spoiler]

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2010-05-07 : 03:39:47

Thank you, khtan, oh great honorable and wise yak.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-07 : 03:50:20

alternatively you can also try. This way, you don't have to convert to same data type.


ORDER BY
  CASE WHEN @OrderBy = 0 THEN A.Rating END,
  CASE WHEN @OrderBy = 1 THEN AR.Price END,
  CASE WHEN @OrderBy = 2 THEN Resorts.ResortName END,
  CASE WHEN @OrderBy not in (0, 1, 2) THEN A.Rating END

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources