Re: [eclipselink-users] Question about Case Expressions in JQPL

[Antonio Goncalves <antonio.mailing@xxxxxxxxx>]

@Tom I don't understand the aliasing bit. You are aliasing with the table name (ex25_book), which does not work in a dynamic query.

@James I'm using Derby in-memory (version 10.5.3.0_1). Do you think I should try another database ?

Antonio

2010/1/26 James Sutherland <jamesssss@xxxxxxxxx>

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.

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

--
--
Antonio Goncalves (antonio.goncalves@xxxxxxxxx)
Software architect

Web site : www.antoniogoncalves.org
Blog: agoncal.wordpress.com
Feed: feeds2.feedburner.com/AntonioGoncalves
Paris JUG leader : www.parisjug.org
LinkedIn: www.linkedin.com/in/agoncal