Can't Save Sort in Query

Creating tables and queries
Post Reply
RatSnake
Posts: 6
Joined: Sat Dec 29, 2012 4:10 am

Can't Save Sort in Query

Post by RatSnake »

I'm trying to sort on 2 fields in query. If I run the query from within edit, it works just fine. If I save the query, the sorts disappear. I'm running Open Office 4.0.0
Dennis Murphy
Third Lake IL
Open Office 4.0.0
Windows 8
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Can't Save Sort in Query

Post by MTP »

Is the sort part of your query? As in, at the end of your SQL have something like

Code: Select all

ORDER BY "SortColumn1", "SortColumn2"
That kind of sort should be saved. If you are using the GUI to do the sort, there is not any way to save that directly inside the query.

A GUI sort will be saved if you do it on a form that was opened in edit mode and then save the form. So you can use the query as the basis for a form, open the form in edit mode, turn design mode off, do the GUI sort, then save the form.

Would one of those methods work for you?
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
RatSnake
Posts: 6
Joined: Sat Dec 29, 2012 4:10 am

Re: Can't Save Sort in Query

Post by RatSnake »

Running with the GUI was just a test to see if the sort worked. It did. I would like to run the query from a report.
Dennis Murphy
Third Lake IL
Open Office 4.0.0
Windows 8
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Can't Save Sort in Query

Post by MTP »

If you want your sort to be saved, go to the query, right-click and choose "Edit in SQL View..."
A window will pop up with some SQL code. Add to the end of the code the words "ORDER BY" and then put the column you are sorting with in double quotes. You can sort by more than one column, just put a comma between the names. Save the query.
Now when you run your report, the query will come up sorted.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
claudia_ggk
Posts: 5
Joined: Sat Apr 07, 2018 12:06 pm

Re: Can't Save Sort in Query

Post by claudia_ggk »

I am having the same problem. I create a query where I sort one of the columns. It works when I run it, but when I save it, close it and open it again, the sorting disappears. I have tried both in design and in SQL view.
Windows 10
OpenOffice 4.1.5
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Can't Save Sort in Query

Post by UnklDonald418 »

The following example query shows SQL that would display a list of people sorted first by their birthdays (youngest to oldest) and then alphabetically by last name and then first name.

Code: Select all

SELECT "LastName", "FirstName", "Date of Birth" FROM "Persons"  
ORDER BY "Date of Birth" DESC, "LastName" ASC, "FirstName" ASC;
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
claudia_ggk
Posts: 5
Joined: Sat Apr 07, 2018 12:06 pm

Re: Can't Save Sort in Query

Post by claudia_ggk »

The problem is not sorting, which works fine, but saving the query containing the sorting. Once I close the query and re-open it to modify it, the sorting has disappeared.
Windows 10
OpenOffice 4.1.5
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Can't Save Sort in Query

Post by UnklDonald418 »

I was able to duplicate your problem.
I used Create Query In Design View to create a new query that includes sorting, then saved that query.
If I reopen the query using the Edit menu choice the sorting is gone.
However, if I select Edit in SQL View, the ORDER BY clause is still there and when I run the query it returns the data properly sorted.
The Query Design GUI does have limitations, and apparently that is one of them.
Another reason to learn SQL!
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
claudia_ggk
Posts: 5
Joined: Sat Apr 07, 2018 12:06 pm

Re: Can't Save Sort in Query

Post by claudia_ggk »

This works differently for me. The sorting disappears both from the SQL code AND from the query design GUI.
I even tried creating the query directly in SQL view, same problem. Could it be due to the different OpenOffice version?
Windows 10
OpenOffice 4.1.5
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Can't Save Sort in Query

Post by UnklDonald418 »

It still strips the ORDER BY clause from your query? That's odd, I thought that the SQL editor would bypass the parser.
I just upgraded to 4.1.5 and still have no problem with Edit in SQL View. I tried it with both an Embedded database and a Split database and still no problems.
Then I tried using LO Version: 6.0.1.1 (x64) and I can see the sorted fields even when using the GUI so it appears they have fixed the problem.
https://www.libreoffice.org/download/download/
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
claudia_ggk
Posts: 5
Joined: Sat Apr 07, 2018 12:06 pm

Re: Can't Save Sort in Query

Post by claudia_ggk »

UnklDonald418 wrote:It still strips the ORDER BY clause from your query?
Yes.
It is odd, very! Any idea what could be causing this?
Any of my local settings? I use Base in Italian or in German, and it doesn't make a difference with respect to this problem.
I have to use OpenOffice and cannot switch to LibreOffice, I use it at school and I cannot switch program in the middle of the school year.
Windows 10
OpenOffice 4.1.5
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Can't Save Sort in Query

Post by UnklDonald418 »

No, I don't know what would cause the SQL direct editor to parse the statement.
I would first use the GUI to set up the query so that it sorts to your satisfaction.
Then making sure the icon Run SQL command directly is selected ( the background color should change from white to probably blue even when the mouse pointer isn't over it) and then Save (Ctrl-S) and exit the query.
Now reopen the query using Edit in SQL View to see if the ORDER BY clause is still there.
If it is missing
Create a new database and copy a table into the new database.
Now create a query that involves sorting then save and exit. Reopen with Edit in SQL View
If the problem persists in the new database the only other suggestion I would have would be to try resetting your user profile
[Tutorial] The OpenOffice User Profile
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
charlie.it
Volunteer
Posts: 417
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: Can't Save Sort in Query

Post by charlie.it »

I do not think it's a user profile problem, I think it is a bug.
I replicated the issue with one of my files on my Mac computer. It only occurs in edit mode but not in edit SQL mode. With the Mac version 4.1.5, but also with version 4.1.3.
I'm answering in parallel on the Italian forum: https://forum.openoffice.org/it/forum/v ... =13&t=9009.

Edit: It occurs also with OO 4.1.5 in Windows 7 pro system.
charlie
Italian AOO Admin
macOS 14 Sonoma M1: Open Office 4.1.15 - LibreOffice 7.5.7.1

http://www.charlieopenoffice.altervista.org
claudia_ggk
Posts: 5
Joined: Sat Apr 07, 2018 12:06 pm

Re: Can't Save Sort in Query

Post by claudia_ggk »

A correction to what I wrote above: when I open the query with "edit" the sorting is not there anymore, even if I switch to SQL view. If I open the query with "edit in SQL view" the sort is still there. Now I understood that is what was meant above. Sorry for the confusion!
I found a bug report: https://bz.apache.org/ooo/show_bug.cgi?id=121096
and a patch: https://svn.apache.org/viewvc/openoffic ... ev=1713502
Hope it helps whoever is having the same problem.
Windows 10
OpenOffice 4.1.5
Post Reply