select with field in empty expression list

Question

Those query work:

SELECT field FROM table WHERE id IN (1,2,3);
SELECT field FROM table WHERE id IN (1);

Why do I have error in this query? (ORA-00936: missing expression)

SELECT field FROM table WHERE id IN ();

how can I say "empty set"?

I have this application that recieve the ids from the user, and they can send 0,1, or as many as they want.

I normally use hibernate and HQL syntax but I have the equivalent error at the end with empty list

Maybe [this](https://stackoverflow.com/a/63658492/6277104) will be helpful. — SternK, Sep 04 '20 at 10:07
Would you please let us know how exactly you are receiving the inputs from the user. Eg: Is it a comma separated list in single quote ? Like '1,2,3,4'. If yes, then what is the input the if they don't send any meaning is it just '' (i.e., single quote with nothing in-between )? — Ranagal, Sep 04 '20 at 12:27

score 0 · Answer 1 · answered Sep 04 '20 at 10:15

The root cause of the issue here is that, under the hood, Hibernate is just using JDBC prepared statements. So, your query is actually being evaluated as something like:

SELECT field FROM table WHERE id IN (?);

The problem here is that when you try to bind nothing, you seem to be getting a syntax error.

The best workaround here I believe would be to just check if the collection be empty or not before even executing the query:

String field = null;
List<Integer> ids = new ArrayList<>();   // leave it empty

if (field != null) {
    // execute your HQL query, using ids
    // assign result to field
}

select with field in empty expression list

1 Answers1