sql and dynamic order by problem

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 2000 Forums

Transact-SQL (2000)

sql and dynamic order by problem

Author

Topic

Limbic
Starting Member

15 Posts

Posted - 2007-04-25 : 06:14:27

Hi,

I have a problem with my query and dynamic order by for a aggregate column. It works perfectly fine when I use the case statement for a group by column but as soon as I add a case for the aggregate column it results in an error.



CREATE TABLE #Programms
(
	ProgrammId INT,
	Title NVARCHAR(50)
)

INSERT INTO #Programms VALUES(1, '2007 Ratings')

CREATE TABLE #Ratings
(
	RatingId INT, 
	ProgrammId INT, 
	EmployeeId INT
	
)

INSERT INTO #Ratings VALUES (1, 1, 665 )
INSERT INTO #Ratings VALUES (2, 1, 321 )
INSERT INTO #Ratings VALUES (3, 1, 3145 )
INSERT INTO #Ratings VALUES (4, 1, 64215 )
INSERT INTO #Ratings VALUES (5, 1, 632165 )
INSERT INTO #Ratings VALUES (6, 1, 66325 )



DECLARE @p_SortDir NVARCHAR(4)
SET @p_SortDir = 'desc'

DECLARE @p_SortColumn NVARCHAR(10)
SET @p_SortColumn = 'Inuse' 

SELECT p.ProgrammId, p.Title, CAST(CASE COUNT(r.RatingId) WHEN 0 THEN 0 ELSE 1 END AS BIT) AS InUse 
FROM #Programms p LEFT JOIN 
#Ratings r ON p.ProgrammId = r.ProgrammId    
GROUP BY p.ProgrammId, p.Title

ORDER BY 
	CASE @p_SortDir 
		WHEN 'desc' THEN
			CASE @p_SortColumn 
			WHEN 'ProgrammId' THEN p.ProgrammId
			END
	END 
	DESC
-- Uncommenting the code block below results in an error --> Invalid column name 'InUse'.
/*
	,
	CASE @p_SortDir 
		WHEN 'desc' THEN
			CASE @p_SortColumn 
				WHEN 'InUse' THEN InUse
			END		
	END 
	DESC
*/


-- But this works

SELECT p.ProgrammId, p.Title, CAST(CASE COUNT(r.RatingId) WHEN 0 THEN 0 ELSE 1 END AS BIT) AS InUse 
FROM #Programms p LEFT JOIN 
#Ratings r ON p.ProgrammId = r.ProgrammId    
GROUP BY p.ProgrammId, p.Title
ORDER BY inUse

Is it just not possible to use a dynamic order by for a aggregate column or am I doing something wrong here.

Your help is very much appreciated!

Limbic

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-25 : 07:00:45

I think your only option is to use Derived table:

Select * from
(
	SELECT p.ProgrammId, p.Title, CAST(CASE COUNT(r.RatingId) WHEN 0 THEN 0 ELSE 1 END AS BIT) AS InUse 
	FROM #Programms p LEFT JOIN 
	#Ratings r ON p.ProgrammId = r.ProgrammId    
	GROUP BY p.ProgrammId, p.Title
) t
ORDER BY 
	CASE @p_SortDir 
		WHEN 'desc' THEN
			CASE @p_SortColumn 
			WHEN 'ProgrammId' THEN ProgrammId
			END
	END 
	DESC,
	CASE @p_SortDir 
		WHEN 'desc' THEN
			CASE @p_SortColumn 
				WHEN 'InUse' THEN InUse
			END		
	END 
	DESC

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources