[Solved] Help with simple SQL statement

Creating and using forms
Post Reply
JWB23
Posts: 9
Joined: Wed Jun 20, 2018 12:02 am

[Solved] Help with simple SQL statement

Post by JWB23 »

When I attempt to execute this command:

update “Scent_Oil_Master” set “SO_Rand_Nbr” = RAND()

I get this error:

1: Unexpected token: “ in statement [update ]

which means nothing to me. I can't figure out what I'm doing wrong. Can anyone help me?

thanks,

Jim
Last edited by JWB23 on Fri Dec 07, 2018 1:28 am, edited 2 times in total.
OpenOffice 4 on Windows 10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: HELP! with simple SQL statement

Post by UnklDonald418 »

It looks like you composed the SQL statement in Writer, which is an unfortunate choice. Writer uses different values for open and close quotation marks which are not understood by the HSQL database engine. The error message is complaining about the “ character/token.
Try

Code: Select all

update "Scent_Oil_Master" set "SO_Rand_Nbr" = RAND()
 Edit: Some further explanation.
Writer uses the Unicode value (U+201C) for the left double quotation mark and (U+201D) for the right double quotation mark.
HSQL expects the ASCII value (0x22) to enclose table and field names.
Among other places the editor found at Tools>SQL uses the ASCII value.
I often use the GUI found at Create Query in Design View to select all the fields I might need, then from the SQL view of the query, I copy and paste the field and table names into a Windows Notepad document where I compose the statement. Then I paste it into the editor at Tools>SQL.
Also note, when entering a statement in Tools>SQL, any properly constructed field or table name enclosed in double quotes will appear in yellow. Any other color will likely result in an execution error. 
Last edited by UnklDonald418 on Thu Dec 06, 2018 6:55 pm, edited 1 time in total.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: HELP! with simple SQL statement

Post by FJCC »

Are you executing this from the usual query pane, where you would enter a SELECT query, or from the menu Tools ->SQL. Only the latter will work.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
JWB23
Posts: 9
Joined: Wed Jun 20, 2018 12:02 am

Re: HELP! with simple SQL statement

Post by JWB23 »

FJCC,

I'm using menu Tools-->SQL. any ideas?

- thx
OpenOffice 4 on Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: HELP! with simple SQL statement

Post by FJCC »

Are you using the proper double quote character as explained by UnklDonald418?
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HELP! with simple SQL statement

Post by Villeroy »

You can avoid typographic quoting in Writer with menu:Tools>Language>...>None
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
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: HELP! with simple SQL statement

Post by UnklDonald418 »

When I copy the SQL statement I presented earlier and paste it into Tools>SQL it executes as expected and adds a random number in "SO_Rand_Nbr" for each row in "Scent_Oil_Master"
According to the HSQLDB User Guide:
RAND() returns a random number x bigger or equal to 0.0 and smaller than 1.0
so be sure that "SO_Rand_Nbr" is a numeric type with at least one decimal place (I used 4 in my test).
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
JWB23
Posts: 9
Joined: Wed Jun 20, 2018 12:02 am

Re: HELP! with simple SQL statement

Post by JWB23 »

I tried it again just now and it worked fine.
I'm baffled.
???
oh well, all I care about is that it works!
- thanks all
OpenOffice 4 on Windows 10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [solved] HELP! with simple SQL statement

Post by UnklDonald418 »

Nothing terribly baffling, your problem was caused by using the wrong double quotes in the SQL statement.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Post Reply