ORA-00904 with Pivot Operator After Upgrading Database To 12.2 or Higher (Doc ID 2674664.1)

Last updated on AUGUST 11, 2020

Applies to:

Symptoms

Upgraded a 11.2 database to 12.2 or higher.

After upgrade, query with Pivot operator gives error:

SELECT
           CASE
               WHEN R1_XXXXX_XXXXX_XXXXX_XXXXX_XXX IS NOT NULL -- Note 30 Characters
               THEN
                   R1_XXXXX_XXXXX_XXXXX_XXXXX_XXX
               ELSE
                   TD_XXXXX_XXXXX_XXXXX_XXXXX_XXX
           END
               AS XXXXX_XXXXX_XXXXX_XXXXX_XXXTD
FROM bug_test
PIVOT
(
    SUM (XXXXX_XXXXX_XXXXX_XXXXX_XXXT) AS XXXXX_XXXXX_XXXXX_XXXXX_XXXT -- Note 29 characters will be prefixed with the TD and R1 making over 30 characters
    FOR (SOMECOL) IN ('TD' AS TD, 'R1' AS R1)
);  
ORA-00904: "XXXXX_XXXXX_XXXXX_XXXXX_XXX": invalid identifier
00904. 00000 - "%s: invalid identifier"

Cause