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

Gia-Khanh-Oracle

In listing 32 the command has a typo, it should read

root@sapm7adm-haapp-0201:~# sysconfig create-profile -o /net/sapm7-h2-storIB/export\

/SOFTWARE/software/util/zc/sc_profile -g location,identity,naming_services,users
1 - 1
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,031 views