ORA-00907缺失右括号问题：SQL语句语法错误排查求助

错误根源 #

你遇到的ORA-00907: missing right parenthesis错误，是因为在Oracle中UNION ALL操作的子查询内不能直接使用ORDER BY子句（除非配合FETCH FIRST/ROWNUM等行限制条件）。你的第三个子查询（月度聚合部分）在括号内包含了ORDER BY to_char(v.day, 'YYYYMM') asc，这违反了Oracle的语法规则，导致解析器报错（错误提示的"缺失右括号"是Oracle对这类语法错误的常见模糊表述）。

修正后的SQL代码 #

我们需要将排序逻辑移到整个UNION ALL查询的外部，并通过添加排序键来保证结果的顺序：

with min_sum as 
(
    -- 按返工日期聚合返工数据
    select rework_date as day, sum(min_rw) as rw_min
    from rw_mn_51_min
    where kpi_nr = 'N' and kida = 'MK' and decision <> 'Disputed'
    group by rework_date
),
veh_sum as 
(
    -- 按日期聚合车辆生产数据
    select date_ as day, sum(f1_production) as f1_veh
    from rw_vehicle_production
    group by date_
)
select label, RMU, min_total, veh_total
from 
(
    -- 年度聚合子查询
    select 
        to_char(v.day, 'YYYY') as label, 
        round(COALESCE(sum(rw_min) / NULLIF(sum(f1_veh), 0), 0), 2) as RMU, 
        sum(rw_min) as min_total, 
        sum(f1_veh) as veh_total,
        1 as sort_order,
        null as month_sort_key
    from 
        veh_sum v
    left join 
        min_sum m on v.day = m.day
    where 
        trunc(v.day) <> trunc(sysdate) -- 改用trunc避免时间部分干扰
        and to_char(v.day, 'YYYY') = to_char(to_date(:P5_CW_LIST), 'YYYY')
    group by 
        to_char(v.day, 'YYYY')

    Union all

    -- 空白分隔行
    Select ' ', null, null, null,
           2 as sort_order,
           null as month_sort_key
    From dual

    Union all

    -- 近12个月度聚合子查询
    select to_char(v.day, 'MON') as label, 
           round(COALESCE(sum(rw_min) / NULLIF(sum(f1_veh), 0), 0), 2) as RMU, 
           sum(rw_min) as min_total, 
           sum(f1_veh) as veh_total,
           3 as sort_order,
           to_char(v.day, 'YYYYMM') as month_sort_key
    from veh_sum v
    left join min_sum m
    on v.day = m.day
    where trunc(v.day) <> trunc(sysdate) -- 改用trunc避免时间部分干扰
      and v.day >= ADD_MONTHS(TRUNC(to_date(:P5_CW_LIST, 'MM/DD/YYYY')), -11)
      and v.day <= TRUNC(to_date(:P5_CW_LIST, 'MM/DD/YYYY'))
    group by to_char(v.day, 'MON'), to_char(v.day, 'YYYYMM')
)
order by sort_order, month_sort_key asc;

关键修改说明 #

1. 移除子查询内的ORDER BY：删除了第三个子查询中的ORDER BY语句，因为它在UNION ALL的子查询中是无效的。
2. 添加排序键：给每个UNION ALL分段添加了sort_order列，确保结果按"年度数据→空白行→月度数据"的顺序排列。
3. 月度排序补充：为月度聚合部分添加month_sort_key列（存储YYYYMM格式的年月值），保证月度数据按时间顺序排列。
4. 日期比较优化：将to_char(v.day) <> to_char(sysdate)改为trunc(v.day) <> trunc(sysdate)，避免因日期字段包含时间部分导致的错误过滤。

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