4 Replies Latest reply on Nov 26, 2008 10:55 AM by 541243

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

    541243
    541243 Nov 26, 2008 10:31 AM
      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
      • 3804 Views
      • Tags:
        • 1. Re: Subquery in select clause and "not a group by expression" error.
          Walter Fernández
          Walter Fernández Nov 26, 2008 10:41 AM (in response to 541243)
          Hi,

          Try this (not tested):
          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,
          po_number;
          Note: You have to include in the GROUP BY clause all SELECT expressions that are not group function arguments.

          Regards,

          Edited by: Walter Fernández on Nov 26, 2008 8:40 AM - Adding note
          • 2. Re: Subquery in select clause and "not a group by expression" error.
            Rod West
            Rod West Nov 26, 2008 10:38 AM (in response to 541243)
            You should try either:

            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, aid.po_distribution_id;

            or

            SELECT COUNT(*) ,
            ai.invoice_num invoice_number ,
            ai.description invoice_description ,
            MAX((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;
            • 3. Re: Subquery in select clause and "not a group by expression" error.
              EnjoyToHelp
              EnjoyToHelp Nov 26, 2008 10:50 AM (in response to 541243)
              Hi Jones,

              U can modify ur query as bellow..........


              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,(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) ;


              I think it will solve ur problem......


              Regards,
              Dip
              • 4. Re: Subquery in select clause and "not a group by expression" error.
                541243
                541243 Nov 26, 2008 10:55 AM (in response to EnjoyToHelp)
                And the winner is.....

                Rod, Thanks for your quick reply. Both work as expected but I prefer the Max() approach, just seems more readable to me.

                Dip, Unfortunatley your not allowed sub-queries in the group by clause but thanks for having ago.