CASE WHEN...THEN...ELSE...END not working using JPQL/JDOQL

When I try to use the "case...when...then...else...end" like in the following query "SELECTCASE WHEN g.name="a" THEN "e" ELSE "o" END g from Guest g", I get the issue:

Unexpected query token 'CASE'

SELECT  ==> CASE <==  WHEN g.name="a" THEN "e" ELSE "o" END g from Guest g

Have I mismatched something or is it an issue with ObjectDB? Anyone could help me on how to use the "case" in JPQL?

Thank you in advance.

This is one of the JPA 2.1 features that ObjectDB does not support yet.

Thank you. Do you know what alternative should I use to have it worked?

thank you in advance.

In your specific sample query:

SELECT CASE WHEN g.name="a" THEN "e" ELSE "o" END g from Guest g

You can add a new field to Guest and maintain it with either the value "e" or "o" depending on the value of the name field. This may be the most efficient solution regarding query execution, since the new field can also be indexed and used in WHERE clauses efficiently.

Other possible solutions:

• Using a method in the query - ObjectDB supports user defined methods in queries as an extension to JPA.
• Running an incomplete query with a simple SELECT g and then adjusting the results in a Java code loop.

Thank you for your reply.

Could you give me a sample code on how to use a method in the query? ( I could not add a new field to the class because it is a Map which handle dynamic key)

See this forum thread.

Thank you. I really appreciate your help.

By the way, is ObjectDB efficient for a key/value storage: the object to store and to deal with are Map and MultiMap?

It is efficient for general object persistence, including maps, although for key/value storage only you may want to consider a pure key/value database.