Dynamic query for Text drop-down errors with Order By

bssxfire8

I have SQL as my db and any time I try to do a Dynamic query drop-down with an ORDER BY clause for a Plain Text attribute I get this error:

com.microsoft.sqlserver.jdbc.SQLServerException: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Is this something I am doing wrong?
This is my query string:

FIND HourType WHERE HourType.AvailabilityCode=3 ORDER BY HourType.Sequence DESC

If I remove the ORDER BY everything works fine.

PointsWell

What is the attribute type for Sequence?

bssxfire8

It is a number field.

tford

There are a bunch of listing if you Google "order by items must appear in the select list if select distinct is specified" so it's not Aware specific.

I don't have an answer for you, but I would explore there.

bssxfire8

Yep it is a very easy to pinpoint/replicate problem in SQL. You can't use distinct in the select statement of a query where you are ordering by a specific column and not include that column in the select statement.
The issue is that there is no way in aware (that I know of) to modify what is being sent to the SQL server from a custom query to make the query something like:

SELECT 
Description 
FROM HourType 
WHERE AvailabilityCode=3 
ORDER BY Sequence DESC

Instead of:

SELECT
DISTINCT Description 
FROM HourType 
WHERE AvailabilityCode=3 
ORDER BY Sequence DESC

Jaymer

i'm having this error. I replaced some peer single links with dynamic choices.
erratic results.
i DO NOT have an order by.
I have 3 of these choice boxes on a form.
2 work fine.
something is throwing an error in the server output AND tomcat output

Web server returned error: Internal error. ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
WebServlet URL got request with params:
WebServlet URL got request with params:
Exception thrown com.bas.connectionserver.server.BASServerException Internal error. ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

very odd
i'm was pointing to a saved query, but when I pasted in the exact same Custom Query it started working correctly.

Jaymer

just officially documented this, the fix, and sent it to Vlad as a bug