如题.SQL语句如下:
create materialized view tj_mv_fwqs_ydjfwqk
build immediate
refresh on commit
as
select sum(jzmj) tongji,dlbm,xlbm,sj_year from tj_wd_shijian --,tj_wd_fwyt,tj_wd_shijian
--where --fwid in(select distinct fwid from GZLR_YWFW) and
-- yt=xlbm and to_char(adddate,'YYYY')= to_char(sj_year)
--group by dlbm,xlbm,sj_year,sj_month
inner join yw_jb_fwxx
inner join tj_wd_fwyt on yw_jb_fwxx.yt=tj_wd_fwyt.xlbm
on to_char(ADDDATE,'YYYY')= to_char(tj_wd_shijian.sj_year)
group by dlbm,xlbm,sj_year;
这个SQL语句在什么时候都可以执行,但是用他来建立物化视图就老是提示:
ERROR 位于第 5 行:
ORA-00904: "from$_subquery$_004"."ADDDATE_29_29": 无效的标识符
补丁已经打上了.
而
select sum(jzmj) tongji,dlbm,xlbm,sj_year from tj_wd_shijian --,tj_wd_fwyt,tj_wd_shijian
--where --fwid in(select distinct fwid from GZLR_YWFW) and
-- yt=xlbm and to_char(adddate,'YYYY')= to_char(sj_year)
--group by dlbm,xlbm,sj_year,sj_month
inner join yw_jb_fwxx
inner join tj_wd_fwyt on yw_jb_fwxx.yt=tj_wd_fwyt.xlbm
on to_char(ADDDATE,'YYYY')= to_char(tj_wd_shijian.sj_year)
group by dlbm,xlbm,sj_year;
这个语句本身并没有错,只执行时有数据被查出.用这个语句建立视图也是可以的
CREATE VIEW V_TEST
as
select sum(jzmj) tongji,dlbm,xlbm,sj_year from tj_wd_shijian --,tj_wd_fwyt,tj_wd_shijian
--where --fwid in(select distinct fwid from GZLR_YWFW) and
-- yt=xlbm and to_char(adddate,'YYYY')= to_char(sj_year)
--group by dlbm,xlbm,sj_year,sj_month
inner join yw_jb_fwxx
inner join tj_wd_fwyt on yw_jb_fwxx.yt=tj_wd_fwyt.xlbm
on to_char(ADDDATE,'YYYY')= to_char(tj_wd_shijian.sj_year)
group by dlbm,xlbm,sj_year;
没有错,唯一错的是创建物化视图的时候.
请高手指教
create materialized view tj_mv_fwqs_ydjfwqk
build immediate
refresh on commit
as
select sum(jzmj) tongji,dlbm,xlbm,sj_year from tj_wd_shijian --,tj_wd_fwyt,tj_wd_shijian
--where --fwid in(select distinct fwid from GZLR_YWFW) and
-- yt=xlbm and to_char(adddate,'YYYY')= to_char(sj_year)
--group by dlbm,xlbm,sj_year,sj_month
inner join yw_jb_fwxx
inner join tj_wd_fwyt on yw_jb_fwxx.yt=tj_wd_fwyt.xlbm
on to_char(ADDDATE,'YYYY')= to_char(tj_wd_shijian.sj_year)
group by dlbm,xlbm,sj_year;
这个SQL语句在什么时候都可以执行,但是用他来建立物化视图就老是提示:
ERROR 位于第 5 行:
ORA-00904: "from$_subquery$_004"."ADDDATE_29_29": 无效的标识符
补丁已经打上了.
而
select sum(jzmj) tongji,dlbm,xlbm,sj_year from tj_wd_shijian --,tj_wd_fwyt,tj_wd_shijian
--where --fwid in(select distinct fwid from GZLR_YWFW) and
-- yt=xlbm and to_char(adddate,'YYYY')= to_char(sj_year)
--group by dlbm,xlbm,sj_year,sj_month
inner join yw_jb_fwxx
inner join tj_wd_fwyt on yw_jb_fwxx.yt=tj_wd_fwyt.xlbm
on to_char(ADDDATE,'YYYY')= to_char(tj_wd_shijian.sj_year)
group by dlbm,xlbm,sj_year;
这个语句本身并没有错,只执行时有数据被查出.用这个语句建立视图也是可以的
CREATE VIEW V_TEST
as
select sum(jzmj) tongji,dlbm,xlbm,sj_year from tj_wd_shijian --,tj_wd_fwyt,tj_wd_shijian
--where --fwid in(select distinct fwid from GZLR_YWFW) and
-- yt=xlbm and to_char(adddate,'YYYY')= to_char(sj_year)
--group by dlbm,xlbm,sj_year,sj_month
inner join yw_jb_fwxx
inner join tj_wd_fwyt on yw_jb_fwxx.yt=tj_wd_fwyt.xlbm
on to_char(ADDDATE,'YYYY')= to_char(tj_wd_shijian.sj_year)
group by dlbm,xlbm,sj_year;
没有错,唯一错的是创建物化视图的时候.
请高手指教
on to_char(ADDDATE,'YYYY')= to_char(tj_wd_shijian.sj_year) 这句错了,把第二个on改成and
create materialized view tj_mv_fwqs_ydjfwqk
build immediate
refresh on commit
as
select sum(jzmj) tongji,dlbm,xlbm,sj_year
from tj_wd_shijian inner join yw_jb_fwxx
on to_char([color=#0000FF]yw_jb_fwxx.ADDDATE,'YYYY')= to_char(tj_wd_shijian.sj_year)[/color]
inner join tj_wd_fwyt
on yw_jb_fwxx.yt=tj_wd_fwyt.xlbm
group by dlbm,xlbm,sj_year;
关联的层次貌似乱了
create materialized view tj_mv_fwqs_ydjfwqk
build immediate
refresh on commit
as
select sum(jzmj) tongji,dlbm,xlbm,sj_year
from tj_wd_shijian inner join yw_jb_fwxx
on to_char(yw_jb_fwxx.ADDDATE,'YYYY')= to_char(tj_wd_shijian.sj_year)
inner join tj_wd_fwyt
on yw_jb_fwxx.yt=tj_wd_fwyt.xlbm
group by dlbm,xlbm,sj_year;