http://stackoverflow.com/questions/4770466/sql-dialect-in-openoffice-base/9315951#9315951 I'm familiar with SQL in SQLite and MySQL, but OpenOffice Base seems to be either very crippled, or I don't understand how to execute raw SQL. I want to do (effectively) this: INSERT INTO t2 SELECT NULL as id, t.foo, t.bar, '' as baz, 0 as quux FROM MyTable t All I can do is SELECT t.foo, t.bar, '' as baz, 0 as quux FROM MyTable t because the NULL seems to give Base confusion, as does the INSERT INTO T2 SELECT ... syntax. Can anyone suggest how I need to fix this?
Indeed, NULL is not accepted by the LibreOffice SQL parser in place of a column name in a SELECT statement. That's a bug. I'll try to get it fixed for 3.5.1, else for 3.5.2. In the meantime, I think this syntax should work: INSERT INTO t2 (foo, bar, baz, quux) SELECT t.foo, t.bar, '' as baz, 0 as quux FROM MyTable t Where "(foo, bar, baz, quux)" refers to column names in the t2 table (in case they differ from the names in the SELECT statement).
(In reply to comment #1) > In the meantime, I think this syntax should work: > > INSERT INTO t2 (foo, bar, baz, quux) SELECT t.foo, t.bar, '' as baz, 0 as quux FROM MyTable t Ah no, "INSERT INTO (...) SELECT" is not supported at all. I had missed that you have *two* problems in my original reading. Cloned the bug for the second issue.
Lionel Elie Mamane commited a patch related to this issue to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=6f3ebfc4c78da67f72befa3bde55dde4d77d5e3a fdo#46198: NULL is a perfectly fine general_value
EMPTY is the string recognized by the the built in parser - good to see it finally also recognizes NULL for those back ends that support it.
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "libreoffice-3-5": http://cgit.freedesktop.org/libreoffice/core/commit/?id=3a61c9f1c51893b242ccf5bb20163b72721a2bf0&g=libreoffice-3-5 fdo#46198: NULL is a perfectly fine general_value It will be available in LibreOffice 3.5.1.
Fix is in 3.5, 3.6, there will be no 3.4.7, so I closed tis one. Please feel free to reopen if I did wrong.