JPA query with CASE WHEN in the WHERE clause. How to do?

Question

How can run a query like below with JPA. (It works with plain SQL)

SELECT t
FROM table t
WHERE
  (
    CASE WHEN (( ... subquery ... )  IS NULL)
    THEN (t.category IS NULL)
    ELSE (t.viewId = :viewId)
    END
  )

I get a MismatchedTokenException at IS

THEN (t.category IS NULL)

Is it possible? Or need I to rewrite this query?

Answer 1

You can convert your where clause to something like:

where
    ((... my first condition ...) and (something is NULL))
    or
    (not (... first condition ...) and (something2 = otherthing))

Answer 2

I think you can't do that. Have a read of the JPQL grammar (here courtesy of OpenJPA). Here are the terms defining the case expression, specifically the general case expression you're using:

• case_expression ::= general_case_expression | simple_case_expression | coalesce_expression | nullif_expression
• general_case_expression ::= CASE when_clause {when_clause}* ELSE scalar_expression END
• when_clause ::= WHEN conditional_expression THEN scalar_expression

The expression on the right-hand side of the THEN has to be a scalar_expression. Which is to say:

• scalar_expression ::= arithmetic_expression | string_primary | enum_primary | datetime_primary | boolean_primary | case_expression | entity_type_expression

I haven't rooted through the definition of all those terms, but i think they don't include things like (t.category IS NULL), i'm afraid.