syntax error in SQL - error 1000

classic Classic list List threaded Threaded
3 messages Options
ptoye ptoye
Reply | Threaded
Open this post in threaded view
|

syntax error in SQL - error 1000

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.







-----
Peter
--
Sent from: http://nabble.documentfoundation.org/Users-f1639498.html

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Peter
Robert Großkopf Robert Großkopf
Reply | Threaded
Open this post in threaded view
|

Re: syntax error in SQL - error 1000

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
correct> 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
--
Homepage: http://robert.familiegrosskopf.de
LibreOffice Community: http://robert.familiegrosskopf.de/map_3


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
ptoye ptoye
Reply | Threaded
Open this post in threaded view
|

Re: syntax error in SQL - error 1000

Thanks  Robert

You are a mine of information.

Peter



-----
Peter
--
Sent from: http://nabble.documentfoundation.org/Users-f1639498.html

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Peter