Hmm, today we've checked that query using Hibernate EM, and it works.
Here is entire query:
SELECT l.id FROM Loan l, in (l.capitalAccount) c
WHERE l.loanStatus = ?1 AND SUM(c.amountDt) - SUM(c.amountCt) <
(SELECT ls.capitalDue FROM l.schedule ls
WHERE ls.dueDate =
(SELECT max(ls2.dueDate)
FROM l.schedule ls2 where ls2.dueDate<?2)
)
I know this might be not well designed/optimized query, but the key
problem here is this:
SUM(c.amountDt) - SUM(c.amountCt)
(when I change it to just one SUM(c.amountDt) it works)
There is no way of going around that problem, I need to compare the
account balance, which is sum of (amountDt-amountCt).
Can you tell me, if such a query is not supposed to work under pure/TopLink JPA?
If so, does it mean I have to change JPA to Hibernate? :(
I really would like to stay with TopLink...
2007/4/4, Witold Szczerba <pljosh.mail_at_gmail.com>:
> Hello there,
> I have to select from database entities, but there is no way of doing
> that without adding like this:
> SUM(x.fieldA - x.fieldB)
> or like this:
> SUM(x.fieldA) - SUM(x.fieldB)
>
> This select is a subquery. Without this simple operation, I will have
> to execute a query for every entity (it can be even 50000 entities).
> Is there any workaround for this? Do you have any ideas?
>
> p.s.
> I saw such an example of hibernate:
>
> select cat.weight + sum(kitten.weight)
> from Cat cat
> join cat.kittens kitten
> group by cat.id, cat.weight
>
> Is that illegal in TopLink's JPA?
>