Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

HQL Exception.

 
Rizwan Yousaf
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic