[Solved] SELECT INTO query syntax error

Creating tables and queries
Post Reply
Ken53
Posts: 6
Joined: Tue Oct 21, 2008 11:22 pm

[Solved] SELECT INTO query syntax error

Post by Ken53 »

(Well, at least it's resolved.)

Attempting to convert an existing Microsoft Access database to OpenOffice Base and also learn some SQL in the process, I have encountered a problem that I cannot solve with my very limited understanding, nor do I find the problem addressed under topics I can think of in this Forum. Condensed to its simplest form, here it is.

I have a table, Persons:

ID Person
1 Bill
2 Sue
3 Joe
4 Mary

In Access, the following query, qry_Backup_Persons,

Code: Select all

   SELECT *
   INTO Persons_Backup
   FROM Persons;
creates a copy of Persons, which it is supposed to.

But in Base, with the same table, the same query,

Code: Select all

   SELECT *
   INTO Persons_Backup
   FROM Persons;
has a syntax error: "Syntax error in SQL expression". No more detailed information is given. I have tried it with and without quotes and brackets around the table names and with and without the ';', all with the same result. What I am trying to do may be bad form, but I'm looking for a quick-and-dirty solution and the SELECT INTO approach would seem to provide it, if I could just get past the syntax error.

The problem occurs in both Ubuntu 8.04 and Windows XP versions of Base, using the HSQL database engine.

Is there a solution or a work-around or an alternate method to create a table from a query?
Last edited by Ken53 on Thu Oct 23, 2008 4:18 pm, edited 1 time in total.
OOo 2.4.X on Ubuntu 8.x + MS Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SELECT INTO query syntax error

Post by Villeroy »

Does the status bar indicate HSQLDB?
http://hsqldb.org/doc/guide/ch09.html#insert-section

Code: Select all

INSERT INTO "Persons_Backup" SELECT * FROM "Persons";
If fields do not match exactly one to one:

Code: Select all

INSERT INTO "Persons_Backup" ("Field2","Field1","Field3") SELECT "Field1","Field3","Field2"  FROM "Persons";
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
Ken53
Posts: 6
Joined: Tue Oct 21, 2008 11:22 pm

Re: SELECT INTO query syntax error

Post by Ken53 »

Thanks for the quick reply!
Villeroy wrote:Does the status bar indicate HSQLDB?
Almost. It says "HSQL database engine". I assume that means the same thing.
...

Code: Select all

INSERT INTO "Persons_Backup" SELECT * FROM "Persons";
That produces the same "Syntax error in SQL expression." message. Ah, but now I see the [More] button! And that reveals more information on the error:

Code: Select all

SQL Status: HY000
Error code: 1000

syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE
I'm not much better off, though, for I see nothing about those "expected" keywords in the SELECT or INSERT command syntax in the Hsqldb User Guide, http://hsqldb.org/doc/guide/ch09.html.
OOo 2.4.X on Ubuntu 8.x + MS Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SELECT INTO query syntax error

Post by Villeroy »

What can I say? The following query finished succesfully after I copied the definition from "Dates" to a new table "Dates2":

Code: Select all

INSERT INTO "Dates2" SELECT * FROM "Dates"
You can copy tables in the GUI. Just copy and paste the objects in the tables container or drag a table aside. A wizard pops up ...
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
Ken53
Posts: 6
Joined: Tue Oct 21, 2008 11:22 pm

Re: SELECT INTO query syntax error

Post by Ken53 »

Well, thanks for trying.

Summary: The syntax looks OK and it works in another's Base, but mine declares a syntax error.

Does anyone know what's going on here? Is this some kind of Base configuration problem? As I recall, I did a standard installation using Applications > Add/Remove ... Is it worth a try to remove Base, then re-install?
OOo 2.4.X on Ubuntu 8.x + MS Windows XP
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: SELECT INTO query syntax error

Post by Sliderule »

Ken33:

Your original question . . . where you said:
Ken33 wrote:SELECT *
INTO Persons_Backup
FROM Persons;

has a syntax error: "Syntax error in SQL expression".
The above, would be accurate ( error ), as you have entered it.

What I mean is, it WILL return an error, BUT, yes, it is 'easy' to correct.

Just for others reading this . . . one can 'easily' create a 'backup' table . . . or . . . part of a table . . . by using the SELECT FIELD_NAME INTO NEW_TABLE FROM OLD_TABLE syntax . . . when . . . the above is 'executed' from either, a macro, OR, the menu, Tools -> SQL...

The above is 'documented' in HSQL documentation at: http://www.hsqldb.org/doc/guide/ch09.ht ... ct-section .

BUT, I suspect, your 'problem' / 'solution' is this little 'footnote' in the documentation:
A unquoted identifier (name) starts with a letter and is followed by any number of ASCII letters or digits. When an SQL statement is issued, any lowercase characters in unquoted identifiers are converted to uppercase. Because of this, unquoted names are in fact ALL UPPERCASE when used in SQL statements.
What this means is . . . because your Table Name is defined as: Persons . . . ( mixed case, using both UPPER CASE and lower case letters ) . . . you MUST wrap your column and table names in double quotes.

So, by entering the command as:

Code: Select all

SELECT *
   INTO "Persons_Backup"
   FROM "Persons";
should work. You should see the message . . . Command successfully executed.

To view the 'new' table in the Table section . . . you will also have to issue the command . . . from the Menu: View -> Refresh Tables

I hope that is clear', and, it helps. Please be sure to let me / us know. :D

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
Ken53
Posts: 6
Joined: Tue Oct 21, 2008 11:22 pm

Re: SELECT INTO query syntax error

Post by Ken53 »

Thanks, Sliderule.

Code: Select all

SELECT *
INTO "Persons_Backup"
FROM "Persons";
works for me in Tools -> SQL..., but not in a query. If that's a hard limitation, then it would appear that Base (using the HSQL database engine, anyway) does not have the equivalent of a MS Access Make Table query. So to create a table under program control requires a macro. Is that right? Or is there another way to use a query to create a table?
OOo 2.4.X on Ubuntu 8.x + MS Windows XP
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: SELECT INTO query syntax error

Post by Sliderule »

Ken33:

I am not sure I understand your question.

Just to be clear ( or at least, as clear as possible ) . . . creating a new table . . . with the

SELECT FIELD_NAMES INTO NEW_TABLE FROM OLD_TABLE

does NOT produce a result set ( return of records to be displayed ) . . . ergo . . . it can ONLY be 'run' from either:
  1. Tools -> SQL...
  2. Macro
. . . but . . . NOT from the Query Window . . . since . . . the above is NOT a Query . . . that is . . . just READING a database. Rather, what you are doing is BOTH, reading, and, writing to the database ( changing the database -- adding a new table ) . . . NOT JUST a 'query'.

You said:
Ken33 wrote:So to create a table under program control requires a macro.
I do NOT understand what you are asking ( under program control ). This is OpenOffice Base, NOT MS Access. You can 'pass' an SQL command ( or multiple SQL commands ) 'directly' to the database engine . . . by issuing the SQL command(s) 'directly' via the Tools -> SQL... menu, OR, via a macro(s).

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
Ken53
Posts: 6
Joined: Tue Oct 21, 2008 11:22 pm

Re: SELECT INTO query syntax error

Post by Ken53 »

Sliderule wrote:... what you are doing is BOTH, reading, and, writing to the database ( changing the database -- adding a new table ) . . . NOT JUST a 'query'.
So the problem lies not in Base, but in my misunderstanding of SQL and databases. I was trying to use a query to do something which a query should not be expected to do. OK, thanks for helping me learn that.
Sliderule wrote:
Ken53 wrote:So to create a table under program control requires a macro.
I do NOT understand what you are asking ( under program control ). ...
I would like to perform a sequence of actions on the database (e.g., query, create a new table, query, ...) automatically, i.e, without the user having to manually perform a sequence of menu selections or enter a sequence of commands. I believe that can be done with a macro. Now I have to learn more about macros ...

Thanks for your help!

Ken53
OOo 2.4.X on Ubuntu 8.x + MS Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SELECT INTO query syntax error

Post by Villeroy »

Running SQL by macro is not difficult. For casual use I would store the string somewhere and paste it into the "command line" (Tools>SQL...). A macro to run SQL that has been stored in a table would be nice.
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
Ken53
Posts: 6
Joined: Tue Oct 21, 2008 11:22 pm

Re: SELECT INTO query syntax error

Post by Ken53 »

Thanks, Villeroy and Sliderule. I think that I can make one of the alternatives you suggested work for me. This is just a prototype application and I will turn it over to someone who knows what they're doing for the operational version.

Ken
OOo 2.4.X on Ubuntu 8.x + MS Windows XP
Post Reply