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.

score 9 · Answer 1 · answered Dec 03 '12 at 12:59

9

COALESCE

(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)

answered Dec 03 '12 at 12:59

Peter Bagyinszki

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/ – Ibram Dec 03 '12 at 15:56
The post you linked seems to have the solution. Why don't you use it? Is there any problem with that? How native `nvl` will help? – Peter Bagyinszki Dec 03 '12 at 19:29

score -1 · Accepted Answer · answered Dec 03 '12 at 15:58

-1

answered Dec 03 '12 at 15:58

Ibram

This answer is wrong - it is not a bug. If Expediente.idSituacion is a relational mapping, JPA requires that using e.idSituacion in a query result in an INNER JOIN, which then filters out nulls. As the 'feature' referenced states, if you want nulls included, you need to define the join as a left outer join, and use that in the order by clause. – Chris Feb 09 '22 at 17:44

2 Answers2