Oracle NVL function equivalent in JQPL in EclipseLink (Version 2.1.3)?

Question

I do this JPQL query

SELECT e 
  FROM Expediente e 
 WHERE e.fechaBaja is null 
 ORDER BY e.idSituacion ASC, 
          e.idExpediente ASC

but when e.idSituacion is null, eclipseLink not return this registry.

How could i do this query with oracle function nvl? Is Is EclipseLink 2.1.3 support this function?

SELECT  nvl(e.idSituacion,' ')  
  FROM Expediente e 
 WHERE e.fechaBaja is null 
 ORDER BY e.idSituacion ASC, 
          e.idExpediente ASC

or

SELECT e 
  FROM Expediente e 
 WHERE e.fechaBaja is null 
   and nvl(e.idSituacion,' ') 
 ORDER BY e.idSituacion ASC, 
       e.idExpediente ASC

Thank you.

Solution 1

It´s a eclipseLink Bug https://bugs.eclipse.org/bugs/show_bug.cgi?id=294092.

I need native sql, example http://tomaszdziurko.pl/2011/12/problem-withjpa-join-column-null-values-and-orderby/

Solution 2

COALESCE

I don't exactly see how it will help you, but you can use COALESCE function: http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#Functions

(This is similar toNVL, but NVL is Oracle specific, COALESCE is ANSI standard.)

Order

With ORDER BY e.idSituacion ASC null values will be at the end of the results.

If you want to have your null values first you can use NULLS FIRST clause:

ORDER BY e.idSituacion ASC NULLS FIRST

(Only from Eclipselink 2.4)