Using SQL queries

[tonyman]

Hey!

I just loaded a SQLite database into OO Calc. But now, how do I enter a query into one of my cells? I tried =SELECT count(*) FROM test; but that didn't work.



I found this article in the wiki, but it only seems to cover the setup.

Thanks!

[Villeroy]

You add queries to the database and use them exactly like tables.

[tonyman]

I don't understand. In cell A1, I want the result of SELECT count(*) FROM activities WHERE name='Anne';. In A2, I want SELECT count(*) FROM activities WHERE name='Siren';. How is this done?

Thanks!

[Villeroy]

1) A Base query
SELECT "Name",COUNT(*) FROM "Table" GROUP BY "Name"
selects all counts for all names.

2) A data pilot does the same.

3) SELECT COUNT(*) FROM "Table" WHERE "Name" = :pName
Prompts for the name on each refresh.

4) A form can be used for parameter substitution: http://user.services.openoffice.org/en/ ... 427#p96427

5) A macro to update an input range with a query comosed in a cell: http://user.services.openoffice.org/en/ ... 6847#p6847

[tonyman]

I still don't understand. All I want is to get the result of this query:

Code: Select all

SELECT count(*) FROM activities WHERE name='Anne';

from my ODBC connected SQLite file - into cell A1.

I cannot simply enter this query into my input line as a function?

I also don't understand why I'd need for wizards, forms and macros for this purpose.

[Villeroy]

Simply put it into your Base document.

[tonyman]

Cool!

But - once I get the result of a query, how do I link it to a cell (as opposed to just copy / drag-and-drop), so that when I bring in a new version of my SQLite file, my cells get automatically updated?

[Villeroy]

Exactly like you pull in the table. Drag the query icon from the left pane of the data source window into a sheet. This imports a column label and a value into an import range. menu:Data>Refresh re-runs the query.
[Tutorial] Using registered datasources in Calc

[tonyman]

I really appreciate it man. Have a great day!