Queries on Queries when linked to a Spreadsheet

[colinco]

Hi there, I'm attempting to migrate to OpenOffice Base from Access and have come up against the following issue.....

I have a spreadsheet (Running under OpenOffice Calc) which is regularly updated (daily at times) and therefore set-up the linked database. The connection works fine and all the sheets in the .ods doc are carried across into the database. One of these sheets / tables is a listing of a large number of glider flights and I can successfully run a query against this table to give a subset of original flights dependant upon various criteria: speed, distance turnpoints used etc. I then want to find which of this subset, flew the longest distance and so attempt to run a query against the first query. This where the problem starts...

Using the Design View it's not possible to build a new query using information from an existing query, the only option available is to add Tables - the radio button to select Queries is just not there. However when using an unlinked HSQL database it's possible to build queries using both Tables and Queries so why not when linking to a spreadsheet?

If then by-pass the Design View and attempt to create the new query using the SQL command:

SELECT MAX( `Handicapped Distance km` ) AS `MMM_Max_km` FROM `xMMM_Qualifiers_1` AS `xMMM_Qualifiers_1`

where 'xMMM_Qualifiers_1' is the first Query and `Handicapped Distance km` is a column within that query, I get a error condition stating that "

The query can not be executed. It contains no valid table."

This is getting most annoying as this works successfully within Access and I can't understand that the simple difference of linking tables to a spreadsheet should restrict the operation of queries to such an extent.

Regards,

ColinCo

[r4zoli]

Create from first query a view, then it can be used as query source.

[colinco]

Unfortunately when linked to a spreadsheet, the 'Create as View' option does not exist on either a Query or Table!

Regards,

ColinCo

[Villeroy]

File based databases do not support nested queries nor views. When views are availlable they are supported by the backend datbase (MySQL, HSQL,...)

[colinco]

File based databases do not support nested queries nor views. When views are availlable they are supported by the backend datbase (MySQL, HSQL,...)

If that's the case, is the there any way I can create a single query that will produce a subset of the original records and return only the one record that has maximum (within that subset) for one of the values:

As an example the original table could be:

Pilot, Aircraft, Distance, Speed
Bob, K13, 123, 76
Mike, LS8, 205, 97
Jim, LS8. 178, 86
Bob, K13, 156, 56

I'd like to find out who flew the fastest using an LS8 aircraft. Records 2 & 3 match the aircraft type and so would equate to the subnet of records, however I'm only interested in the one LS8 flight that was the fastest i.e. record 2 in this case. However with a spreadsheet linked database the MAXIMUM 'function' is not available from either the Design View drop down list and if entered via SQL, causes the following error

'The query can not be executed. It is too complex. Only "Count(*) is supported.

http://user.services.openoffice.org/en/ ... 3&p=107467#
Regards,

ColinCo

[Villeroy]

No. All you can do with file based pseudo-databases goes like:

Code: Select all

SELECT <field list> (incl. AS "alias names")
FROM "single table" 
WHERE <condition1> AND/OR <condition2> AND/OR <condition3>
ORDER BY <field list> ASC/DESC

which is more than you can do in the spreadsheet, particularly when in need of more sort fields and nested filter criteria with OR and AND.
You can't use more than one table (relations) nor groups and aggregations (SUM, AVG, MIN, MAX, GROUP BY, ...). The only availlable aggregation is COUNT(*) to get the overall count of rows.
Availlable field functions are: SQL Functions for file based database drivers

You can do all this in a separate "database table"(spreadsheet) in fact:
Function Result <-column labels
MAX =MAX(ListField)
MIN =MIN(ListField)
SUM =SUM(ListField)
... ...

One other option when connecting to ODF-spreadsheets: Define your lists as named database ranges (Data>Define) and hide the sheet-tables in the database (Tools>Table Filter) so you see only the relevant list ranges as "database tables".

It is fairly easy to copy spreadsheet lists into a real database. Copy your spread-Base tables into a new built-in database.
Using database data in spreadsheets is easy: [Tutorial] Using registered datasources in Calc

[eremmel]

If I understand you right you have in one sheet of information that you query to put data into various Base tables and now you run into SQL limitations. Why not putting all the original data of that sheet into single Base table and defined queries/views on that table to get the results you want (iso having various tables with sub-sets of data of that sheet)?