Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 22nd, 2004, 11:43 AM
Registered User
 
Join Date: Sep 2004
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
 
Old September 22nd, 2004, 12:55 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old September 22nd, 2004, 01:03 PM
Registered User
 
Join Date: Sep 2004
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old September 23rd, 2004, 04:06 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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>>
 
Old September 27th, 2004, 07:57 AM
Registered User
 
Join Date: Sep 2004
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old September 28th, 2004, 01:15 PM
Registered User
 
Join Date: Sep 2004
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old September 29th, 2004, 06:42 AM
Friend of Wrox
 
Join Date: Sep 2004
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to Anantsharma Send a message via Yahoo to Anantsharma
Default

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
 
Old May 8th, 2008, 02:30 AM
Registered User
 
Join Date: May 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
Grouping and Distinct items Condor76 XML 5 May 23rd, 2007 01:11 AM
Select Distinct? [email protected] SQL Language 5 November 5th, 2005 09:58 AM
Distinct SELECT DISTINCT question... EndEffect Classic ASP Databases 4 August 18th, 2005 08:53 AM
order by In clause items? cs8271506 SQL Language 2 September 19th, 2004 08:13 PM
Select Distinct Order by... MichaelTJ SQL Language 2 November 17th, 2003 12:49 AM





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