Holmes IV Holmes IV - 7 months ago 43
SQL Question

Oracle Pivot Multi Table

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"
*Cause:

*Action: Error at Line: 18 Column: 6


Any thoughts?

SELECT * FROM
(
SELECT ACC.NBR,CTA.NAMEZ
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
)
PIVOT
(
MAX(VALUEZ) --comes from table CTAS (ERROR LINE)
FOR NAMEZ IN ('1','2','3') --from table CTAS
)
ORDER BY ACC.NBR;


As a side note, I would love it if it was possible to turn the ('1','2','3') into a subquery, but it looks like that is not possible from other post i have read. If it was easy it would be (select distinct namez from CTAS)

Answer

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 ACC.NBR,CTA.NAMEZ,CTA.VALUEZ.

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.

Comments