How to clear the 'visible' check box in a query field used only for sorting?

[EasyTrieve]

In Base Query Design is there a way to clear the visible box for a field that will only be used to produce the ORDER BY clause? In other words, you don’t want that field to be output, but just use it to affect the sort order.

SQL doesn’t usually require that fields listed in ORDER BY also be listed in SELECT.

Only if I set the field to (not sorted), can I then un-check this box. But that doesn’t make any sense to me. I must be missing something.

For what it’s worth, I already tried creating one query to produce all fields, including the sort columns, and then a 2nd query that uses the first query as input, to only select the fields I wanted, and not select the sort fields, but the sort gets messed up in the 2nd query. This reordering shouldn’t happen from query to query, but it does.

[Regina]

What kind of database do you use? It is possible with the embedded HSQL, but not possible with a flat spreadsheet.

[EasyTrieve]

@Regina, sorry, I should have said; I’m connecting to MariaDB via MySQL->JDBC connector. I’ve known about this issue for some time, but hoped it would just go away at some point once I understood the nature of it.

Also I just did a test w/ HSQLDB 1.8, and can see that it works properly in this regard. I also tried Hide Column in the output, but when I copy the output to Calc, the hidden becomes visible again. Guess it’s time to take a walk in the bugzilla garden again. Thanks again.

[Ratslinger]

It works fine in SQL View.

[EasyTrieve]

@Ratslinger, thanks for the workaround. Yes that works great, well almost. It makes it so you can no longer use design mode. For small queries this is fine, for big it’s more of a challenge.

Also now I see the little SQL toggle button at the top (‘Run SQL Command Directly’) which locks design mode off, so the SQL can’t be silently destroying by accident. I never new what this button was for till now.

Are you aware of any bug report or fix, for this design mode issue? Thank you.

[Ratslinger]

That is not what the button is for. It is to bypass the built-in interpreter. The design view does not allow you to do many of the things you can do with SQL if using other than HSQL 1.8 - it is a crutch.

[EasyTrieve]

Interesting. You’re right, manual says “interpreted” & clarifies w/ “…without a previous evaluation by LibreOffice”. So I entered a Union query & with SQL button off, & tried to run it, and got: “Only queries are allowed.” which makes no sense, as a UNION query is a query. As far as I can tell, it looks like this “interpretation” is a check to see if the typed SQL is (possibly) compatible with design mode, i.e. a SELECT Q. Thanks for your insight into this.

[EasyTrieve]

Also noticed column widths changed depending on SQL button state (e.g. for something simple like SELECT * FROM foobar), so something about displayed formatting is also affected by the SQL button.

[EasyTrieve]

& I was able to get my more complex query to not show columns used only for sorting, so thanks for your tip.