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
[Solved] Help with simple SQL statement
[Solved] Help with simple SQL statement
Last edited by JWB23 on Fri Dec 07, 2018 1:28 am, edited 2 times in total.
OpenOffice 4 on Windows 10
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: HELP! with simple SQL statement
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
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: HELP! with simple SQL statement
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: HELP! with simple SQL statement
FJCC,
I'm using menu Tools-->SQL. any ideas?
- thx
I'm using menu Tools-->SQL. any ideas?
- thx
OpenOffice 4 on Windows 10
Re: HELP! with simple SQL statement
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: HELP! with simple SQL statement
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: HELP! with simple SQL statement
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).
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: HELP! with simple SQL statement
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
I'm baffled.
???
oh well, all I care about is that it works!
- thanks all
OpenOffice 4 on Windows 10
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: [solved] HELP! with simple SQL statement
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11