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
      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
        • 1. Re: Subquery in select clause and "not a group by expression" error.
          Walter Fernández
          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
            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
              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
                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.