ORA-30483错误：外层SELECT中窗口函数使用受限问题排查

错误根源很直接：窗口函数不允许放在GROUP BY子句里，你把ROW_NUMBER()直接写到GROUP BY中了，Oracle会直接触发这个报错。

从你的SQL逻辑来看，应该是想按SUBINVENTORY_CODE分区，取每个分区里成本日期最新的记录。正确的做法是把分组聚合和窗口函数拆成两层子查询：

• 第一层先做分组聚合，算出每个INVENTORY_ITEM_ID+SUBINVENTORY_CODE+TRANSACTION_COST组合对应的最新成本日期
• 第二层在聚合结果上添加窗口函数进行分区排序
• 最后在外层过滤出每个分区的第一条有效记录

修正后的SQL代码：

SELECT * FROM 
(
    SELECT 
        agg.INVENTORY_ITEM_ID,
        agg.SUBINVENTORY_CODE,
        agg.COST_DATE,
        agg.TRANSACTION_COST,
        ROW_NUMBER() OVER (PARTITION BY agg.SUBINVENTORY_CODE ORDER BY agg.INVENTORY_ITEM_ID, agg.COST_DATE DESC) rownum1
    FROM 
    (
        SELECT 
            A.INVENTORY_ITEM_ID,
            C.SUBINVENTORY_CODE,
            TO_CHAR(MAX(B.COST_DATE)) COST_DATE,
            TO_CHAR(B.TRANSACTION_COST,'FM99990.90') TRANSACTION_COST
        FROM CST_ONHAND_V A
        JOIN CST_ITEM_COST_HISTORY_V B 
            ON A.REC_TRXN_ID = B.TRANSACTION_ID
            AND A.COST_ORG_ID = B.COST_ORG_ID
            AND A.COST_BOOK_ID = B.COST_BOOK_ID
            AND A.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
        JOIN CST_TXN_LAYER_DTLS_V C 
            ON A.REC_TRXN_ID = C.REC_TRXN_ID
        WHERE A.INVENTORY_ITEM_ID = :p_inv_num
        GROUP BY 
            A.INVENTORY_ITEM_ID,
            C.SUBINVENTORY_CODE,
            TO_CHAR(B.TRANSACTION_COST,'FM99990.90')
    ) agg
) 
WHERE rownum1 = 1 AND TRANSACTION_COST IS NOT NULL

额外优化：把原SQL的隐式连接改成了显式JOIN写法，逻辑完全一致但可读性更强。

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