________________________________
From: Dan Lewis <elderdanlewis@gmail.com>
To: users@global.libreoffice.org
Sent: Friday, 11 January 2013, 23:08
Subject: Re: [libreoffice-users] Re: Base: Action Queries
I wonder about the last line of your code:
WHERE OrgName IS NOT NULL;
Above this line you used "OrgName"
Your field has upper and lower case letters. When this is the
case, you must enclose the field's name in double quotes. So, I'm
thinking that this is likely the problem.
A quote from the HSQLDB Guide (version 1.8):
name
The character set for quoted identifiers (names) in HSQLDB is Unicode.
A unquoted identifier (name) starts with a letter and is followed by any
number of AS-
CII letters or digits. When an SQL statement is issued, any lowercase
characters in un-
quoted identifiers are converted to uppercase. Because of this, unquoted
names are in
fact ALL UPPERCASE when used in SQL statements. An important implication
of this
is the for accessing columns names via JDBC DatabaseMetaData: the
internal form,
which is the ALL UPPERCASE must be used if the column name was not
quoted in the
CREATE TABLE statement.
Quoted identifiers can be used as names (for tables, columns,
constraints or indexes).
Quoted identifiers start and end with " (one doublequote). A quoted
identifier can con-
tain any Unicode character, including space. In a quoted identifier use
"" (two double-
quotes) to create a " (one doublequote). With quoted identifiers it is
possible to create
mixed-case table and column names.
--Dan
On 01/11/2013 04:42 PM, Tom Davies wrote:
Hi :)
Thanks guys :) Ok, so i am starting out with the embedded database against all advice but just so that i can keep
emailing it to myself easily until i get to grips with it better. Then i hope to export this table to whatever small
back-end seems best. My Sql coding gets rejected "Syntax error in SQL expression" and then these "SQL
Status: HY000 Error code: 1000 Syntax error in SQL expression"
"SQL Status: HY000 Error code: 1000"
"SQL Status: HY000 Error code: 1000 syntax error, unexpected $end, expecting BETWEEN or IN or
SQL_TOKEN_LIKE"
Here's 'my' code
INSERT INTO OrgTable
("IdOfOrg", "IdOfPerson", "OrgName")
SELECT ("IdOfOrg", "IdOfPerson", "OrgName")
FROM "zzQuery1"
WHERE OrgName IS NOT NULL;
Regards from
Tom :)
________________________________
From: Alex Thurgood <alex.thurgood@gmail.com>
To: users@global.libreoffice.org
Sent: Friday, 11 January 2013, 20:46
Subject: [libreoffice-users] Re: Base: Action Queries
Le 11/01/2013 21:28, Tom Davies a écrit :
Hi Tom,
I've exported a tiny part of the data, and ended up with 2 columns
ClientID
CompanyName
Not all clients have a company name so there are a lot of blanks in the 2nd column. I've written a
query to just show the non-blanks (=non-nulls) but now i want to make that into a table in it's own
right. Is there an easy way to get a query to create the table for me or should i export as a Csv?
This might help
http://www.techonthenet.com/sql/insert.php
using INSERT ... SELECT
Alex