syntax error in SQL - error 1000

users
#1

I have a query which works fine. But when I edited it in the graphical query
editor LO refused to save it saying that there was an SQL error. The
detailed message was "syntax error, unexpected $end, expecting BETWEEN or IN
or SQL_TOKEN_LIKE". Even if I try to save the unedited version I get the
error, which I find a bit bizarre. But it runs OK and gives the correct
result.

The SQL for the query is

SELECT "Wines"."ID", "Wines"."Wine_Name" "Name", "Wines"."Year",
"Suppliers"."Name" "Supplier", "Wines"."Price", "Regions"."Region",
"Wines"."Bottles_Bought", "Wines"."Bottles_Left" "Left", "Wines"."Buy_Again"
"Buy", "Wines"."Avoid", "Wines"."Bin_End", "Wines"."Alcohol",
"Wines"."Acquired", "Wines"."Drink_By", "Wines"."Comments", "Styles"."Style"
FROM "Wines", "Suppliers", "Regions", "Styles" WHERE "Wines"."Supplier_Id" =
"Suppliers"."Id" AND "Wines"."Region_Id" = "Regions"."Id" AND
"Wines"."Style_Id" = "Styles"."Id" AND UCASE( "Wines"."Wine_Name" ) LIKE
UCASE( '%' || ( SELECT "filterstring" FROM "tblfilter" ) ) || '%'

I suspect that the problem is in the last clause.

The underlying database is HSQLDB.

#2

Hi Peter,

I have a query which works fine. But when I edited it in the graphical query> editor LO refused to save it saying that there was an SQL error. The>

detailed message was "syntax error, unexpected $end, expecting BETWEEN
or IN> or SQL_TOKEN_LIKE". Even if I try to save the unedited version I
get the> error, which I find a bit bizarre. But it runs OK and gives the

result.

This is the default-message when GUI doesn't know how to change the
code. You have designed the query after switchung from GUI to direct SQL
for adding
UCASE( "Wines"."Wine_Name" ) LIKE UCASE( '%' || ( SELECT "filterstring"
FROM "tblfilter" ) ) || '%'
and the query will work.
Could be better to change it a littel bit
UCASE( "Wines"."Wine_Name" ) LIKE '%' || UCASE( SELECT "filterstring"
FROM "tblfilter" ) || '%'
and to set the subquery for only one row, but could be it won't change
the behavior. Query will be opened for changing in GUI-Mode, but this
wouldn't work ...

Regards

Robert

#3

Thanks Robert

You are a mine of information.

Peter