Hi guys,
I have a calulated column in my bean which fetches data from 2 tables using unions and sums.
Instead of executing the query for all my VOs inside a list and populating a transient field, I would like to use the @Formula annotation to fetch all data in a single query.
This query works fine when executed as a Hibernate SQLQuery:
Code:
select nvl(sum(resa), 0) as resa
from (
select sum(nvl(q1,0)) resa from (
select sum(nvl(qreso9621,0)) q1
from tbl_9621
where coddl9621 = 214
and crivw9621 = 986
and idtn9621 = 687074
and idtn9621 > 0
union all
select sum(nvl(qreso9622,0)) q1
from tbl_9622
where coddl9622 = 214
and crivw9622 = 986
and idtn9622 = 687074
and idtn9622 > 0 )
union all
select -1 * sum(nvl(qvend9625,0)) resa
from tbl_9625
where coddl9625 = 214
and crivw9625 = 986
and idtn9625 = 687074
)
but when I execute the same query using @Formula, Hibernate inserts aliases in the query (ex. bollaresad0_) which cause Oracle to issue an "ORA-00923: FROM keyword not found where expected".
This is the query Hibernate generates:
Code:
select nvl(sum(bollaresad0_.resa), 0)
from (
select sum(nvl(bollaresad0_.q1,0)) bollaresad0_.resa
from (select sum(nvl(bollaresad0_.qreso9621,0)) bollaresad0_.q1
from tbl_9621 where bollaresad0_.coddl9621 = 214
and bollaresad0_.crivw9621 = 986
and bollaresad0_.idtn9621 = 687074
and bollaresad0_.idtn9621 > 0
union all
select
sum(nvl(bollaresad0_.qreso9622,0)) bollaresad0_.q1
from tbl_9622
where bollaresad0_.coddl9622 = 214
and bollaresad0_.crivw9622 = 986
and bollaresad0_.idtn9622 = 687074
and bollaresad0_.idtn9622 > 0 )
union all
select -1 * sum(nvl(bollaresad0_.qvend9625,0)) bollaresad0_.resa
from tbl_9625
where bollaresad0_.coddl9625 = 214
and bollaresad0_.crivw9625 = 986
and bollaresad0_.idtn9625 = 687074)
) as formula1_
Is there a way to force Hiberante to execute the Formula SQL query exactly as it is?
Thanks.
Login
FAQ
Search