bk wrote:
OpenOffice BASE seems to require some unusual form of SQL syntax that does not
seem to be the the same standard used by Microsoft Access, Sybase, Oracle or
even HSQL. I've been trying to replicate, to no avail, a SELECT query used in
a Microsoft Access DB, using the HSQLDB SQL syntax from that website. But
OpenOffice doesn't seem to know what to do with INTO TEXT temptable in a SELECT
statement.
I particularly need to be able to combine the data from multiple fields of the
same record in a table [with separating commas], into ONE temporary list field,
to be the source for selecting into only ONE field in an entirely different
table.
You probably already know that you can do things like this:
select T1 + T2 from TABLE1
You want to insert extra text, and you can not use something like this:
select T1 + 'hello'+ T2 from TABLE1
This will work:
select CONCAT(T1, 'hello') + T2 from TABLE1
In general, the function that are documented for hsqldb will work:
http://hsqldb.sourceforge.net/web/hsqlDocsFrame.html
Where can I obtain the official complete syntax for the SELECT statement that OpenOffice BASE happily accepts
(including some examples of correct usage using wildcards)? [The structure of SELECT DISTINCT "fieldname"
FROM "tablename" "tablename" ORDER BY "fieldname" ASC in which the names must be quoted
and the tablename repeated seems awkward.]
Many different types of systems automatically convert table names to
upper case unless they are quoted. I think that there is a way to turn
this off, but I am not certain off hand. So, you may not like quoting
filednames and table names, but if you use lower case letters, or spaces
in your names, this is your only option. (someone else can prove me
wrong, I hope). In my intro documents, I usually use all upper case so
that I can avoid the problem. It certainly caused me headaches until I
figured it out.
I'm studying the feasability of moving a single-user DB from Microsoft Access on a Windows PC eventually to OpenOffice on a Mac using OS 10, with OpenOffice on a PC as the intermediate stage. The Access DB in question has no forms [yet] but is loaded with table field queries and numerous ad hoc queries for specialized extractions. Some very large tables consist entirely of fields related to and filled in by fields in other tables via pull-down list queries.
Inserting large amounts of data into such an Access DB, where most of the fields in a record grouping are the same, is more easily achieved in a table format using the Ctrl-Quote feature in Access that automatically repeats the field immediately above.
Thank you.
Bill
--
Andrew Pitonyak
My Macro Document: http://www.pitonyak.org/AndrewMacro.sxw
My Book: http://www.hentzenwerke.com/catalog/oome.htm
Info: http://www.pitonyak.org/oo.php
See Also: http://documentation.openoffice.org/HOW_TO/index.html
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
