Base Query Wizard closes Base

Discuss the database features

Base Query Wizard closes Base

Postby BDE » Sun Feb 18, 2018 11:45 am

Totally new to Base and running into following problem. I created a database linked to an ODS spreadsheet and when I am clicking the query wizard Base simply closes.
OpenOffice 5.1.6.2 on Linux Mint Serena
BDE
 
Posts: 2
Joined: Sun Feb 18, 2018 11:33 am

Re: Base Query Wizard closes Base

Postby BDE » Sun Feb 18, 2018 2:29 pm

Sorry, now in English: the Query wizard, ans also the Forms- and Reports wizard produces an immediate close of Base, no wizard at all.
OpenOffice 5.1.6.2 on Linux Mint Serena
BDE
 
Posts: 2
Joined: Sun Feb 18, 2018 11:33 am

Re: Base Query Wizard closes Base

Postby Villeroy » Sun Feb 18, 2018 3:44 pm

This might be a bug in your version of LibreOffice. The "query wizard" is useless anyway, particularly when used with a file based database (sheets, text, dBase, Thunderbird addresses). All you can query from a file based database is this:
Code: Select all   Expand viewCollapse view
SELECT <field list>
FROM "Single Table"
WHERE <conditions>
ORDER BY <field list> ASC|DESC

This means that a query can return any order of columns and rows from one spreadsheet range representing a "list", filtered by complex conditions. This is more than a spreadsheet can do but does not require any "wizard". Simply use the "designer" tool or use a bare minimum of SQL.

Query a two-column list from 4 columns "Forename", "Surname", "Date" and "Category") of the spreadsheet "Sheet1".
Code: Select all   Expand viewCollapse view
SELECT CONCAT("Surname", ', ', "Forename") AS "Full Name", "Date" AS "Birthday"
FROM "Sheet1"
WHERE MONTH("Date")=MONTH(CURDATE()) AND "Category"='Friends'
ORDER BY "Date" DESC

This concatenates the forenames and surnames with a comma to "Doe, John" filters the rows by the current month and by the friends category and orders the result set by the dates (latest on top). The field list in the SELECT clause supports alias names ("Column Label" AS "Other Name").
This is almost everything you can do with a sheet in Base.

There is a rather limited set of available functions: http://www.openoffice.org/dba/specifica ... tions.html (for instance MONTH and CONCAT in the above example).
When referring to a "sheet", you always get the used area of the entire sheet as a database table.
Not every sheet is organized as an adjacent list. You can hide inadequate sheets via Base menu:Tools>Table Filter...
You can separate list ranges from other sheet content by means of "database ranges" (Calc menu:Data>Define...). Database ranges are treated as tables together with the used areas of the sheets.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25984
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Base

Who is online

Users browsing this forum: No registered users and 4 guests