View Single Post
  #1 (permalink)  
Old September 22nd, 2004, 11:43 AM
tbaquero tbaquero is offline
Registered User
 
Join Date: Sep 2004
Location: , , Panama.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default ERROR 145: ORDER BY ITEMS... SELECT DISTINCT...

I'm trying to order a stored procedure dynamically using a variable called @OrderBy in a CASE statement on the ORDER BY clause, but i'm continuosly getting this error:

"Error 145: ORDER BY items must appear in the select list if SELECT DISTINCT is specified."

Here's my code:
CREATE PROCEDURE dbo.sp_det_eq_chasis
-- Detalle de Equipos por Tipo de Chasis
@tipochasis varchar(100), @OrderBy varchar(100)
AS
SELECT DISTINCT cs.name0 'Computadora',
ISNULL(ctd.ChassisTypesDesc0,'_Sin Tipo Chasis_') AS 'Tipo Chasis',
username0 'Usuario-Inventario',
user_name0 'Usuario-Last-Logon',
cs.manufacturer0 'Fabricante',
model0 'Modelo',
pr.name0 'Procesador',
CONVERT(varchar(25),CurrentClockSpeed0) 'Velocidad'
FROM GS_COMPUTER_SYSTEM cs
LEFT OUTER JOIN GS_PROCESSOR pr ON cs.resourceid=pr.resourceid
LEFT OUTER JOIN SMS_CHASSIS_TYPES_DESCRIPTIONS ctd
ON cs.chassistypes0=ctd.chassistypes0
WHERE ISNULL(ctd.ChassisTypesDesc0,'_Sin Tipo Chasis_') LIKE RTRIM(@tipochasis)+ '%'
ORDER BY
CASE
WHEN @OrderBy='Computadora' THEN cs.name0
WHEN @OrderBy='Tipo Chasis' THEN ISNULL(ctd.ChassisTypesDesc0,'_Sin Tipo Chasis_')
WHEN @OrderBy='Usuario-Inventario' THEN cs.username0
WHEN @OrderBy='Usuario-Last-Logon' THEN cs.user_name0
WHEN @OrderBy='Fabricante' THEN cs.manufacturer0
WHEN @OrderBy='Modelo' THEN model0
WHEN @OrderBy='Procesador' THEN pr.name0
WHEN @OrderBy='Velocidad' THEN CONVERT(varchar(25),CurrentClockSpeed0)
ELSE cs.name0
END
GO

When i try it without the DISTINCT word it works fine, i have alias for each column and put that alias after the THEN word and it still won't work.

Can anyone help?

Thank You!


TANIA P. BAQUERO
Panama Canal Authority
Student
Reply With Quote