Ask Your Question

Base Sorting and Grouping - sth wrong. Version: MS SQL

asked 2014-07-16 14:02:19 +0200

Prosper gravatar image

updated 2015-08-31 18:18:02 +0200

Alex Kemp gravatar image


I have a database with simple query. It has 3 columns: Seller_Id; Doc_Id (invoice number); Product_Id Database is MS SQL 2008 R2. I connect to this database using jtds.jdbc driver.

I found some bug in Base Report. I create report using SQL command. If I use "Direct SQL" then Base doesn't sort results. If I use "Normal mode" (I don't know what is name for it) then everything is ok.

But I need to use subquery with TOP command and I'm not able to do this in normal mode.

After many hours struggling with this I found some solutions but I'm not very happy with them. If I use normal mode I need to use complicated solution with SELECT and MAX to retrive TOP row. It is not elegant nor effective. If I use "Direct SQL" I need to sort results, because Base doesn't do it. I can't use ORDER BY because MS SQL doesn't allow this in inner select. And probably base treat this whole SQL query as subquery. To workaround this I need to use SELECT TOP 99999999 (some big number) and ORDER BY and the end. It not elegant as well.

Should I report a bug or I made some mistakes?

For example if I want to sort and group by seller:

Seller_Id; Doc_Id (invoice number); Product_Id

And the result in base report is:
Group: Seler 1
Group: Seler 2
Group: Seler 1

If I try to sort it in query (using "order by" ) in "Direct mode" I get error.

"The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified." It is because I want to order in inner select and this is prohibited.

edit retag flag offensive close merge delete


Well the error message is pretty explicit, isn't it ? What you are trying to do in your query is not supported by the db engine. Which db engine (and version) / datasource are you using ?

Of course, the error message might also be displayed mistakenly, if there is a problem in the programming code

Alex Thurgood gravatar imageAlex Thurgood ( 2014-07-18 10:03:11 +0200 )edit

I updated my question.

Prosper gravatar imageProsper ( 2014-07-24 12:10:22 +0200 )edit

Which version of LibreOffice ? There was a bug in older versions of LibreOffice when using escape processing (the direct SQL toggle switch). Additionally, it might be a good idea to try and file a bug report and repeat what you have written here.

Alex Thurgood gravatar imageAlex Thurgood ( 2014-08-10 19:50:50 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-02-27 15:45:00 +0200

I agree that this is strange.

First, please check that you get connected to the same db and server, using the same user credentials (this can make sense).

Next, please click on the "SQL" button in the toolbar on the Result data tab. You will see the SQL query which is actually executed. Possibly the TOP clause has been added to this query.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2014-07-16 14:02:19 +0200

Seen: 401 times

Last updated: Feb 27 '19