[eclipselink-users] Question about Case Expressions in JQPL

[Antonio Goncalves <antonio.mailing@xxxxxxxxx>]

Hi all,

In JPA 2.0 we can now use CASE _expression_ in JPQL. I was having a look at the spec (§4.6.17.3) but can't really make my example work.

Let's say there's a Book entity with an id, isbn, price and an editor. When you want to get all the prices of the books, you'll do :

  Select b.price from Book b 

Easy. Now, for Chistmas, you want to have 50% discounts on all the books from Apress editor and 20% for all the other editors. How would you write that ? I've tried :

SELECT b.price, CASE b.editor

                     WHEN 'Apress' THEN b.price * 0.5

                     ELSE b.price * 0.8

                END

FROM Book b 

This doesn't work and gives me the following exception.

Any idea how to write such a statement ?

Thanks,

Antonio

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.0.v20091127-r5931): org.eclipse.persistence.exceptions.DatabaseException

Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "EDITOR" at line 1, column 20.

Error Code: 30000

Call: SELECT PRICE, CASE EDITOR WHEN 'Apress' THEN (PRICE * 0.5) ELSE (PRICE * 0.8) END FROM ex25_book ORDER BY ISBN ASC

Query: ReportQuery(referenceClass=Book25 sql="SELECT PRICE, CASE EDITOR WHEN ? THEN (PRICE * ?) ELSE (PRICE * ?) END FROM ex25_book ORDER BY ISBN ASC")

Local Exception Stack: 

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.0.v20091127-r5931): org.eclipse.persistence.exceptions.DatabaseException

Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "EDITOR" at line 1, column 20.

Error Code: 30000

Call: SELECT PRICE, CASE EDITOR WHEN 'Apress' THEN (PRICE * 0.5) ELSE (PRICE * 0.8) END FROM ex25_book ORDER BY ISBN ASC