[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] Question about Case Expressions in JQPL
|
Hi Christopher,
Indeed, the following works :
query = em.createQuery("SELECT CASE WHEN b.editor ='Apress' THEN b.price * 0.5 ELSE b.price * 0.8 END FROM Book b");
But not the following :
query = em.createQuery("SELECT CASE b.editor WHEN 'Apress' THEN b.price * 0.5 ELSE b.price * 0.8 END FROM Book b");
A shame. I'll give it a try with another database.
Thanks,
Antonio
2010/2/2 christopher delahunt
<christopher.delahunt@xxxxxxxxxx>
Hello Antonio,
You can try another database, or change the statement slightly. Derby should support using something like:
"SELECT b.price, CASE WHEN b.editor ='Apress' THEN b.price * 0.5 ELSE b.price * 0.8 END FROM Book b"
My understanding is that Derby doesn't support simple case but does have case support where the condition is in the when clause.
Best Regards,
Chris
Antonio Goncalves wrote:
@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 <mailto: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
Software architect
Web site : www.antoniogoncalves.org <http://www.antoniogoncalves.org>
Blog: agoncal.wordpress.com <http://agoncal.wordpress.com>
Feed: feeds2.feedburner.com/AntonioGoncalves <http://feeds2.feedburner.com/AntonioGoncalves>
Paris JUG leader : www.parisjug.org <http://www.parisjug.org>
LinkedIn: www.linkedin.com/in/agoncal <http://www.linkedin.com/in/agoncal>
------------------------------------------------------------------------
--
--
Antonio Goncalves (
antonio.goncalves@xxxxxxxxx)
Software architect
Web site :
www.antoniogoncalves.org
Blog:
agoncal.wordpress.comFeed:
feeds2.feedburner.com/AntonioGoncalvesParis JUG leader :
www.parisjug.org
LinkedIn:
www.linkedin.com/in/agoncal
