Oracle SQL执行Pivot操作时出现ORA-00904: 'Sales'无效标识符错误的排查求助

Hey there! Let's break down why your pivot query is throwing that ORA-00904 error, and fix it step by step.

错误原因 #

Oracle的PIVOT子句是在FROM子句阶段执行的，这时候你在主SELECT里定义的别名sales（即(quantity * price) as sales）还没有被创建。PIVOT只能引用基础数据源（原始表、子查询或CTE）中已经存在的列，所以直接在PIVOT里使用sales会被判定为无效标识符。

修正方案 #

我们需要先把sales的计算逻辑放到一个子查询（或者CTE）里，让PIVOT能访问到这个预计算的列。另外还要注意：

• ORDER_PAY是DATE类型，在PIVOT的IN子句里最好显式用TO_DATE转换字符串，避免隐式转换的问题
• 给PIVOT后的日期列起一个可读性更好的别名（比如MAY_01_SALES），因为直接用日期当列名不太方便

方案1：使用子查询 #

SELECT *
FROM (
    SELECT 
        order_pay, 
        product_id, 
        (quantity * price) AS sales 
    FROM order_tbl
)
PIVOT (
    SUM(sales) 
    FOR order_pay IN (
        TO_DATE('01-MAY-2015', 'DD-MON-YYYY') AS MAY_01_SALES,
        TO_DATE('02-MAY-2015', 'DD-MON-YYYY') AS MAY_02_SALES
    )
);

方案2：使用CTE（公共表表达式，更易读） #

WITH order_sales AS (
    SELECT 
        order_pay, 
        product_id, 
        (quantity * price) AS sales 
    FROM order_tbl
)
SELECT *
FROM order_sales
PIVOT (
    SUM(sales) 
    FOR order_pay IN (
        TO_DATE('01-MAY-2015', 'DD-MON-YYYY') AS MAY_01_SALES,
        TO_DATE('02-MAY-2015', 'DD-MON-YYYY') AS MAY_02_SALES
    )
);

验证数据的基础脚本 #

如果需要重新创建表和插入数据，可以用以下脚本：

CREATE TABLE ORDER_TBL (
 ORDER_PAY DATE,
 ORDER_ID VARCHAR2(10 BYTE),
 PRODUCT_ID VARCHAR2(10 BYTE),
 QUANTITY NUMBER(5),
 PRICE NUMBER(5)
);

Insert into ORDER_TBL (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE) Values (TO_DATE('5/1/2015', 'MM/DD/YYYY'), 'ORD1', 'PROD1', 5, 5);
Insert into ORDER_TBL (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE) Values (TO_DATE('5/1/2015', 'MM/DD/YYYY'), 'ORD2', 'PROD2', 2, 10);
Insert into ORDER_TBL (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE) Values (TO_DATE('5/1/2015', 'MM/DD/YYYY'), 'ORD3', 'PROD3', 10, 25);
Insert into ORDER_TBL (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE) Values (TO_DATE('5/1/2015', 'MM/DD/YYYY'), 'ORD4', 'PROD1', 20, 5);
Insert into ORDER_TBL (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE) Values (TO_DATE('5/2/2015', 'MM/DD/YYYY'), 'ORD5', 'PROD3', 5, 25);
Insert into ORDER_TBL (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE) Values (TO_DATE('5/2/2015', 'MM/DD/YYYY'), 'ORD7', 'PROD1', 2, 5);
Insert into ORDER_TBL (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE) Values (TO_DATE('5/2/2015', 'MM/DD/YYYY'), 'ORD8', 'PROD5', 1, 50);
Insert into ORDER_TBL (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE) Values (TO_DATE('5/2/2015', 'MM/DD/YYYY'), 'ORD9', 'PROD6', 2, 50);
Insert into ORDER_TBL (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE) Values (TO_DATE('5/2/2015', 'MM/DD/YYYY'), 'ORD10', 'PROD2', 4, 10);
Insert into ORDER_TBL (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE) Values (TO_DATE('5/2/2015', 'MM/DD/YYYY'), 'ORD6', 'PROD4', 6, 20);
COMMIT;

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