[Solved] SQL Statements available for spreadsheet data

Creating tables and queries
Post Reply
owlbrudder
Posts: 93
Joined: Tue Nov 08, 2016 8:04 am

[Solved] SQL Statements available for spreadsheet data

Post by owlbrudder »

I have an application in Base which draws its data from a Calc spreadsheet. It would be helpful if I could use 'LIMIT' in one of my queries, but neither of the following expressions, which I found via Google, works:

Code: Select all

SELECT LIMIT 3 2 "First Name", "Surname" FROM "Master List" WHERE "Surname" = :Surname
SELECT "First Name", "Surname" FROM "Master List" WHERE "Surname" = :Surname LIMIT 3 OFFSET 2
The first query returns a syntax error until I delete the LIMIT 3 2 expression and the second query fails to apply any limit or offset.

I have been able to find a list of SQL functions available for File-type data sources (eg Spreadsheets), but not a list of the SQL statements available, so I don't know if what I want to do is impossible.

As usual, any advice would be appreciated.

Cheers,
Doug
Last edited by Hagar Delest on Thu Apr 27, 2017 3:36 pm, edited 1 time in total.
Reason: tagged [Solved].
LibreOffice Version: 5.3.3.0
Fedora 26 Linux x86_64
Core i7, 8Gb
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQL Statements available for spreadsheet data

Post by Villeroy »

SELECT ...
FROM "Single Table"
WHERE "this"='that' OR :param="this"
ORDER BY "blah"

together with http://www.openoffice.org/dba/specifica ... tions.html
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
owlbrudder
Posts: 93
Joined: Tue Nov 08, 2016 8:04 am

Re: SQL Statements available for spreadsheet data

Post by owlbrudder »

Thanks, Villeroy. I had found that page of functions and it has been very useful, but what I need is the list of SQL statements I can use. For example, I know I can use SELECT, ORDER BY, SORT ASCENDING etc, but the LIMIT statement does not work. This may be because the LIMIT statement does not work for file-type datasources, or it may be because the LIMIT statement has been replaced by a different statement. I am still Googling in hope of finding a list of such statements, but no luck so far.

Sigh.
LibreOffice Version: 5.3.3.0
Fedora 26 Linux x86_64
Core i7, 8Gb
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQL Statements available for spreadsheet data

Post by Villeroy »

SELECT FROM WHERE ORDER BY
No more, no less.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
owlbrudder
Posts: 93
Joined: Tue Nov 08, 2016 8:04 am

Re: SQL Statements available for spreadsheet data

Post by owlbrudder »

Villeroy wrote:No more, no less.
Thanks. I will give up on that idea and see if I can emulate it adequately with WHERE "Field" > 'Criterion' AND "Field" < 'Criterion. Now, as for ROWNUM ... "8-)
LibreOffice Version: 5.3.3.0
Fedora 26 Linux x86_64
Core i7, 8Gb
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQL Statements available for spreadsheet data

Post by Villeroy »

Villeroy wrote:SELECT FROM WHERE ORDER BY
No more, no less.
I forgot the DISTINCT keyword.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
owlbrudder
Posts: 93
Joined: Tue Nov 08, 2016 8:04 am

Re: SQL Statements available for spreadsheet data

Post by owlbrudder »

Villeroy wrote:I forgot the DISTINCT keyword.
... and IS NULL / IS NOT NULL; not to mention all the functions: https://www.openoffice.org/dba/specific ... tions.html.

Villeroy, I want to thank you for all your help and advice. You have had a large hand in helping me set up a fund-raising system for a charity and it is going great guns already. I owe you. :)

Cheers,
Doug
LibreOffice Version: 5.3.3.0
Fedora 26 Linux x86_64
Core i7, 8Gb
Post Reply