How find an HSQLDB Syntax error?

English
,
#1

I am editing a Base Query. (Base 25.2.4.3 on Windows 11 Pro.)

I get a SQL Syntax error when I try to save.

SQL Status: HY000
Error code: 1000
Syntax error in SQL statement
  1. Are these codes documented anywhere?
  2. Anyone want to venture a guess what is wrong with my statement? Route, Order, Last and Address are all columns in Clients.

SELECT "Route", "Order" + '-' + "Last" as "Label", "Address" + ', Sonoma, CA' AS "Full_Addr" FROM "Clients" WHERE ( "Route" = 'A' OR "Route" = 'B' OR "Route" = 'C' OR "Route" = 'D' OR "Route" = 'E' OR "Route" = 'F' ) ORDER BY "Route" ASC, "Order" ASC

Thanks!

#2

It’s the 'literals' that seem to be the problem. I have used "column" + 'literal' + "column" in other contexts – why not here?

#3

Not all of us have a built-in SQL translator in our heads. :slight_smile:
Could you upload the problematic file?

#4

What file? I posted the entire SQL statement. The only other “file” is my database including all of the embedded data. What can I tell you about it? Clients is a table. Route, Order, Last, etc. are all columns in that table.

#5

Make a copy of the .odb file, delete data from all tables (if it is confidential information) and upload the new .odb file here.

#6

First guess: use || instead of + or maybe concat()

Second guess: If you use hsqldb, look there:

https://hsqldb.org/doc/guide/builtinfunctions-chapt.html

#7
1 Like
#8

For internal databases of Base: Have a look at Base Guide - Database Tasks

Concatening for internal HSQLDB allows CONCAT, || and +.

The code in the original post couldn’t be read completely. So I couldn’t guess where the problem appears. The brackets in the WHERE clause are needless…

#9

Base does not accept + as concatenation operator when it parses a query. Use || instead.

#10

Actually

QUERY takes "column" + "column" and "column" || 'literal' but not "column" + 'literal'

Form field initialization takes "column" + "column" and "column" + 'literal' but not "column" || 'literal'

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.