Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] Question about Case Expressions in JQPL 
Hi Antonio,

  Try aliasing ex25_book.

e.g.

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

Antonio Goncalves wrote:

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 




------------------------------------------------------------------------

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

Back to the top