Calculations on multiple subqueriy results in 1 HQL query

§upernova§oftware · **Posted:** Fri Jul 28, 2006 2:54 pm

I am trying to use the results or two subqueries in a simple calculation. What I need to do is limit the results in the where clause to

Code:

where reclassify.numpieces -
          numCommitted - (I could find no way to alias the subqueries and have no problem repeating the subquery here as I did in the full query below)
          numCompleted                          

I can tweak the generated sql easily to do the simple subtraction of the subquery results, but I want this to work in HQL. Any help would be greatly appreciated.

Code:

select reclassify as reclassify,
      (
        select coalesce(sum(c.numPieces),0) 
       from Coupling c
        where c.state.id=reclassify.id
      ) as numCommitted,  
 
       (
         select coalesce(sum(c2.numPieces),0) 
         from Coupling c2 
         where c2.state.parentState.id=reclassify.id 
       ) as numCompleted                          
from Reclassify reclassify
where (
        select coalesce(sum(c.numPieces),0)
        from Coupling c 
        where c.state.id=reclassify.id
      )>0