我建物化视图的SQL如下:
create materialized view log on p_productionmodifyline with rowid, sequence (P_PRODUCTIONMODIFY_ID,P_PRODUCTION_ID) including new values;
创建成功。
create materialized view v_productionmodifyline
refresh fast on commit as
select count(*) cnt, s.p_production_id x2, count(s.qtyold) g2, sum(s.qtyold) qtybom
from p_productionmodifyline s group by s.p_production_id
报ORA-12033错。p_productionmodifyline的结构如下:
create table P_PRODUCTIONMODIFYLINE
(
P_PRODUCTIONMODIFYLINE_ID NUMBER(10) not null,
AD_CLIENT_ID NUMBER(10) not null,
AD_ORG_ID NUMBER(10) not null,
CREATED DATE default SYSDATE not null,
CREATEDBY NUMBER(10) default 0 not null,
ISACTIVE CHAR(1) default 'Y' not null,
UPDATED DATE default SYSDATE not null,
UPDATEDBY NUMBER(10) default 0 not null,
P_PRODUCTIONMODIFY_ID NUMBER(10) not null,
LINE NUMBER(10) not null,
P_PRODUCTION_ID NUMBER(10) not null,
M_PRODUCT_ID NUMBER(10) not null,
QTYOLD NUMBER not null,
QTYNEW NUMBER not null,
QTYMATERIALUSE NUMBER not null,
DATESTARTOLD DATE not null,
DATESTARTNEW DATE not null,
DATEENDOLD DATE not null,
DATEENDNEW DATE not null,
AD_ORGPRODUCTIONOLD_ID NUMBER(10),
AD_ORGPRODUCTIONNEW_ID NUMBER(10),
DESCRIPTION NVARCHAR2(255)
)
alter table P_PRODUCTIONMODIFYLINE
add constraint P_PRODUCTIONMODIFYLINE_1 foreign key (P_PRODUCTIONMODIFY_ID)
references P_PRODUCTIONMODIFY (P_PRODUCTIONMODIFY_ID);
alter table P_PRODUCTIONMODIFYLINE
add constraint P_PRODUCTIONMODIFYLINE_2 foreign key (P_PRODUCTION_ID)
references P_PRODUCTION (P_PRODUCTION_ID);
create materialized view log on p_productionmodifyline with rowid, sequence (P_PRODUCTIONMODIFY_ID,P_PRODUCTION_ID) including new values;
创建成功。
create materialized view v_productionmodifyline
refresh fast on commit as
select count(*) cnt, s.p_production_id x2, count(s.qtyold) g2, sum(s.qtyold) qtybom
from p_productionmodifyline s group by s.p_production_id
报ORA-12033错。p_productionmodifyline的结构如下:
create table P_PRODUCTIONMODIFYLINE
(
P_PRODUCTIONMODIFYLINE_ID NUMBER(10) not null,
AD_CLIENT_ID NUMBER(10) not null,
AD_ORG_ID NUMBER(10) not null,
CREATED DATE default SYSDATE not null,
CREATEDBY NUMBER(10) default 0 not null,
ISACTIVE CHAR(1) default 'Y' not null,
UPDATED DATE default SYSDATE not null,
UPDATEDBY NUMBER(10) default 0 not null,
P_PRODUCTIONMODIFY_ID NUMBER(10) not null,
LINE NUMBER(10) not null,
P_PRODUCTION_ID NUMBER(10) not null,
M_PRODUCT_ID NUMBER(10) not null,
QTYOLD NUMBER not null,
QTYNEW NUMBER not null,
QTYMATERIALUSE NUMBER not null,
DATESTARTOLD DATE not null,
DATESTARTNEW DATE not null,
DATEENDOLD DATE not null,
DATEENDNEW DATE not null,
AD_ORGPRODUCTIONOLD_ID NUMBER(10),
AD_ORGPRODUCTIONNEW_ID NUMBER(10),
DESCRIPTION NVARCHAR2(255)
)
alter table P_PRODUCTIONMODIFYLINE
add constraint P_PRODUCTIONMODIFYLINE_1 foreign key (P_PRODUCTIONMODIFY_ID)
references P_PRODUCTIONMODIFY (P_PRODUCTIONMODIFY_ID);
alter table P_PRODUCTIONMODIFYLINE
add constraint P_PRODUCTIONMODIFYLINE_2 foreign key (P_PRODUCTION_ID)
references P_PRODUCTION (P_PRODUCTION_ID);
create materialized view log on p_productionmodifyline with rowid, sequence(P_PRODUCTION_ID,qtyold) including new values;
select,where中的列都要放到sequence中。