create materialized view cux_fa_deprn_cat_cost_MV
refresh complete on demand
start with to_date('23-05-2009 04:00:00', 'dd-mm-yyyy hh24:mi:ss') next trunc(SYSDATE+1)+240/1440
as
SELECT (SELECT 1 FROM fnd_user fu1 WHERE fu1.user_id = fu.user_id)
FROM fnd_user fu
报ORA-22818: subquery expressions not allowed here,是不是不支持子查询?
refresh complete on demand
start with to_date('23-05-2009 04:00:00', 'dd-mm-yyyy hh24:mi:ss') next trunc(SYSDATE+1)+240/1440
as
SELECT (SELECT 1 FROM fnd_user fu1 WHERE fu1.user_id = fu.user_id)
FROM fnd_user fu
报ORA-22818: subquery expressions not allowed here,是不是不支持子查询?
refresh complete on demand
start with to_date('23-05-2009 04:00:00', 'dd-mm-yyyy hh24:mi:ss') next trunc(SYSDATE+1)+240/1440
as
SELECT 1
FROM fnd_user
WHERE user_id IN (SELECT user_id FROM fnd_user1);
CREATE MATERIALIZED VIEW TAX_NP_FXQKB_NSPG_NEW
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE
ASSELECT A.INSID,A.NSRSBH, A.PGSSRQ, A.PGSSQQ, A.PGSSQZ,A.PGXM, A.PGRY,B.CBSK_ZZS,B.ZNJ_ZZS, B.CBSK_XFS, B.ZNJ_XFS,
DECODE (B.TZYNSSDE_ZZS,NULL,B.TZYNSSDE_XFS,B.TZYNSSDE_ZZS) TZYNSSDE,
DECODE (B.CBSK_QYSDS,NULL,B.CBSK_WZSDS,B.CBSK_QYSDS) CBSK_QYSDS,
DECODE (B.ZNJ_QYSDS,NULL,B.ZNJ_WZSDS,B.ZNJ_QYSDS) ZNJ_QYSDS,
B.FK,
DECODE(D.YS_BZ,0,'是','否') SFYSJC,
DECODE((SELECT COUNT(*) JG FROM TAX_NP_YTJZJL2 WHERE INSID=B.INSID),0,'否','是') SFYT,
DECODE((SELECT COUNT(*) JG FROM TAX_NP_SDDCB WHERE INSID=B.INSID),0,'否','是') SFSDDC,
A.ZYYDWT,
C.STATUS
FROM TAX_NP_FXQKB A,
TAX_NP_NSPGQKBG B,
T_WF_ENG_INS_HSTY C,
TAX_NP_NPXAJYS D
WHERE B.INSID=A.INSID
AND C.ID=A.INSID
AND A.INSID=D.INSID(+)
AND C.STATUS='C'
AND A.NSRSBH LIKE '3402%';单独的select执行没有问题,所以大家不用怀疑select的正确性,一旦创建物化视图就出现ORA-22818: subquery expressions not allowed here ,
用到的子查询只有
DECODE((SELECT COUNT(*) JG FROM TAX_NP_YTJZJL2 WHERE INSID=B.INSID),0,'否','是') SFYT,
DECODE((SELECT COUNT(*) JG FROM TAX_NP_SDDCB WHERE INSID=B.INSID),0,'否','是') SFSDDC,去掉这两句,则能成功创建,大家帮忙看看怎么回事。
DECODE(E.INSID,0,'否','是') SFYT,
DECODE(F.INSID,0,'否','是') SFSDDC,
.....
FROM TAX_NP_FXQKB A,
TAX_NP_NSPGQKBG B,
T_WF_ENG_INS_HSTY C,
TAX_NP_NPXAJYS D ,
TAX_NP_YTJZJL2 E,
TAX_NP_SDDCB F
WHERE B.INSID=A.INSID
AND C.ID=A.INSID
AND A.INSID=D.INSID(+)
AND C.STATUS='C'
and B.INSID=E.INSID(+)
and B.INSID=F.INSID(+)
AND A.NSRSBH LIKE '3402%'; 可以这样试试看