Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Subquery in select clause and "not a group by expression" error.

541243Nov 26 2008 — edited Nov 26 2008
Hi All,
Hopefully someone out there can help me out with this one. I have a sql query that performs a sub-query as part of the select cause combined with an aggregate function and is giving me a "not a group by expression".

I've created a simplified example
 SELECT COUNT(*)                     ,
  ai.invoice_num invoice_number      ,
  ai.description invoice_description ,
  (SELECT poh.po_header_id
       FROM po_headers poh ,
      po_distributions pod
      WHERE pod.po_distribution_id = aid.po_distribution_id
    AND pod.po_header_id           = poh.po_header_id
    ) po_number
     FROM ap_invoice_distributions aid ,
    ap_invoices ai
    WHERE ai.invoice_id = 1476932
  AND aid.project_id    = 8608   
  AND aid.task_id       = 462202  
  AND ai.invoice_id     = aid.invoice_id
 GROUP BY ai.invoice_num,
                ai.description;
I'm pretty sure that it is expecting me to add the po_number to the group by clause but this doesn't work and gives the error "invalid identifier".

Can anyone suggest a way to re-gig the query so that I can add po_number to my group by clause.

Kris
This post has been answered by Rod West on Nov 26 2008
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 24 2008
Added on Nov 26 2008
4 comments
17,120 views