Re: [libreoffice-users] Re: Base: Action Queries

Hi :)
I think i need to explore this a bit more tomorrow.  I tried both types of quotes and no quotes.  
Then i made the field names unique and all lower-case and i tried using a query as the source 
rather than a table.  Then i tried editing my Sql code to the most basic example on the page
http://www.techonthenet.com/sql/insert.php
but even once i took out the "Select" part the error message kept grumbling about it.  So i think i 
might need to write the code in Gedit or something and then copy&paste the code into a fresh query. 
 

I'm not quite sure what i'm doing wrong but that is not a big surprise.  I've got a bit of reading 
to do and some of that should make more sense now i've played around a bit.
Thanks all and regards from
Tom :)  





> ________________________________
> 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
> > >
> > >
> > >
> > > -- 
> > > For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
> > > Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > > Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> > > List archive: http://listarchives.libreoffice.org/global/users/
> > > All messages sent to this list will be publicly archived and cannot be deleted
>
>
> -- 
> For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
> Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/global/users/
> All messages sent to this list will be publicly archived and cannot be deleted
-- 
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted