视图
create or replace view v_statistic_sd as
(
select t.statistic_id,t.data,t.depart_id ,t.depart_id as zzjgdm,t.spsxbh,t.shouli_result
,t.banjie_result,t.time_limit_result,t.OCCUR_DATE,'0' step,'0' status
from t_statistic_shunde t
where t.statistic_id in('10','11','14')
--order by t.statistic_id,t.depart_id,t.spsxbh,t.occur_date
)
union
(
select va.STATISTIC_ID,va.DATA,va.depart_id,va.depart_id as zzjgdm,va.SPSXBH,va.SHOULI_RESULT
,va.banjie_result,va.TIME_LIMIT_RESULT,va.OCCUR_DATE,'0' step,va.status
from v_statistic_status_sd va
--order by va.statistic_id,va.depart_id,va.spsxbh,va.occur_date
)
union
(
select vb.STATISTIC_ID,vb.DATA,vb.depart_id,vb.depart_id as zzjgdm,vb.SPSXBH,vb.SHOULI_RESULT
,vb.banjie_result,vb.TIME_LIMIT_RESULT,vb.OCCUR_DATE,vb.step,'0' status
from v_statistic_step_sd vb
--order by vb.statistic_id,vb.depart_id,vb.spsxbh,vb.occur_date
)
--全区受理办结数,和在办件状态数
物化视图
create materialized view MV_statistic_sd
tablespace users
refresh complete on commitenable query rewriteAS
select t.statistic_id,t.data,t.depart_id,t.shouli_result,
t.banjie_result,t.time_limit_result,step,status
from v_statistic_sd t ---查询来源视图
报错,subquery expression not allow here
请高手,帮帮忙,我的本意思是,为上面的视图,建一个物化视图,因为视图查询很慢
create or replace view v_statistic_sd as
(
select t.statistic_id,t.data,t.depart_id ,t.depart_id as zzjgdm,t.spsxbh,t.shouli_result
,t.banjie_result,t.time_limit_result,t.OCCUR_DATE,'0' step,'0' status
from t_statistic_shunde t
where t.statistic_id in('10','11','14')
--order by t.statistic_id,t.depart_id,t.spsxbh,t.occur_date
)
union
(
select va.STATISTIC_ID,va.DATA,va.depart_id,va.depart_id as zzjgdm,va.SPSXBH,va.SHOULI_RESULT
,va.banjie_result,va.TIME_LIMIT_RESULT,va.OCCUR_DATE,'0' step,va.status
from v_statistic_status_sd va
--order by va.statistic_id,va.depart_id,va.spsxbh,va.occur_date
)
union
(
select vb.STATISTIC_ID,vb.DATA,vb.depart_id,vb.depart_id as zzjgdm,vb.SPSXBH,vb.SHOULI_RESULT
,vb.banjie_result,vb.TIME_LIMIT_RESULT,vb.OCCUR_DATE,vb.step,'0' status
from v_statistic_step_sd vb
--order by vb.statistic_id,vb.depart_id,vb.spsxbh,vb.occur_date
)
--全区受理办结数,和在办件状态数
物化视图
create materialized view MV_statistic_sd
tablespace users
refresh complete on commitenable query rewriteAS
select t.statistic_id,t.data,t.depart_id,t.shouli_result,
t.banjie_result,t.time_limit_result,step,status
from v_statistic_sd t ---查询来源视图
报错,subquery expression not allow here
请高手,帮帮忙,我的本意思是,为上面的视图,建一个物化视图,因为视图查询很慢
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货