I have the following Problem. I want to filter on a column in my table to either be true or if false then only give me the entries with a date greater than xxx. So basicly return me every Customer that is a valid customer and return alle Customers that are not valid and have a activationDate greater then 3.5 years.

I tried the query inside my psql console and there everything is working fine. But when im trying to adapt it to be used with @NamedQueries in my JPA Entity the query cant be validated (neither in Intellij nor while running the code).

Query in Postgres (WORKING FINE):

SELECT * FROM customer c
WHERE (c.is_customer = true) OR (c.is_customer = false AND c.activation_date >  CURRENT_DATE   INTERVAL '3.5 years');

Query in Java (NOT WORKING):

@NamedQuery(name = CustomerBE.NAMED_QUERY,
                query = "SELECT c FROM CustomerBE c "
                         "WHERE (c.isCustomer= true) OR (c.isCustomer= false AND c.activationDate > CURRENT_DATE   INTERVAL '3.5 years')"
)

Intellij Error: '(', <expression> or identifier expected, got '('

JPQL Error:

Exception Description: Syntax error parsing [SELECT c FROM CustomerBE c WHERE (c.isCustomer= true) OR (c.isCustomer= false AND c.activationDate > CURRENT_DATE   INTERVAL '3.5 years']. 
[41, 297] The expression is not a valid conditional expression.
[297, 298] The query contains a malformed ending

I already tried adaption the bracket placement. Did someone maybe have a simmilar problem or a workaround solution for me?

CodePudding user response：

You probably need to put a space before the WHERE clause or else your string will not be a valid JPQL:

"SELECT c FROM CustomerBE cWHERE (c.isCustomer= true) O ..."

I'm not sure if this is the problem you reported, but it is also a problem to be fixed.

CodePudding user response：

Ok fixed it now. The Problem was the postgre Part "CURRENT_DATE INTERVAL '3.5 years'" had to substitute it with :filterDate and set it through the parameter.