使用LEVEL伪列连接遇ORA-00976错误，求每日未结订单统计方案

需求说明 #

从视图TMP_ORDERS_VIEW中获取2023-10-01至当日的每日日期序列，同时统计对应日期的未结订单数量（未结订单定义为ORDER_CLOSED_DATE为空的订单，需满足订单在当日处于未结状态：ORDER_OPEN_DATE≤当日，且ORDER_CLOSED_DATE为空或≥当日），输出用于生成折线图的时间序列数据。

错误尝试及问题分析 #

尝试使用LEVEL伪列和CONNECT BY生成日期序列，但触发错误ORA-00976: Specified pseudocolumn or operator not allowed here，错误代码如下：

select dt + (level - 1), open_orders_count_on_dt
from (select TO_DATE('01-OCT-23', 'dd-MON-YYYY') dt from dual)
LEFT OUTER JOIN 
    (
        select dt + (level - 1) ofcdt, count(ORDER_UID) open_orders_count_on_dt
        from TMP_ORDERS_VIEW
        where order_created_date<=dt AND NVL(order_closed_date, current_date)>=dt
    ) OFC on (ofcdt = dt + (level - 1))
connect by dt + (level - 1) <= TO_DATE('01-APR-24', 'dd-MON-YYYY');

错误原因 #

• Oracle不允许在JOIN的ON子句中直接引用LEVEL伪列，这是触发ORA-00976错误的直接原因
• 错误地用CONNECT BY包裹整个关联查询，正确逻辑应是先独立生成完整的日期范围序列，再关联订单数据进行统计

正确解决方案 #

通过WITH子句先生成目标日期范围的所有日期，再关联视图统计每日未结订单数：

WITH date_range AS (
    -- 生成2023-10-01至当日的每日日期序列
    SELECT TRUNC(TO_DATE('2023-10-01', 'YYYY-MM-DD') + LEVEL - 1) AS stat_date
    FROM dual
    CONNECT BY TO_DATE('2023-10-01', 'YYYY-MM-DD') + LEVEL - 1 <= TRUNC(SYSDATE)
)
SELECT
    dr.stat_date,
    COUNT(ov.ORDER_UID) AS open_orders_count
FROM date_range dr
LEFT JOIN TMP_ORDERS_VIEW ov
    ON ov.ORDER_OPEN_DATE <= dr.stat_date
    AND (ov.ORDER_CLOSED_DATE IS NULL OR ov.ORDER_CLOSED_DATE >= dr.stat_date)
GROUP BY dr.stat_date
ORDER BY dr.stat_date;

代码说明 #

• date_range子查询：利用LEVEL和CONNECT BY生成从起始日期到当日的所有日期，用TRUNC确保日期为无时间部分的纯日期值
• 关联逻辑：左连接订单视图，筛选出在统计日期处于未结状态的订单（开单日期≤统计日期，且未关闭或关闭日期≥统计日期）
• 分组统计：按统计日期分组，计算当日未结订单数量，最后按日期排序保证序列顺序

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