Dynamic query for Text drop-down errors with Order By

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
bssxfire8
Posts: 46
Joined: Fri Oct 02, 2015 11:41 pm

Dynamic query for Text drop-down errors with Order By

Post 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
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: Dynamic query for Text drop-down errors with Order By

Post by PointsWell »

What is the attribute type for Sequence?
bssxfire8
Posts: 46
Joined: Fri Oct 02, 2015 11:41 pm

Re: Dynamic query for Text drop-down errors with Order By

Post by bssxfire8 »

It is a number field.
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: Dynamic query for Text drop-down errors with Order By

Post by 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.
Tom - V8.8 build 3137 - MySql / PostGres
bssxfire8
Posts: 46
Joined: Fri Oct 02, 2015 11:41 pm

Re: Dynamic query for Text drop-down errors with Order By

Post by 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:

Code: Select all

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

Code: Select all

SELECT
DISTINCT Description 
FROM HourType 
WHERE AvailabilityCode=3 
ORDER BY Sequence DESC
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: Dynamic query for Text drop-down errors with Order By

Post by 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

Code: Select all

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.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: Dynamic query for Text drop-down errors with Order By

Post by Jaymer »

just officially documented this, the fix, and sent it to Vlad as a bug
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
Post Reply