Base: Data modification queries? append, update?

[JohnTheWysard]

I'm a new user of OpenOffice Base, but I have quite a bit (too much??) familiarity with other dialects of SQL. My current problem is that I'm trying to massage a non-normalized spreadsheet type of list (newspaper subscribers and subscription renewals to be exact) into normalized tables.

In Access or SQL/Server I'd do this by creating Group By queries or DISTINCT queries, and changing them into a MakeTable or Append query. I am sure there is a way to do this in OOBase, but I haven't found it! Any suggestions?

I'm running a freshly installed instance of OO 2.4.1 on WindowsXP.

I'm looking forward to participating in this group; as I get more familiarity with the way things are done in this programming environment, I hope I'll be able to help others.

John W. Vinson aka John the Wysard (no false modesty there!)

[QuazzieEvil]

Base does not yet have Append, Update, or Delete query objects--as in MS Access. This must be done with SQL commands in Base. If you need to perform complex updates/deletes/inserts, you may want to use Basic macros to execute the SQL commands. See http://www.geocties.com/rbenitez22 docs on Basic programming and Base. You can also execute SQL with the SQL dialog (Tools | SQL..) .

[DrewJensen]

Well, if you are looking for a nice GUI way to do that and not found it - it is because it is not there.

You need to do this using standard SQL commands.

These command would be entered into the SQL window ( Tools>SQL ). Which is friggin modal...pet peeve.

So - you can use the Query Designer to create you select statement and then switch to SQL virew - copy the SQL statement to the clipboard. Open the SQL window, paste it in and fix it up as needed then execute it.

[JohnTheWysard]

so - you can use the Query Designer to create you select statement and then switch to SQL virew - copy the SQL statement to the clipboard. Open the SQL window, paste it in and fix it up as needed then execute it.

Well, that would be fine... I'm quite comfortable writing SQL. But it seems that my syntax differs from OOBase's syntax! The ANSI syntax

INSERT INTO tablename(field, field, field) SELECT field, field, field FROM table

and assorted variants does not work. Is there a SQL language definition document somewhere?

Thanks for the help!

John W. Vinson

[JohnTheWysard]

Base does not yet have Append, Update, or Delete query objects--as in MS Access. This must be done with SQL commands in Base. If you need to perform complex updates/deletes/inserts, you may want to use Basic macros to execute the SQL commands. See http://www.geocties.com/rbenitez22 docs on Basic programming and Base. You can also execute SQL with the SQL dialog (Tools | SQL..) .

hmmm... getting a Yahoo "page not found" error on that...

I'm willing to use Basic programming if necessary but surely this can be done with a SQL query! Is there a SQL language syntax document somewhere?

John W. Vinson

[Sliderule]

John:

You asked:

Well, that would be fine... I'm quite comfortable writing SQL. But it seems that my syntax differs from OOBase's syntax! The ANSI syntax

INSERT INTO tablename(field, field, field) SELECT field, field, field FROM table

and assorted variants does not work. Is there a SQL language definition document somewhere?

Please look at the link below, and, especially at the Suggestions part by Sliderule ( AKA that is yours truly ):

http://user.services.openoffice.org/en/ ... =13&t=5627

I would be willing to GUESS ( but cannot say for certain ) that your SQL issue is probably explained at:

http://www.hsqldb.org/doc/guide/ch09.ht ... on-section

. . . so . . . if you place your Table and Field names between double quotes . . . this will 'resolve' your issues.

Try something like this and of course, change tablename and field to the appropriate name . . . that ARE case sensitive to the exact name as defined in your table definition:

Code: Select all

INSERT INTO "tablename" ("field", "field", "field") SELECT "field", "field", "field" FROM "table";
INSERT INTO "MyTable" ("Field_01", "Field_02", "Field_03") SELECT "Field_01", "Field_02", "Field_02" FROM "Next_Table";

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.

[DrewJensen]

Base is really just a front end to any database engine - it includes an embedded version of HSQLdb if you want the data in the actual Base file however.

The user Manual for that can be found at their site, http://hsqldb.org

There are few things on the OO.o wiki also, but go to the source to start with...if your example SQL is just that now problem but is that is what you used then yes it failed.

HSQLdb has the following quoting rules:
ALL Identifiers must be double quoted if they use mixed-case or all lower case names. ( that includes your column and table aliases by the way )

The SQL window helps you here with a 'feature' - if casts everything in the statement to upper case that is not double quoted.
Meanwhile the GUI designers helps you with a 'feature' - .it double quotes everything for you...

SO - say you create a table in the SQL window: create table_1 ( id Identity, fname varchar(40) );
Now you enter a statement later in the SQL window: select * from table_1
Everything works, because everything is in upper case in the database...

Now you use the GUI table designer to create the table like: table_2( idx identity, lname varchar(100))

You open the SQL window and enter: select * from table_2 - error table not found. Cause it really is named "table_2"

Cool go to the GUI query tool, open it in sql view and enter: select * from table_1 - error because there is no "table_1" it is TABLE_1

LOL - it really is true, but really isn't that big a deal after a day or two...

Edit: Once again I am too slow to beat sliderule to the answer... *!&^&