Page 1 of 1

[Solved] Help with simple SQL statement

PostPosted: Wed Dec 05, 2018 8:33 pm
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

Re: HELP! with simple SQL statement

PostPosted: Wed Dec 05, 2018 9:06 pm
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   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. 

Re: HELP! with simple SQL statement

PostPosted: Wed Dec 05, 2018 9:08 pm
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.

Re: HELP! with simple SQL statement

PostPosted: Thu Dec 06, 2018 10:04 pm
by JWB23
FJCC,

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

- thx

Re: HELP! with simple SQL statement

PostPosted: Thu Dec 06, 2018 10:20 pm
by FJCC
Are you using the proper double quote character as explained by UnklDonald418?

Re: HELP! with simple SQL statement

PostPosted: Thu Dec 06, 2018 10:30 pm
by Villeroy
You can avoid typographic quoting in Writer with menu:Tools>Language>...>None

Re: HELP! with simple SQL statement

PostPosted: Thu Dec 06, 2018 11:39 pm
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).

Re: HELP! with simple SQL statement

PostPosted: Fri Dec 07, 2018 1:11 am
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

Re: [solved] HELP! with simple SQL statement

PostPosted: Fri Dec 07, 2018 1:41 am
by UnklDonald418
Nothing terribly baffling, your problem was caused by using the wrong double quotes in the SQL statement.