These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]

Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: Complex Query in Formula Annotation
PostPosted: Fri Oct 22, 2010 12:14 pm 

Joined: Fri Oct 22, 2010 11:56 am
Posts: 1
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:
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:
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
      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?

Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.