July 23, 2008 at 3:57 pm
I'm new to the over() function. I tried searching the forums first, but was not able to find any threads describing the particular problem I'm having. Here's the code and the error. Any help will be greatly appreciated.
setting the varible @OrderBy to 'Id' or 'data' works great, but if I set it to 'name' I get the error. Is over(order by xxx) strictly for numerical data types????
Thanks.
-Mario
if exists (select 1 from sys.objects where object_Id = object_id('dbo.test') and objectproperty(object_id, 'isusertable') = 1)
drop table dbo.test
go
create table dbo.test (id int not null, name varchar(50) not null, data varchar(50) not null)
go
insert dbo.test(id, name, data) values (0, 'zero', '111')
insert dbo.test(id, name, data) values (1, 'one', '110')
insert dbo.test(id, name, data) values (2, 'two', '101')
insert dbo.test(id, name, data) values (3, 'three', '100')
insert dbo.test(id, name, data) values (4, 'four', '011')
insert dbo.test(id, name, data) values (5, 'five', '010')
insert dbo.test(id, name, data) values (6, 'six', '001')
insert dbo.test(id, name, data) values (7, 'seven', '000')
go
set ansi_nulls off
set concat_null_yields_null off
declare @Id int, @name varchar(50), @data varchar(50)
declare @PageIndex int, @PageSize int
declare @StartIndex int, @EndIndex int
declare @OrderBy varchar(50)
select @PageIndex = 1, @PageSize = 4
select @OrderBy = 'name'
select @StartIndex = ((@PageIndex - 1) * @PageSize) + 1
select @EndIndex = ((@PageIndex - 1) * @PageSize) + 1 + @PageSize
select @Id = null, @name = null, @data = null;
with DerivedT as
(
select
[Id]
, [name]
, [data]
, RowNum = row_number() over
(
order by
(
case @OrderBy
when 'Id' then [Id]
when 'name' then [name]
when 'data' then [data]
else [Id]
end
)
)
from
dbo.test
where
[Id] = coalesce(@Id, [Id])
and [name] = coalesce(@Name, [name])
and [data] = coalesce(@data, [data])
)
select
*
from
DerivedT
where
RowNum >= @StartIndex
and RowNum < @EndIndex;
if exists (select 1 from sys.objects where object_Id = object_id('dbo.test') and objectproperty(object_id, 'isusertable') = 1)
drop table dbo.test
go
Msg 245, Level 16, State 1, Line 16
Conversion failed when converting the varchar value 'zero' to data type int.
July 23, 2008 at 4:02 pm
The problem is that you are trying to use the CASE function to dynamically switch what data type it is returning and you cannot do that. All of the data types returned by a single Case expression (or any single function instance) must all be compatible.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 23, 2008 at 5:44 pm
OK, I get that, but why then, it works when @OrderBy = 'Id' which is an int, and also works when @OrderBy = 'data' which is a varchar, but not 'name' which is also a varchar? Is it because it can cast column test.data to int (which happens to be an int)?
I don't have access to a SQL Server at the moment, but if that's true, then using cast(id as varchar) and cast(data as varchar) should work, since name is varchar.
Thanks.
-Mario
July 23, 2008 at 6:53 pm
Yes, it is trying to convert everything to int, and it is fine as long as that works.
I agree, you should Cast/Convert evrything to the same data type.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 23, 2008 at 7:17 pm
You were right.
Changing
case @OrderBy
when 'Id' then [Id]
when 'name' then [name]
when 'data' then [data]
else [Id]
end
to
case @OrderBy
when 'Id' then cast([Id] as varchar)
when 'name' then cast([name] as varchar)
when 'data' then cast([data] as varchar)
else cast([Id] as varchar)
end
works.
How come this is not documented in BOL? :crazy:
Thanks for your help.
-Mario
July 23, 2008 at 8:01 pm
It is, but in different places. You sort of have to put it together.
The key to another way to view it is to remember two facts: 1) CASE is a function and 2) SQL statments are compiled.
Because everything is complied, that means that every expression must be reduced to a definite data type (can't be compiled otherwise), and this means the every operator, and function instance in a SQL statment must be reducible to a single deterministic data type. There is no true polymorphism in SQL so even functions intended to return any data type, like CAST and CONVERT, still have to reduce to only one fixed data type for any single instance of that function in an expression.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 23, 2008 at 8:27 pm
It was just surprising because I could swear that the following code used to work in sql2k:
declare @OrderBy varchar(50)
select @OrderBy = 'name'
select
Id
, [name]
, data
from
dbo.test
order by
case @OrderBy
when 'Id' then 1
when 'name' then 2
when 'data' then 3
end
I did not have a sql2 instance to try it out, but I remember writing a new version of sp_who2 that I used to pass in a parameter specifying what column I wanted the result set to be ordered by.
But good to know. Thanks again.
-Mario
July 23, 2008 at 10:46 pm
nospam (7/23/2008)
It was just surprising because I could swear that the following code used to work in sql2k:
Entirely possible. I believe that something changed from 2000 to 2005 in the type precedence and the coercion process of Case functions.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply