Win a copy of Java Mock Exams (software) this week in the Programmer Certification (OCPJP) 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
 
What are you doing? You are supposed to be reading this tiny ad!
the new thread boost feature brings a LOT of attention to your favorite threads
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!