Hi everyone! I�m using the following HQL to pull up records from Oracle 9i.
QUERY ====== 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.
I don't quite see the need for the subquery in there.
"(SELECT SUM(units) FROM HpInvoiceDetails invoiceD WHERE invoiceD.invoice_id = inv.invoiceId) as conUnits"
is you just had SUM(inv_d), you would probably just need to add Group by clause. The other thing I just noticed as typing this is the subquery does not have the alias attached in the SUM, so that would need to be SELECT SUM(invoiceD.units) FROM.......
0
