Oracle SQL报错ORA-00979及ORA-00904问题求助

问题现象 #

执行给定SQL时遇到两个连续错误：

• 首次执行报错：ORA-00979: not a GROUP BY expression（对应代码第11行第13列）
• 尝试将Dates_添加到GROUP BY子句末尾后，又报错：ORA-00904: "DATES_": invalid identifier（对应代码第42行第26列）

原SQL代码 #

SELECT p.project_name,
       CASE
         WHEN :P42_DATE_RANGES = 'Monthly' THEN To_char(b.date_sys, 'Month')
         WHEN :P42_DATE_RANGES = 'Daily' THEN To_char(b.date_sys, 'MM/DD/YYYY')
         WHEN :P42_DATE_RANGES = 'Weekly' THEN To_char(Trunc(b.date_sys, 'IW'), 'MM/DD/YYYY')
       END AS my_date,
       CASE
         WHEN :P42_DATE_RANGES = 'Monthly' THEN Trunc(b.date_sys, 'MM')
         WHEN :P42_DATE_RANGES = 'Daily' THEN Trunc(b.date_sys)
         WHEN :P42_DATE_RANGES = 'Weekly' THEN Trunc(b.date_sys, 'IW')
       END AS Dates_,
       Count(DISTINCT i.id)       AS count_of_ingested,
       SUM(hrc.highlighted_count) AS count_of_highlighted,
       SUM(hrc.redacted_count)    AS count_of_redacted
FROM   customer c
       inner join project p
               ON c.id = p.id_customer
       inner join batch b
               ON p.id = b.id_project
       left outer join ingested i
                    ON b.id = i.id_batch
       left outer join (SELECT hc.id AS id_ingested,
                               highlighted_count,
                               redacted_count
                        FROM  (SELECT i.id,
                                      Count(DISTINCT h.id) AS highlighted_count
                               FROM   ingested i
                                      left outer join highlighted h
                                                   ON i.id = h.id_ingested
                               GROUP  BY i.id) hc
                              join (SELECT i.id,
                                           Count(DISTINCT r.id) AS
                                           redacted_count
                                    FROM   ingested i
                                           left outer join redacted r
                                                        ON i.id = r.id_ingested
                                    GROUP  BY i.id) rc
                                ON hc.id = rc.id) hrc
                    ON i.id = hrc.id_ingested
WHERE  b.date_sys BETWEEN To_date(:P42_START_DATE) AND To_date(:P42_END_DATE)
GROUP  BY p.project_name,
          CASE
            WHEN :P42_DATE_RANGES = 'Monthly' THEN To_char(b.date_sys, 'Month')
            WHEN :P42_DATE_RANGES = 'Daily' THEN
            To_char(b.date_sys, 'MM/DD/YYYY')
            WHEN :P42_DATE_RANGES = 'Weekly' THEN
            To_char(Trunc(b.date_sys, 'IW'), 'MM/DD/YYYY')
          END 

错误原因 #

1. ORA-00979：SELECT列表中包含Dates_这个由CASE表达式生成的列，但GROUP BY子句未包含该列的完整表达式。Oracle要求所有非聚合列必须出现在GROUP BY中，且不能用SELECT里的别名代替完整表达式。
2. ORA-00904：Oracle的GROUP BY子句不支持引用SELECT列表中的列别名（Dates_），必须使用原始的CASE表达式或表列名。

解决方案 #

方式1：在GROUP BY中添加Dates_对应的完整CASE表达式 #

直接把SELECT里生成Dates_的CASE表达式完整复制到GROUP BY末尾：

SELECT p.project_name,
       CASE
         WHEN :P42_DATE_RANGES = 'Monthly' THEN To_char(b.date_sys, 'Month')
         WHEN :P42_DATE_RANGES = 'Daily' THEN To_char(b.date_sys, 'MM/DD/YYYY')
         WHEN :P42_DATE_RANGES = 'Weekly' THEN To_char(Trunc(b.date_sys, 'IW'), 'MM/DD/YYYY')
       END AS my_date,
       CASE
         WHEN :P42_DATE_RANGES = 'Monthly' THEN Trunc(b.date_sys, 'MM')
         WHEN :P42_DATE_RANGES = 'Daily' THEN Trunc(b.date_sys)
         WHEN :P42_DATE_RANGES = 'Weekly' THEN Trunc(b.date_sys, 'IW')
       END AS Dates_,
       Count(DISTINCT i.id)       AS count_of_ingested,
       SUM(hrc.highlighted_count) AS count_of_highlighted,
       SUM(hrc.redacted_count)    AS count_of_redacted
FROM   customer c
       inner join project p
               ON c.id = p.id_customer
       inner join batch b
               ON p.id = b.id_project
       left outer join ingested i
                    ON b.id = i.id_batch
       left outer join (SELECT hc.id AS id_ingested,
                               highlighted_count,
                               redacted_count
                        FROM  (SELECT i.id,
                                      Count(DISTINCT h.id) AS highlighted_count
                               FROM   ingested i
                                      left outer join highlighted h
                                                   ON i.id = h.id_ingested
                               GROUP  BY i.id) hc
                              join (SELECT i.id,
                                           Count(DISTINCT r.id) AS
                                           redacted_count
                                    FROM   ingested i
                                           left outer join redacted r
                                                        ON i.id = r.id_ingested
                                    GROUP  BY i.id) rc
                                ON hc.id = rc.id) hrc
                    ON i.id = hrc.id_ingested
WHERE  b.date_sys BETWEEN To_date(:P42_START_DATE) AND To_date(:P42_END_DATE)
GROUP  BY p.project_name,
          CASE
            WHEN :P42_DATE_RANGES = 'Monthly' THEN To_char(b.date_sys, 'Month')
            WHEN :P42_DATE_RANGES = 'Daily' THEN
            To_char(b.date_sys, 'MM/DD/YYYY')
            WHEN :P42_DATE_RANGES = 'Weekly' THEN
            To_char(Trunc(b.date_sys, 'IW'), 'MM/DD/YYYY')
          END,
          -- 添加Dates_对应的完整CASE表达式
          CASE
            WHEN :P42_DATE_RANGES = 'Monthly' THEN Trunc(b.date_sys, 'MM')
            WHEN :P42_DATE_RANGES = 'Daily' THEN Trunc(b.date_sys)
            WHEN :P42_DATE_RANGES = 'Weekly' THEN Trunc(b.date_sys, 'IW')
          END

方式2：使用CTE简化重复代码 #

通过CTE先计算分组所需的日期字段，避免在SELECT和GROUP BY中重复编写CASE表达式，可读性更强：

WITH batch_dates AS (
    SELECT 
        p.project_name,
        b.id AS batch_id,
        CASE
            WHEN :P42_DATE_RANGES = 'Monthly' THEN To_char(b.date_sys, 'Month')
            WHEN :P42_DATE_RANGES = 'Daily' THEN To_char(b.date_sys, 'MM/DD/YYYY')
            WHEN :P42_DATE_RANGES = 'Weekly' THEN To_char(Trunc(b.date_sys, 'IW'), 'MM/DD/YYYY')
        END AS my_date,
        CASE
            WHEN :P42_DATE_RANGES = 'Monthly' THEN Trunc(b.date_sys, 'MM')
            WHEN :P42_DATE_RANGES = 'Daily' THEN Trunc(b.date_sys)
            WHEN :P42_DATE_RANGES = 'Weekly' THEN Trunc(b.date_sys, 'IW')
        END AS Dates_
    FROM customer c
    INNER JOIN project p ON c.id = p.id_customer
    INNER JOIN batch b ON p.id = b.id_project
    WHERE b.date_sys BETWEEN To_date(:P42_START_DATE) AND To_date(:P42_END_DATE)
)
SELECT 
    bd.project_name,
    bd.my_date,
    bd.Dates_,
    COUNT(DISTINCT i.id) AS count_of_ingested,
    SUM(hrc.highlighted_count) AS count_of_highlighted,
    SUM(hrc.redacted_count) AS count_of_redacted
FROM batch_dates bd
LEFT OUTER JOIN ingested i ON bd.batch_id = i.id_batch
LEFT OUTER JOIN (
    SELECT 
        hc.id AS id_ingested,
        hc.highlighted_count,
        rc.redacted_count
    FROM (
        SELECT 
            i.id,
            COUNT(DISTINCT h.id) AS highlighted_count
        FROM ingested i
        LEFT OUTER JOIN highlighted h ON i.id = h.id_ingested
        GROUP BY i.id
    ) hc
    JOIN (
        SELECT 
            i.id,
            COUNT(DISTINCT r.id) AS redacted_count
        FROM ingested i
        LEFT OUTER JOIN redacted r ON i.id = r.id_ingested
        GROUP BY i.id
    ) rc ON hc.id = rc.id
) hrc ON i.id = hrc.id_ingested
GROUP BY bd.project_name, bd.my_date, bd.Dates_

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