Daeng Daeng - 6 months ago 84
SQL Question

ERROR ORA-00904: in simple UNPIVOT query. What am I doing wrong?

Running basic query to UNPIVOT columns to rows and getting "invalid identifier" error. Is there a typo in my code?

Oracle 11G

select
1 c1, 2 c2, 3 c3
FROM
dual t
UNPIVOT
INCLUDE NULLS (
VALUE
FOR
COL
IN
(
c1,c2,c3
));


Error:

ORA-00904: "C3": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 533 Column: 13

Answer

Please restructure your query like this...

with t as (select
   1 c1, 2 c2, 3 c3
FROM
   dual) 
select * from t 
UNPIVOT
INCLUDE NULLS (
VALUE
   FOR
      COL
   IN
   (
      c1,c2,c3
   ));
Comments