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

The SQL seems correct.  What database are you using?



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


-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
http://www.eclipse.org/eclipselink/
 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink 
Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
http://wiki.oracle.com/page/TopLink TopLink 
Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
-- 
View this message in context: http://old.nabble.com/Question-about-Case-Expressions-in-JQPL-tp27280138p27324497.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.



Back to the top