Hibernate version: 2.1.1

Name and version of the database you are using: Oracle 9i

I am trying to figure out how to, or a better way to do an inner select with an aggregate function to an alias. I have 3 tables and table 2 and 3 are children of table 1. I'm trying to do a sum() on a column in table 2 and 3 and compare the values returned. Then return table 1 with those sum() values of the 2 inner selects. I first tried assigning the value of the inner selects to an alias and added that alias to the outer select, but I get an exception where it doesn't recognize the alias. Here is the hql that I was trying to do:

select sc, expected_volume, actual_volume from SourceCode as sc where (select sum(invoice.quantity) from InvoiceDetail as invoice where sc.pk.ftfId = invoice.pk.ftfId and sc.pk.mailpieceId = invoice.pk.mailpieceId and sc.pk.sourceCodeId = invoice.pk.sourceCodeId) as actual_volume > (select sum(dist.volume) from Distribution as dist where sc.pk.ftfId = dist.pk.ftfId and sc.pk.mailpieceId = dist.pk.mailpieceId and sc.pk.sourceCodeId = dist.pk.sourceCodeId and dist.pk.startDate < sysdate) as expected_volume and sc.mailpiece.companyId = :companyId

Does anyone have any suggestions of a way to make this work?

thanks in advance.