Search code examples
expressionhsqldbjdbctemplate

Trouble when using expression IN

Use: db: HSQLDB, jdbctemplate.

I created tables: 

    <SELECTED_DATE ID="1" DATE_VALUE="2018-02-02 00:00:00.000000"/>
    <SELECTED_DATE ID="2" DATE_VALUE="2018-02-03 00:00:00.000000"/>
    <SELECTED_DATE ID="3" DATE_VALUE="2018-02-04 00:00:00.000000"/>
    <SELECTED_DATE ID="4" DATE_VALUE="2018-02-05 00:00:00.000000"/>
    <SELECTED_DATE ID="5" DATE_VALUE="2018-02-07 00:00:00.000000"/>
    <SELECTED_DATE ID="6" DATE_VALUE="2018-02-08 00:00:00.000000"/>

How to use select * from SELECTED_DATE where DATE_VALUE in ( '2018-02-08', ... );

this causes an error: java.sql.SQLSyntaxErrorException: data type of expression is not boolean

or select * from SELECTED_DATE where DATE_VALUE IN ELEMENTS('2018-02-08', ...);

this causes an error: java.sql.SQLSyntaxErrorException: unexpected token: ELEMENTS required: (

but such select * from SELECTED_DATE where DATE_VALUE = ( '2018-02-08'); its work

Solution

  • Your date column is in fact a TIMESTAMP column.

    For IN predicates, you need to declare the type of the values:

    select * from SELECTED_DATE where DATE_VALUE in ( DATE'2018-02-02', DATE'2018-02-03');

    The above works. If your SELECT statement does not still work, please report the complete statement.