jpql exception ORA-00932: inconsistent datatypes: expected BINARY got DATE

Question

I wrote the below JPQL.

select e.name from emp e where e.date = nvl(:date,select max(date) from emp e where e.id=1);

When I pass the date value, it should run the JPQL with the given date value. If I don't pass any date value then the JPQL should run with max(date).

But getting an exception inconsistent datatypes.

Observations :

If I change the query to :

select e.name from emp e where e.date = :date

It is working fine. But when I use nvl I'm getting the exception.

Also how are you passing param? Can you include the java code in your question? — Vasan, Mar 14 '18 at 23:54
@query(select e.name from emp e where e.date = nvl(:date,select max(date) from emp e where e.id=1) findemp(@param Date date) that is how my repo will look like. In service layer I will have below code findemp("2012-12-12") — Naresh, Mar 15 '18 at 00:01
But "2012-12-12" is a string - do you parse Date object from it? Otherwise your code looks fine. Try [logging generated SQL](https://stackoverflow.com/questions/14358934/how-to-get-the-jpa-generated-sql-query) to console, which might provide some clues. — Vasan, Mar 15 '18 at 00:17
yeah I'm parsing it to date and then passing that date to the method. — Naresh, Mar 15 '18 at 00:50
yeah generated the sql .. and I ran the sql in the db.. It is working fine.. — Naresh, Mar 15 '18 at 00:56
I guess you could try a couple of things - 1) Try COALESCE instead of NVL (has same effect) and 2) Try making it into a native SQL query instead of the default JPQL. I don't know why your code doesn't work though - as I said I don't see any issues with it. — Vasan, Mar 15 '18 at 01:10
@Vasan Thanks Vasan for your inputs.. I tried using COALESCE, even though I'm getting the same issue.. Will there be any problem with the JPA version.. The JPA version I'm using is 1.7 — Naresh, Mar 15 '18 at 12:15

score 0 · Answer 1 · answered May 18 '22 at 13:20

Using the NamedQuery can be helpful to you that works very well with above type of WHERE conditions.

For example:

Entity

Person.java

    @NamedQuery(name="get_all_persons", 
    query="select p from Person p"
            + " where p.birthDate = nvl(:date, (select max(birthDate) from Person))")

Repository:

PersonJpaRepository.java

public List<Person> findAll() {
    TypedQuery<Person> namedQuery = entityManager.createNamedQuery("get_all_persons", Person.class);
    //namedQuery.setParameter("date", "2022-05-18");
    namedQuery.setParameter("date", null);
    return namedQuery.getResultList();
}

score 0 · Answer 2 · answered May 19 '22 at 07:12

Since you use spring-data-jpa your solution might be as simple as upgrading to the latest version (2.7.0 at the moment), since this might be resolved by the fix for this issue.

If you are not using Spring Data JPA you should be able to do what was done in the fix and use TypedParameterValue to pass null values. This will should pass type information to the database avoiding the arbitrary misattribution of types to null values.

An alternative approach would be to use a CAST.

Since you did only post a tiny fraction of the exception there is no way telling what might be the correct types and where to exactly put the cast in the query.

Possibly the best approach would be to have two queries, one with the argument present, one without it and decide which one to call on the java side depending on the value of your argument.

jpql exception ORA-00932: inconsistent datatypes: expected BINARY got DATE

2 Answers2