Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old September 22nd, 2004, 11:43 AM
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
  #2 (permalink)  
Old September 22nd, 2004, 12:55 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
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
Reply With Quote
  #3 (permalink)  
Old September 22nd, 2004, 01:03 PM
Registered User
 
Join Date: Sep 2004
Location: , , Panama.
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
Reply With Quote
  #4 (permalink)  
Old September 23rd, 2004, 04:06 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
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>>
Reply With Quote
  #5 (permalink)  
Old September 27th, 2004, 07:57 AM
Registered User
 
Join Date: Sep 2004
Location: , , Panama.
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
Reply With Quote
  #6 (permalink)  
Old September 28th, 2004, 01:15 PM
Registered User
 
Join Date: Sep 2004
Location: , , Panama.
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
Reply With Quote
  #7 (permalink)  
Old September 29th, 2004, 06:42 AM
Friend of Wrox
 
Join Date: Sep 2004
Location: New Delhi, Dekhi, India.
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
Reply With Quote
  #8 (permalink)  
Old May 8th, 2008, 02:30 AM
Registered User
 
Join Date: May 2008
Location: , , .
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

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Grouping and Distinct items Condor76 XML 5 May 23rd, 2007 01:11 AM
Select Distinct? hugh@kmcnetwork.com SQL Language 5 November 5th, 2005 08: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 16th, 2003 11:49 PM



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


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