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

Problem with Hibernate Query

 
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.

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.

Cheers!
Rizwan Yousaf
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.......

Mark
 
Rizwan Yousaf
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I got your point. Thanks a lot, I really appreciate you help!!!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic