Queries on Queries when linked to a Spreadsheet

Discuss the database features

Queries on Queries when linked to a Spreadsheet

Postby colinco » Tue Oct 13, 2009 12:37 pm

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
OpenOffice.org 3.1.1
OOO310m19 (Build9420)
Vista Home
colinco
 
Posts: 3
Joined: Tue Oct 13, 2009 11:59 am

Re: Queries on Queries when linked to a Spreadsheet

Postby r4zoli » Tue Oct 13, 2009 12:51 pm

Create from first query a view, then it can be used as query source.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Queries on Queries when linked to a Spreadsheet

Postby colinco » Tue Oct 13, 2009 1:07 pm

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

Regards,

ColinCo
OpenOffice.org 3.1.1
OOO310m19 (Build9420)
Vista Home
colinco
 
Posts: 3
Joined: Tue Oct 13, 2009 11:59 am

Re: Queries on Queries when linked to a Spreadsheet

Postby Villeroy » Tue Oct 13, 2009 1:29 pm

File based databases do not support nested queries nor views. When views are availlable they are supported by the backend datbase (MySQL, HSQL,...)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17305
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Queries on Queries when linked to a Spreadsheet

Postby colinco » Tue Oct 13, 2009 2:10 pm

Villeroy wrote: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.

posting.php?mode=quote&f=13&p=107467#
Regards,

ColinCo
OpenOffice.org 3.1.1
OOO310m19 (Build9420)
Vista Home
colinco
 
Posts: 3
Joined: Tue Oct 13, 2009 11:59 am

Re: Queries on Queries when linked to a Spreadsheet

Postby Villeroy » Tue Oct 13, 2009 2:35 pm

No. All you can do with file based pseudo-databases goes like:
Code: Select all   Expand viewCollapse view
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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17305
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Queries on Queries when linked to a Spreadsheet

Postby eremmel » Wed Oct 14, 2009 12:06 am

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)?
Please, edit this thread's initial post and add "[Solved]" to the subject line if your problem has been solved.
OOo 3.3.0 on XP SP3 for real life with ORB; AOO 3.4.1, 4.0.1, LO 4.1.2.3 on W7 for testing
User avatar
eremmel
Volunteer
 
Posts: 656
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands


Return to Base

Who is online

Users browsing this forum: No registered users and 4 guests