Row_Number() Order By Conversion issue.

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)

Row_Number() Order By Conversion issue.

Author

Topic

malawneh
Starting Member

24 Posts

Posted - 2009-03-04 : 12:22:50

I am recieving the following error...

Msg 245, Level 16, State 1, Line 676
Conversion failed when converting the varchar value 'GUEVARA' to data type int.

On a SQLSERVER 2005 Server. I want to be able to sort on any of the available fields (FamilyID,ParentID,FamilyName,ParentName) The first 2 are INT datatypes and the second 2 are Varchar(50). The query below works fine when oredr by FamilyID/ParentID are selected. But when FamilyName/ParentName is selected the error displayed above is returned. Is there a limitation when using the "Row_Number()" function to only order by INT data types? Is there a work around for this? Any help appreciated. Thank you all.
DECLARE @Page INT
, @PageSize INT
, @GUID VarChar(50)
, @SortBy VarChar(50)
, @OrderBy VarChar(4)
, @TotalPages INT

SELECT @Page = 1
, @PageSize = 10
, @GUID = '123214544563dfhsghdfh'
, @SortBy = 'FamilyName'
, @OrderBy = 'DESC'

SELECT @TotalPages =COUNT(*)/@PageSize
FROM dbo.tblFamilySearchResult
WHERE [GUID] = @GUID

;
IF @OrderBy = 'ASC'
BEGIN
WITH Family AS
(
SELECT FamilyID
, FamilyName
, ParentID
, ParentName
, ROW_NUMBER() OVER(ORDER BY CASE @SortBy WHEN 'ParentName' THEN ParentName
WHEN 'FamilyName' THEN FamilyName
WHEN 'FamilyID' THEN FamilyID
WHEN 'ParentID' THEN ParentID
END ASC) AS 'RowNumber'
, @TotalPages AS 'Total Pages'
FROM dbo.tblFamilySearchResult
WHERE [GUID] = @GUID
)

SELECT *
FROM Family
WHERE RowNumber BETWEEN (@Page -1) * @PageSize + 1 AND @Page * @PageSize
END
ELSE
BEGIN
WITH Family AS
(
SELECT FamilyID
, FamilyName
, ParentID
, ParentName
, ROW_NUMBER() OVER(ORDER BY CASE @SortBy WHEN 'FamilyName' THEN FamilyName
WHEN 'ParentName' THEN ParentName
WHEN 'FamilyID' THEN FamilyID
WHEN 'ParentID' THEN ParentID
END DESC) AS 'RowNumber'
, @TotalPages AS 'Total Pages'
FROM dbo.tblFamilySearchResult
WHERE [GUID] = @GUID
)

SELECT *
FROM Family
WHERE RowNumber BETWEEN (@Page -1) * @PageSize + 1 AND @Page * @PageSize
END

Sample Data for tblFamilySearchResult...
GUID FamilyID FamilyName ParentID ParentName
123214544563dfhsghdfh 9972 GUEVARA 44978 GUEVARA, JUANA
123214544563dfhsghdfh 9973 OLIA 44983 OLIA, JENNIFER
123214544563dfhsghdfh 9974 COOPER 44984 COOPER, WILLIAM
123214544563dfhsghdfh 9975 BOYD 44985 BOYD, LINDAY
123214544563dfhsghdfh 9976 CASTAN 44990 CASTAN, HEATHER
123214544563dfhsghdfh 9977 PACHECO 44995 PACHECO, NICOLAS
123214544563dfhsghdfh 9978 WOOD 44997 WOOD, BEVERLY
123214544563dfhsghdfh 9979 GUIZAR 44999 GUIZAR, MARGARITA
123214544563dfhsghdfh 9980 GULLEY 45265 GULLEY, PORSHA
123214544563dfhsghdfh 9981 DANISH 45712 DANISH, AMIR
123214544563dfhsghdfh 9983 ZHOU 45638 ZHOU, LING

Michael Alawneh, DBA

malawneh
Starting Member

24 Posts

Posted - 2009-03-04 : 12:41:52

Just as a note the following does work with the above referenced table. Just not with the ORDER BY CASE...

SELECT FamilyName
, ROW_NUMBER() OVER(ORDER BY FamilyName ASC)
FROM dbo.tblFamilySearchResult

Michael Alawneh, DBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 12:42:52

try like this


DECLARE @Page INT
, @PageSize INT
, @GUID VarChar(50)
, @SortBy VarChar(50)
, @OrderBy	VarChar(4)
, @TotalPages INT

SELECT @Page = 1
, @PageSize = 10
, @GUID	= '123214544563dfhsghdfh'
, @SortBy = 'FamilyName'
, @OrderBy	= 'DESC'

SELECT @TotalPages =COUNT(*)/@PageSize
FROM dbo.tblFamilySearchResult
WHERE [GUID] = @GUID


WITH Family AS
(
SELECT FamilyID
, FamilyName
, ParentID
, ParentName
, ROW_NUMBER() OVER(ORDER BY CASE  WHEN @SortBy='ParentName' AND  @OrderBy = 'ASC' THEN ParentName 
WHEN 'FamilyName'AND @OrderBy = 'ASC' THEN FamilyName ELSE 1 END ASC,
CASE @SortBy WHEN 'FamilyID' AND  @OrderBy = 'ASC' THEN FamilyID
WHEN 'ParentID' AND  @OrderBy = 'ASC' THEN ParentID ELSE 1
END ASC,CASE  WHEN @SortBy='ParentName' AND  @OrderBy = 'DESC' THEN ParentName 
WHEN 'FamilyName'AND @OrderBy = 'DESC' THEN FamilyName ELSE 1 END DESC,
CASE @SortBy WHEN 'FamilyID' AND  @OrderBy = 'DESC' THEN FamilyID
WHEN 'ParentID' AND  @OrderBy = 'DESC' THEN ParentID ELSE 1
END DESC) AS 'RowNumber'
, @TotalPages AS 'Total Pages'
FROM dbo.tblFamilySearchResult
WHERE [GUID] = @GUID
)

SELECT *
FROM Family
WHERE RowNumber BETWEEN (@Page -1) * @PageSize + 1 AND @Page * @PageSize

malawneh
Starting Member

24 Posts

Posted - 2009-03-04 : 13:15:09

Thank you for your help visakh16. Using your solution as a sample I have come up with the code below. Wich I have tested and works as expected. Thank you visakh16!

DECLARE @Page INT
, @PageSize INT
, @GUID VarChar(50)
, @SortBy VarChar(50)
, @OrderBy VarChar(4)
, @TotalPages INT

SELECT @Page = 1
, @PageSize = 10
, @GUID = '123214544563dfhsghdfh'
, @SortBy = 'ParentID'
, @OrderBy = 'DESC'

SELECT @TotalPages =COUNT(*)/@PageSize
FROM dbo.tblFamilySearchResult
WHERE [GUID] = @GUID
;
WITH Family AS
(
SELECT FamilyID
, FamilyName
, ParentID
, ParentName
, ROW_NUMBER() OVER(ORDER BY CASE WHEN @SortBy='ParentName' AND @OrderBy = 'ASC' THEN ParentName END,
CASE WHEN @SortBy='FamilyName' AND @OrderBy = 'ASC' THEN FamilyName END,
CASE WHEN @SortBy='FamilyID' AND @OrderBy = 'ASC' THEN FamilyID END,
CASE WHEN @SortBy='ParentID' AND @OrderBy = 'ASC' THEN ParentID END,
CASE WHEN @SortBy='ParentName' AND @OrderBy = 'DESC' THEN ParentName END DESC,
CASE WHEN @SortBy='FamilyName' AND @OrderBy = 'DESC' THEN FamilyName END DESC,
CASE WHEN @SortBy='FamilyID' AND @OrderBy = 'DESC' THEN FamilyID END DESC,
CASE WHEN @SortBy='ParentID' AND @OrderBy = 'DESC' THEN ParentID END DESC)
AS 'RowNumber'
, @TotalPages AS 'Total Pages'
FROM dbo.tblFamilySearchResult
WHERE [GUID] = @GUID
)

SELECT *
FROM Family
WHERE RowNumber BETWEEN (@Page -1) * @PageSize + 1 AND @Page * @PageSize

Michael Alawneh, DBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 13:21:03

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources