I am trying to create a Pivot in Oracle. I keep getting the error message
ORA-00904: "VALUEZ": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Action: Error at Line: 18 Column: 6
SELECT * FROM
FROM ACCS ACC
JOIN CARS CAR ON CAR.CAR_AAD_ID = ACC.ACC_AAD_ID
JOIN CTAS CTA ON CAR_CUS_ID = CTA_CUS_ID
MAX(VALUEZ) --comes from table CTAS (ERROR LINE)
FOR NAMEZ IN ('1','2','3') --from table CTAS
ORDER BY ACC.NBR;
The columns referenced in your PIVOT clause must exist in the row source that is being pivoted. You select
ACC.NBR,CTA.NAMEZ from the table; it looks like you need to expand that to
You cannot use a subquery to replace the list of pivot values. I believe the underlying reason for this is that the parser must be able to figure out the columns that the query will produce prior to executing it; so the pivot values must be hardcoded.
What you might be able to do, if it is appropriate to wrap this query up in a procedure or function, is to first execute a query to get the list of pivot values, then build the pivot query string using that information and execute it via dynamic SQL.