在Oracle上无法按视图取消透视 [英] Unpivot on view is not working in Oracle
问题描述
我有以下查询
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;
如果 mytable 是一个表，则工作正常. 基本上，我的来源是一个视图，如果将 mytable 替换为我的视图，则会抛出错误ORA-00933:SQL命令未正确结束.
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??
这是视图结构
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')
;
下面是我的表结构
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
);
推荐答案
如Aleksej所说-在Oracle服务器版本<上将无法使用. 11克.
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
我们可以将C1，C2和C3值的每一行转换为单列下的三行值，如下所示.
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
如果我们不需要C1，C2或C3中具有NULL值的行，则可以使用WHERE子句将其过滤掉.
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
这篇关于在Oracle上无法按视图取消透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
