Select nvl(max(c.EmployeeId),0) in JPQL?

Question

I'm using oracle10g database and eclipselink, I need to obtain the last inserted key from the table so i've created this query

javax.persistence.Query q =   
                    em.createQuery("SELECT nvl(MAX(c.myAtt),0) " +   
                        "from myTable as c");   
             return Integer.parseInt(q.getSingleResult().toString());   `

But when the table is empy(sometimes it might get empty) i'm getting ILEGAL ARGUMENT EXCEPTION, cause: JPQL Exception, detail: "An exception occurred while creating a query in EntityManager". What i'm doing wrong?

Does the problem occur at em.createQuery(), or at q.getSingleResult()? Can you set appropriate logging level to see the generated SQL (Hibernate has this, not sure about EclipseLink)? — javashlook, Aug 01 '09 at 12:24

score 2 · Answer 1 · edited Sep 25 '12 at 06:24

2

NVL() is supported now in newer versions of jpql

edited Sep 25 '12 at 06:24

athspk

6,722
7
37
51

answered Sep 05 '12 at 21:28

ash123

197
1
3
10

score 2 · Answer 2 · edited Jun 28 '13 at 15:32

2

You could use the COALESCE function. It can be used to achieve the same as nvl. For instance:

select nvl(columna,'1') from table
select COALESCE(columna,'1') from table

edited Jun 28 '13 at 15:32

Xavi López

27,550
11
97
161

answered Jun 28 '13 at 14:10

user2532187

121
1
3

Robar · Answer 3 · 2012-03-14T21:27:49.013

2

In the meanwhile somebody else could have inserted something in Autorizaciones and then you receive the wrong id

edited Mar 14 '12 at 21:27

answered May 05 '10 at 12:06

Robar

1,929
2
30
61

score 1 · Answer 4 · answered Aug 01 '09 at 17:45

Paraphrasing Apu, "I don't know what part of that question to correct first" :-)

First of all, retrieving last inserted key in this way is a VERY BAD THING © It's dangerous, inefficient and most of all, unnecessary as your JPA already does it for you: once you insert your entity, its identifier property will automatically be updated to contain its primary key.

Secondly, as far as your query goes, "myTable" is not something you would use in JPQL, you need to specify your entity name instead (e.g. if you're mapping "Car" to "CAR_TABLE" you should use "Car" instead of "CAR_TABLE" in JPQL queries).

Finally, NVL() is not supported by JPQL. It is supported (sort of, via Expression.ifNull()) by EclipseLink Expressions. Not that you'd need it in a scenario like this, anyway.

Select nvl(max(c.EmployeeId),0) in JPQL?

4 Answers4