-1

I have a working script and would like to add the column 'Product_Hier_2_l2_Name' from a table called b_product. When I add the Column Oracles throws back the error:

ORA-00904: "P"."PRODUCT_HIER_2_L2_NAME": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause:
*Action: Error at Line: 150 Column: 5

I have tried different scripts changing the group by adding table reference '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
;
live_happillyagain
  • 23
  • 2
  • You [have now](https://stackoverflow.com/q/74435135/266304) added the column in the first level of subquery, but there it should just be `PRODUCT_HIER_2_L2_NAME`, not `P.PRODUCT_HIER_2_L2_NAME`. The `P` table alias doesn't exist at this level. (If you had aliased your `from()` clauses you could prefix with that alias instead, but you haven't.) – Alex Poole Nov 15 '22 at 11:25

1 Answers1

0

You can't use the same alias (P.) throughout the whole code. That alias is valid only in the innermost subquery. Elsewhere, it is either A. or no alias at all:

  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,                       --> remove P.
                   MONTHS_BETWEEN,
                   COUNT (DISTINCT CONTACT_KEY) AS TOT_MEMS
              FROM (  SELECT T.CONTACT_KEY,
                             T.BU_CODE,
                             TXN_MTH,
                             A.PRODUCT_HIER_2_L2_NAME,           --> A., not P.
                             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;
Littlefoot
  • 131,892
  • 15
  • 35
  • 57