新增内连接表列触发ORA-00904无效标识符错误求助

原本可正常运行的SQL脚本，添加b_product表的Product_Hier_2_l2_Name列后，Oracle返回如下错误：

ORA-00904: "P"."PRODUCT_HIER_2_L2_NAME": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
错误位置：第150行第5列

尝试修改GROUP BY子句、添加表引用P.Product_Hier_2_L2_NAME等调整后问题仍未解决，原脚本如下：

SELECT
        BU_CODE
    ,   CUST_TYPE
    ,   TXN_MTH
    ,   PRODUCT_HIER_2_L2_NAME
    ,   MONTHS_BETWEEN
    ,   TOT_MEMS
    ,   SUM(TOT_MEMS) OVER (PARTITION BY BU_CODE, TXN_MTH) AS TOT_IN_MTH
FROM (
    SELECT
            BU_CODE
        ,   CUST_TYPE
        ,   TXN_MTH
        ,   P.PRODUCT_HIER_2_L2_NAME
        ,   MONTHS_BETWEEN
        ,   COUNT(DISTINCT CONTACT_KEY) AS TOT_MEMS
    FROM (
            SELECT
                    T.CONTACT_KEY
                ,   T.BU_CODE
                ,   TXN_MTH
                ,   P.PRODUCT_HIER_2_L2_NAME
                ,   CASE    WHEN A.TXN_MTH = MIN(X.FISCAL_MTH_IDNT) THEN 'NEW'
                            ELSE 'RETURNING' END AS CUST_TYPE
                ,   MIN(CASE WHEN X.FISCAL_MTH_IDNT > A.TXN_MTH THEN X.FISCAL_MTH_IDNT ELSE NULL END) AS NEXT_TXN_MTH
                ,   MONTHS_BETWEEN(
                       TO_DATE(MIN(CASE WHEN X.FISCAL_MTH_IDNT > A.TXN_MTH THEN X.FISCAL_MTH_IDNT ELSE NULL END),'YYYYMM'),
                       TO_DATE(TXN_MTH,'YYYYMM')) AS MONTHS_BETWEEN
            FROM B_TRANSACTION T
            INNER JOIN B_TIME X
                ON T.TRANSACTION_DT_KEY = X.DATE_KEY
            INNER JOIN B_PRODUCT P 
                ON T.PRODUCT_KEY = P.PRODUCT_KEY
            INNER JOIN (
                    SELECT DISTINCT
                            T.CONTACT_KEY
                        ,   T.BU_KEY
                        ,   X.FISCAL_MTH_IDNT AS TXN_MTH
                        ,   P.PRODUCT_HIER_2_L2_NAME
                    FROM B_TRANSACTION T
                    INNER JOIN B_PRODUCT P
                    ON T.PRODUCT_KEY = P.PRODUCT_KEY
                    INNER JOIN B_TIME X
                        ON T.TRANSACTION_DT_KEY = X.DATE_KEY
                    WHERE   1=1
                        AND FISCAL_MTH_IDNT BETWEEN 202101 AND 202112
                        AND MEMBER_SALE_FLAG = 'Y'
                        AND CONTACT_KEY > 0
                        AND TRANSACTION_TYPE_NAME = 'Item'
                        AND T.BU_KEY IN (5)
                        ) A
                ON A.CONTACT_KEY = T.CONTACT_KEY AND A.BU_KEY = T.BU_KEY
            GROUP BY
                    T.CONTACT_KEY
                ,   T.BU_CODE
                ,   TXN_MTH
                ,   P.PRODUCT_HIER_2_L2_NAME
        )
    GROUP BY
            BU_CODE
        ,   CUST_TYPE
        ,   TXN_MTH
        ,   PRODUCT_HIER_2_L2_NAME
        ,   MONTHS_BETWEEN
    ORDER BY 1,2,3,4
    )
GROUP BY
        BU_CODE
    ,   CUST_TYPE
    ,   TXN_MTH
    ,   MONTHS_BETWEEN
    ,   TOT_MEMS
;

错误点1：表别名作用域超出有效范围 #

中间子查询（第二层）中使用了P.PRODUCT_HIER_2_L2_NAME，但表别名P仅在最内层子查询（第三层）中有效，外层子查询无法识别该别名。最内层子查询已经将该列输出为PRODUCT_HIER_2_L2_NAME，因此中间子查询直接引用列名即可，不需要加P.前缀。

错误点2：GROUP BY子句缺失必要列 #

最外层SELECT中包含PRODUCT_HIER_2_L2_NAME列，但对应的GROUP BY子句中未包含该列。Oracle要求GROUP BY必须包含所有非聚合、非窗口函数的列，否则会引发语法错误。

修正后的SQL #

SELECT
        BU_CODE
    ,   CUST_TYPE
    ,   TXN_MTH
    ,   PRODUCT_HIER_2_L2_NAME
    ,   MONTHS_BETWEEN
    ,   TOT_MEMS
    ,   SUM(TOT_MEMS) OVER (PARTITION BY BU_CODE, TXN_MTH) AS TOT_IN_MTH
FROM (
    SELECT
            BU_CODE
        ,   CUST_TYPE
        ,   TXN_MTH
        ,   PRODUCT_HIER_2_L2_NAME
        ,   MONTHS_BETWEEN
        ,   COUNT(DISTINCT CONTACT_KEY) AS TOT_MEMS
    FROM (
            SELECT
                    T.CONTACT_KEY
                ,   T.BU_CODE
                ,   TXN_MTH
                ,   P.PRODUCT_HIER_2_L2_NAME
                ,   CASE    WHEN A.TXN_MTH = MIN(X.FISCAL_MTH_IDNT) THEN 'NEW'
                            ELSE 'RETURNING' END AS CUST_TYPE
                ,   MIN(CASE WHEN X.FISCAL_MTH_IDNT > A.TXN_MTH THEN X.FISCAL_MTH_IDNT ELSE NULL END) AS NEXT_TXN_MTH
                ,   MONTHS_BETWEEN(
                       TO_DATE(MIN(CASE WHEN X.FISCAL_MTH_IDNT > A.TXN_MTH THEN X.FISCAL_MTH_IDNT ELSE NULL END),'YYYYMM'),
                       TO_DATE(TXN_MTH,'YYYYMM')) AS MONTHS_BETWEEN
            FROM B_TRANSACTION T
            INNER JOIN B_TIME X
                ON T.TRANSACTION_DT_KEY = X.DATE_KEY
            INNER JOIN B_PRODUCT P 
                ON T.PRODUCT_KEY = P.PRODUCT_KEY
            INNER JOIN (
                    SELECT DISTINCT
                            T.CONTACT_KEY
                        ,   T.BU_KEY
                        ,   X.FISCAL_MTH_IDNT AS TXN_MTH
                        ,   P.PRODUCT_HIER_2_L2_NAME
                    FROM B_TRANSACTION T
                    INNER JOIN B_PRODUCT P
                    ON T.PRODUCT_KEY = P.PRODUCT_KEY
                    INNER JOIN B_TIME X
                        ON T.TRANSACTION_DT_KEY = X.DATE_KEY
                    WHERE   1=1
                        AND FISCAL_MTH_IDNT BETWEEN 202101 AND 202112
                        AND MEMBER_SALE_FLAG = 'Y'
                        AND CONTACT_KEY > 0
                        AND TRANSACTION_TYPE_NAME = 'Item'
                        AND T.BU_KEY IN (5)
                        ) A
                ON A.CONTACT_KEY = T.CONTACT_KEY AND A.BU_KEY = T.BU_KEY
            GROUP BY
                    T.CONTACT_KEY
                ,   T.BU_CODE
                ,   TXN_MTH
                ,   P.PRODUCT_HIER_2_L2_NAME
        )
    GROUP BY
            BU_CODE
        ,   CUST_TYPE
        ,   TXN_MTH
        ,   PRODUCT_HIER_2_L2_NAME
        ,   MONTHS_BETWEEN
    ORDER BY 1,2,3,4
    )
GROUP BY
        BU_CODE
    ,   CUST_TYPE
    ,   TXN_MTH
    ,   PRODUCT_HIER_2_L2_NAME
    ,   MONTHS_BETWEEN
    ,   TOT_MEMS
;

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