p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2000 (http://p2p.wrox.com/forumdisplay.php?f=20)
-   -   ERROR 145: ORDER BY ITEMS... SELECT DISTINCT... (http://p2p.wrox.com/showthread.php?t=18786)

tbaquero September 22nd, 2004 11:43 AM

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

happygv September 22nd, 2004 12:55 PM

I just played around it, not sure what the reason is, it doesn't work when DISTINCT is used along with CASE in the order by clause. It works perfect without CASE in ORDER BY Clause or, without DISTINCT in SELECT list.

Hope you should avoid one of them.

_________________________
- Vijay G
Strive for Perfection

tbaquero September 22nd, 2004 01:03 PM

I don't think i can avoid one of those because i need it to be distinct so i don't get repeated records in the asp recordset i'm showing, and because my stored procedure is so dynamic i can't avoid using the case in the orderby clause either. In fact i'm using the case in the orderby clause statement because in the asp page where i'm executing the stored procedure i have links on the column heading so the person can sort the records using that field.

Still waiting for some help...

TANIA P. BAQUERO
Panama Canal Authority
Student

pgtips September 23rd, 2004 04:06 AM

you can get around this limitation by putting the SELECT DISTINCT part into a view. Then you can just do SELECT * FROM YourView WHERE ... ORDER BY <<your dynamic part>>

tbaquero September 27th, 2004 07:57 AM

I have 26 queries to order by. Do you mean that i need to have 1 SELECT DISTINCT view for each?

TANIA P. BAQUERO
Panama Canal Authority
Student

tbaquero September 28th, 2004 01:15 PM

I solve the problem by ordering the query in the recordset and not in the stored procedure. In the stored procedure i included a static "ORDER BY", but i then declared the recordset like this:
<% sqlstr=exec sp2512 ''
   Set rs1 = Server.CreateObject("ADODB.Recordset")
   rs1.CursorLocation = 3 'client side cursor
   rs1.ActiveConnection = dataConn 'set the connection
   rs1.CursorType = 3
   rs1.source=sqlstr
   if orderby<>"" then
      rs1.sort=orderby
   end if
   rs1.open
' i get orderby value through request.querystring %>

hope this will help anyone else with this same issue.

TANIA P. BAQUERO
Panama Canal Authority
Student

Anantsharma September 29th, 2004 06:42 AM

Well friend do you aware of it that SQL gets forced to sort the resultset on the colum for which Distinct is written? So as per your query, SQL will try to order the resultset always on first colum. Further ur query is a little bit complex to read. I will review later.





B. Anant

shyam03 May 8th, 2008 02:30 AM

Hi, i had a similar problem. i was able to solve it by modifying the query as a sub query.

SELECT * FROM
(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)+ '%') AS A
ORDER BY
CASE
WHEN @OrderBy='Computadora' THEN name0
WHEN @OrderBy='Tipo Chasis' THEN ISNULL(ChassisTypesDesc0,'_Sin Tipo Chasis_')
WHEN @OrderBy='Usuario-Inventario' THEN username0
WHEN @OrderBy='Usuario-Last-Logon' THEN user_name0
WHEN @OrderBy='Fabricante' THEN manufacturer0
WHEN @OrderBy='Modelo' THEN model0
WHEN @OrderBy='Procesador' THEN name0
WHEN @OrderBy='Velocidad' THEN CONVERT(varchar(25),CurrentClockSpeed0)
ELSE name0



All times are GMT -4. The time now is 08:28 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.