HSQL unexpected token in statement

Question

I have created in LibreOffice Base a simple database with few tables. I want to run the following query:

SELECT SUM( "Total price" ) AS "Expenses" FROM "Expenses" WHERE "the Name of the Ware" IS 'food' AND "Date" BETWEEN {d '2019-08-06' } AND {d '2019-08-20' }

but I get an error:

SQL state: 37000 Errorcode: -11

Unexpected token in statement [SELECT SUM( "Total price" ) AS "Expenses" FROM "Expenses" WHERE "the Name of the Ware" IS 'food' AND "Date" BETWEEN '2019-08-06' AND '2019-08-20' ]

As one can see I am trying to get SUM of "Total price" values from the Expenses table's records where "the Name of the Ware" is 'food' and the Date is between two given dates.

How can I achieve my goal?

Answer 1

The IS keyword is used only for NULL and TRUE or FALSE expressions, for example IS NULL or IS TRUE

You need to use the equal sign instead:

SELECT SUM( "Total price" ) AS "Expenses" FROM "Expenses" WHERE "the Name of the Ware" = 'food' AND "Date" BETWEEN {d '2019-08-06' } AND {d '2019-08-20' }