Unpivot on view is not working in Oracle

Question

I have the below query

select distinct cd, nm, 
  case when parent=cd then 0 else parent end as parent, lvl 
from (
  select * from mytable
  unpivot (
      (cd, nm, parent) for lvl in ((CD_1, NM_1, CD_1) as 1,(CD_2, NM_2, CD_1) as 2,(CD_3, NM_3, CD_2) as 3,(CD_4, NM_4, CD_3) as 4, (CD_5, NM_5, CD_4) as 5)
  )
)
order by lvl;

If mytable is a table this is working fine. Basically my source is a view , if replace the mytable with my view its throwing an error ORA-00933 : SQL command not properly ended.

Any alternate for removing that error??

This is view structure

DROP VIEW MY_VIEW;

/* Formatted on 2/4/2016 3:12:19 PM (QP5 v5.149.1003.31008) */
CREATE OR REPLACE FORCE VIEW MY_VIEW
(
   CHIEF_NM,
   CHIEF_NO,
   CMPNY_CD,
   CMPNY_NM,
   COST_CNTR_CD,
   COST_CNTR_NM,
   INCMBNT_PRSN_NM,
   OBJECT_ID,
   1_CD,
   1_NM,
   2_CD,
   2_NM,
   3_CD,
   3_NM,
   4_CD,
   4_NM,
   5_CD,
   5_NM,
   6_CD,
   6_NM,
   7_CD,
   7_NM,
   8_CD,
   8_NM,
   9_CD,
   9_NM,
   10_CD,
   10_NM,
   11_CD,
   11_NM,
   12_CD,
   12_NM,
   13_CD,
   13_NM,
   14_CD,
   14_NM,
   15_CD,
   15_NM,
   ORG_UNIT_NM,
   PRSNNL_AREA_CD,
   PRSNNL_AREA_NM,
   PRSNNL_SUBAREA_CD,
   PRSNNL_SUBAREA_NM,
   ESA_LAST_UPDT_TMSTMP,
   SNAPSHOT_DT,
   LOG_DEL_IND
)
AS
   SELECT chief_nm,
          chief_no,
          cmpny_cd,
          cmpny_nm,
          cost_cntr_cd,
          cost_cntr_nm,
          incmbnt_prsn_nm,
          object_id,
          1_cd,
          1_nm,
          2_cd,
          2_nm,
          3_cd,
          3_nm,
          4_cd,
          4_nm,
          5_cd,
          5_nm,
          6_cd,
          6_nm,
          7_cd,
          7_nm,
          8_cd,
          8_nm,
          9_cd,
          9_nm,
          10_cd,
          10_nm,
          11_cd,
          11_nm,
          12_cd,
          12_nm,
          13_cd,
          13_nm,
          14_cd,
          14_nm,
          15_cd,
          15_nm,
          org_unit_nm,
          prsnnl_area_cd,
          prsnnl_area_nm,
          prsnnl_subarea_cd,
          prsnnl_subarea_nm,
          esa_last_updt_tmstmp,
          esa_last_updt_tmstmp snapshot_dt,
          log_del_ind
     FROM TBLE a
    WHERE a.exp_dt = TO_DATE ('31-Dec-9999', 'dd-Mon-yyyy')
;

Below is my table structure

DROP TABLE mytable;
CREATE TABLE mytable(
   OBJECT_ID INTEGER  NOT NULL PRIMARY KEY 
  ,CD_1      INTEGER  NOT NULL
  ,NM_1      VARCHAR(15) NOT NULL
  ,CD_2      INTEGER  NOT NULL
  ,NM_2      VARCHAR(28) NOT NULL
  ,CD_3      INTEGER  NOT NULL
  ,NM_3      VARCHAR(20) NOT NULL
  ,CD_4      INTEGER  NOT NULL
  ,NM_4      VARCHAR(28) NOT NULL
  ,CD_5      INTEGER  NOT NULL
  ,NM_5      VARCHAR(16) NOT NULL
  ,DT        VARCHAR(14) NOT NULL
);

Answer 1

As Aleksej already said - it won't work on the Oracle server version < 11g.

So here is an old and bit "nasty" approach...

select key, c1, c2, c3 from t order by key ;

KEY C1    C2    C3
--- ----- ----- -----
r1  v1    v2    v3
r2  v1    v2
r3  v1          v3
r4  v1
r5        v2    v3
r6        v2
r7              v3
r8

We can convert each row of C1, C2, and C3 values into three rows of values under a single column as follows.

break on key skip 1 duplicates
select key, 'C1' as source, c1 as val from t union all
select key, 'C2' as source, c2 as val from t union all
select key, 'C3' as source, c3 as val from t
order by key, source ;

KEY SOURCE VAL
--- ------ -----
r1  C1     v1
r1  C2     v2
r1  C3     v3
r2  C1     v1
r2  C2     v2
r2  C3
r3  C1     v1
r3  C2
r3  C3     v3
r4  C1     v1
r4  C2
r4  C3
r5  C1
r5  C2     v2
r5  C3     v3
r6  C1
r6  C2     v2
r6  C3
r7  C1
r7  C2
r7  C3     v3
r8  C1
r8  C2
r8  C3

If we do not need the rows with NULL values from C1, C2, or C3 we can use WHERE clauses to filter them out.

select key, 'C1' as source, c1 as val from t where c1 is not null union all
select key, 'C2' as source, c2 as val from t where c2 is not null union all
select key, 'C3' as source, c3 as val from t where c3 is not null
order by key, source ;

KEY SOURCE VAL
--- ------ -----
r1  C1     v1
r1  C2     v2
r1  C3     v3
r2  C1     v1
r2  C2     v2
r3  C1     v1
r3  C3     v3
r4  C1     v1
r5  C2     v2
r5  C3     v3
r6  C2     v2
r7  C3     v3

(c) http://oracle-knowledgeshare.blogspot.de