Oracle子查询含OR需GROUP BY？ORA-00979报错原因及解决问询

错误原因分析 #

对比两个子查询的差异就能明白问题所在：

1. 第一个子查询仅关联aia.po_header_id，而aia.po_header_id已经在你的GROUP BY列表中。这意味着每个分组内aia.po_header_id的值是固定的，子查询结果在分组内唯一，Oracle能确定它属于分组维度，无需加入GROUP BY。
2. 第二个子查询用OR关联了aia.po_header_id和ail.po_header_id，但ail.po_header_id不在GROUP BY列表中。由于一张发票（aia）对应多行发票行（ail），同一分组内不同行的ail.po_header_id可能不同，导致子查询在同一个分组里可能返回多个值。Oracle无法确定该取哪个值作为分组结果，因此触发ORA-00979: not a GROUP BY expression错误，要求将该子查询加入GROUP BY。但子查询是动态计算的，无法直接加入GROUP BY，必须调整写法。

解决方案 #

提供两种可行的解决思路：

思路1：用聚合函数包裹子查询 #

将PO_Number的子查询用MAX()或MIN()包裹，转换成聚合表达式，这样Oracle就不需要将其加入GROUP BY。示例：

MAX((SELECT pha.segment1
     FROM   po_headers_all pha
     WHERE  (pha.po_header_id = aia.po_header_id or pha.po_header_id = ail.po_header_id) 
       and rownum=1)) PO_Number

注：rownum=1已经保证子查询返回单行，用MAX()只是为了满足聚合语法要求，不会改变结果。

思路2：将PO关联移到FROM子句（更推荐） #

避免在SELECT中写相关子查询，通过LEFT JOIN提前关联PO表获取每个发票行对应的PO编号，再在分组时处理。这种写法更清晰，性能也更优。

修改后的完整查询如下：

--BI report query
SELECT :P_Trigger_ID                                    KEY            ,
       gl_flexfields_pkg.Get_description_sql(gcc.chart_of_accounts_id, 1,
       gcc.segment1)                                            Descr,
       -- 取分组内第一个匹配的PO编号，优先用发票级别的PO
       MAX(CASE WHEN pha.po_header_id = aia.po_header_id THEN pha.segment1 ELSE pha.segment1 END) PO_Number,
       aia.invoice_num Invoice_Number,
       psv.segment1 Vendor_Number,
       psv.vendor_name Vendor_Name,
       sum(aid.amount) Freight_Charge,
       (SELECT hr.location_code
        FROM   po_headers_all po,
               hr_locations_all hr
        WHERE  po.po_header_id = aia.po_header_id
               AND po.ship_to_location_id = hr.location_id ) Facility_Code,
       gcc.segment1 Entity,
       gcc.segment2 Location,
       gcc.segment3 Cost_Centre,
       gl_flexfields_pkg.Get_description_sql(gcc.chart_of_accounts_id, 3,
       gcc.segment3) Department_Name,
       (SELECT hr.address_line_1
        FROM   po_headers_all po,
               hr_locations_all hr
        WHERE  po.po_header_id = aia.po_header_id
               AND po.ship_to_location_id = hr.location_id  ) Facility_Address1,
       (SELECT hr.address_line_2
        FROM   po_headers_all po,
               hr_locations_all hr
        WHERE  po.po_header_id = aia.po_header_id
               AND po.ship_to_location_id = hr.location_id ) Facility_Address2,
       (SELECT hr.town_or_city
        FROM   po_headers_all po,
               hr_locations_all hr
        WHERE  po.po_header_id = aia.po_header_id
               AND po.ship_to_location_id = hr.location_id ) Facility_City,
       (SELECT hr.region_2
        FROM   po_headers_all po,
               hr_locations_all hr
        WHERE  po.po_header_id = aia.po_header_id
               AND po.ship_to_location_id = hr.location_id) Facility_State,
       (SELECT hr.postal_code
        FROM   po_headers_all po,
               hr_locations_all hr
        WHERE  po.po_header_id = aia.po_header_id
               AND po.ship_to_location_id = hr.location_id) Facility_Zip,
       aia.invoice_amount Total_Invoice_Amount,
       To_char(aia.invoice_date, 'MM/DD/YYYY') INVOICE_DATE,
       '' BILLING_GROUP
FROM   ap_invoices_all aia
JOIN ap_invoice_lines_all ail ON aia.invoice_id = ail.invoice_id
JOIN poz_suppliers_v psv ON aia.vendor_id = psv.vendor_id
JOIN ap_invoice_distributions_all aid ON aia.invoice_id = aid.invoice_id 
                                     AND ail.line_number = aid.invoice_line_number
JOIN gl_code_combinations gcc ON aid.dist_code_combination_id = gcc.code_combination_id
-- 关联PO表，匹配发票或发票行对应的PO
LEFT JOIN po_headers_all pha ON pha.po_header_id = aia.po_header_id 
                            OR pha.po_header_id = ail.po_header_id
WHERE  aia.WFAPPROVAL_STATUS='WFAPPROVED'
       AND ail.line_type_lookup_code = 'FREIGHT'
       AND aia.creation_date between (Nvl(To_date(Trunc(:P_Last_Update_Date),
                                    'YYYY-MM-DD HH24:Mi:SS'), sysdate) - 31) 
                                and (Nvl(to_date(substr(:P_Last_Update_Date,1,10)||substr(:P_Last_Update_Date,12,8),'YYYY-MM-DD HH24:Mi:SS'), sysdate))    
GROUP  BY :P_Trigger_ID,
          gcc.chart_of_accounts_id,
          gcc.segment1,
          gcc.segment2,
          gcc.segment3,
          aia.po_header_id,
          aia.invoice_num,
          psv.segment1,
          psv.vendor_name,
          aia.invoice_amount,
          aia.invoice_date

如果需要严格优先取发票级别的PO（aia.po_header_id匹配的），可以调整SELECT中的PO_Number逻辑为：

COALESCE(MAX(CASE WHEN pha.po_header_id = aia.po_header_id THEN pha.segment1 END),
         MAX(CASE WHEN pha.po_header_id = ail.po_header_id THEN pha.segment1 END)) PO_Number

内容的提问来源于stack exchange，提问作者Nick