HQL Exception.

[Rizwan Yousaf]

Hi everyone!
I�m using the following HQL to pull up records from Oracle 9i.
SELECT DISTINCT inv.invoiceId, per.surname, per.firstName, assig.assignmentNumber, assig.rate, assig.unit,
trunc(inv_pd.startDate), trunc(inv_pd.endDate), trunc(inv.invoiceDate), b_code.invoiceTimeDue, inv.status
, (SELECT SUM(units) FROM HpInvoiceDetails invoiceD WHERE invoiceD.invoice_id = inv.invoiceId) as conUnits
FROM HpInvoice inv, HpInvoiceDetails inv_d, HpBillingCode b_code, HpInvoicePeriodDates inv_pd,
HpAssignmentsD assig, HpPersonD per
WHERE inv_d.invoiceId = inv.invoiceId
AND assig.assignmentId = inv_d.assignmentId
AND per.personId = assig.personId
AND b_code.billingCodeId = assig.billingCodeId
AND inv_pd.billingCodeId = b_code.billingCodeId


But each time I execute, it comes up with following exception

[STDOUT] net.sf.hibernate.QueryException: aggregate function expected before ( in SELECT [SELECT DISTINCT inv.invoiceId, per.surname, per.firstName, assig.assignmentNumber, assig.rate, assig.unit, trunc(inv_pd.startDate), trunc(inv_pd.endDate), trunc(inv.invoiceDate), b_code.invoiceTimeDue, inv.status, (SELECT units FROM com.es.invoice.dto.fnd.HpInvoiceDetails invoiceD WHERE invoiceD.invoice_id = inv.invoiceId) as conUnits FROM com.es.invoice.dto.fnd.HpInvoice inv, com.es.invoice.dto.fnd.HpInvoiceDetails inv_d, com.es.billingcode.dto.fnd.HpBillingCode b_code, com.es.invoice.dto.fnd.HpInvoicePeriodDates inv_pd, com.es.assignment.dto.fnd.HpAssignmentsD assig, com.es.hr.person.dto.fnd.HpPersonD per where inv_d.invoiceId = inv.invoiceId AND assig.assignmentId = inv_d.assignmentId AND per.personId = assig.personId AND b_code.billingCodeId = assig.billingCodeId AND inv_pd.billingCodeId = b_code.billingCodeId]

Please let me know if I�m doing it wrong way. Any help or assistance would be highly appreciated.
Cheers!
Rizwan Yousaf