[Solved] Help with simple SQL statement

Creating and using forms

[Solved] Help with simple SQL statement

Postby JWB23 » Wed Dec 05, 2018 8:33 pm

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
JWB23
 
Posts: 9
Joined: Wed Jun 20, 2018 12:02 am

Re: HELP! with simple SQL statement

Postby UnklDonald418 » Wed Dec 05, 2018 9:06 pm

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   Expand viewCollapse view
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1122
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: HELP! with simple SQL statement

Postby FJCC » Wed Dec 05, 2018 9:08 pm

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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7128
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: HELP! with simple SQL statement

Postby JWB23 » Thu Dec 06, 2018 10:04 pm

FJCC,

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

- thx
OpenOffice 4 on Windows 10
JWB23
 
Posts: 9
Joined: Wed Jun 20, 2018 12:02 am

Re: HELP! with simple SQL statement

Postby FJCC » Thu Dec 06, 2018 10:20 pm

Are you using the proper double quote character as explained by UnklDonald418?
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7128
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: HELP! with simple SQL statement

Postby Villeroy » Thu Dec 06, 2018 10:30 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26631
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HELP! with simple SQL statement

Postby UnklDonald418 » Thu Dec 06, 2018 11:39 pm

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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1122
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: HELP! with simple SQL statement

Postby JWB23 » Fri Dec 07, 2018 1:11 am

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
JWB23
 
Posts: 9
Joined: Wed Jun 20, 2018 12:02 am

Re: [solved] HELP! with simple SQL statement

Postby UnklDonald418 » Fri Dec 07, 2018 1:41 am

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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1122
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA


Return to Forms

Who is online

Users browsing this forum: No registered users and 3 guests