增加视图,maximo比较麻烦,在数据库配置里中不能加入group语句,需要在后台修改。
1、在数据库配置里增加视图。下面是已经全部配置好的效果
2、configdb数据库。
3、修改maxview 、maxviewcfg
SELECT * FROM maxview where viewname='VGPLINEMONTH' FOR UPDATE;
SELECT * FROM maxviewcfg where viewname='VGPLINEMONTH' FOR UPDATE;
VIEWNAME | VIEWSELECT | VIEWWHERE | AUTOSELECT | MAXVIEWID | VIEWFROM | ROWSTAMP |
VGPLINEMONTH
| gasplannum,planmonth,sum(jz1qty) as jz1qty,sum(jz1m3) as jz1m3,sum(jz1ton) as jz1ton,sum(jz3qty) as jz3qty,sum(jz3m3) as jz3m3,sum(jz3ton) as jz3ton,sum(jz10qty) as jz10qty,sum(jz10m3) as jz10m3,sum(jz10ton) as jz10ton,sum(qtycost) as qtycost,sum(m3cost) as m3cost,sum(toncost) as toncost | group by gasplannum, planmonth | 0 | 103 | GASPLANLINE | 10441478 |
4、删除maxattributecfg 、maxattribute 、maxviewcolumn 、maxviewcolumnCFG 不需要的字段
delete from maxattributecfg where objectname='VGPLINEMONTH' and lower(attributename) not in ('gasplannum','planmonth','jz1qty','jz1m3','jz1ton','jz3qty','jz3m3','jz3ton','jz10qty','jz10m3','jz10ton','qtycost','m3cost','toncost');
delete from maxattribute where objectname='VGPLINEMONTH' and lower(attributename) not in ('gasplannum','planmonth','jz1qty','jz1m3','jz1ton','jz3qty','jz3m3','jz3ton','jz10qty','jz10m3','jz10ton','qtycost','m3cost','toncost');
DELETE FROM maxviewcolumn where viewname='VGPLINEMONTH' and lower(viewcolumnname) NOT in ('gasplannum','planmonth');
DELETE FROM maxviewcolumnCFG where viewname='VGPLINEMONTH' and lower(viewcolumnname) NOT in ('gasplannum','planmonth');
5、修改视图'VGPLINEMONTH'
create or replace view vgplinemonth as
select gasplannum,planmonth,sum(jz1qty) as jz1qty,sum(jz1m3) as jz1m3,sum(jz1ton) as jz1ton,sum(jz3qty) as jz3qty,sum(jz3m3) as jz3m3,sum(jz3ton) as jz3ton,sum(jz10qty) as jz10qty,sum(jz10m3) as jz10m3,sum(jz10ton) as jz10ton,sum(qtycost) as qtycost,sum(m3cost) as m3cost,sum(toncost) as toncost
from GASPLANLINE
group by gasplannum, planmonth
注意:视图中字段如果用别名,必须使用“字段 as 别名 ”格式,不然系统不认。
例如
create or replace view viewitem as
select invbalances.invbalancesid as invbalancesid,invbalances.itemnum as itemnum,invbalances.location as location,invbalances.binnum as binnum,invbalances.curbal as curbal,invbalances.lotnum as lotnum,invbalances.siteid as siteid,invlot.currencycode as currencycode ,invlot.unitcost as unitcost,invlot.yoninvoice as yoninvoice,invlot.invoice as invoice,invlot.confirmcostdate as confirmcostdate from invbalances
inner join invlot
on invbalances.itemnum=invlot.itemnum and invbalances.location=invlot.location and invbalances.lotnum=invlot.lotnum and invbalances.siteid=invlot.siteid
order by invlot.lotnum desc
插入视图列
insert into maxviewcolumn(viewname,viewcolumnname,tablename,tablecolumnname,maxviewcolumnid)
values('VIEWITEM','','INVLOT','',maxviewcolumnseq.nextval)
insert into maxviewcolumncfg(viewname,viewcolumnname,tablename,tablecolumnname,changed,maxviewcolumnid)
values('VIEWITEM','','INVLOT','','N',maxviewcolumncfgseq.nextval)
例如:
update maxattributecfg f set f.persistent = 1, f.columnname = f.attributename, f.entityname = f.objectname, f.searchtype = 'WILDCARD'
where f.objectname ='VIEWITEM' and f.attributename = 'CURRENCYCODE'
insert into maxviewcolumn(viewname,viewcolumnname,tablename,tablecolumnname,maxviewcolumnid)
values('VIEWITEM','CURRENCYCODE','INVLOT','CURRENCYCODE',maxviewcolumnseq.nextval)
insert into maxviewcolumncfg(viewname,viewcolumnname,tablename,tablecolumnname,changed,maxviewcolumnid)
values('VIEWITEM','CURRENCYCODE','INVLOT','CURRENCYCODE','N',maxviewcolumncfgseq.nextval)
-----------------------------------------------------------------------
update maxattributecfg f set f.persistent = 1, f.columnname = f.attributename, f.entityname = f.objectname, f.searchtype = 'WILDCARD'
where f.objectname ='VIEWITEM' and f.attributename = 'CURRENCYCODE'
update maxattributecfg f set f.persistent = 1, f.columnname = f.attributename, f.entityname = f.objectname, f.searchtype = 'EXACT'
where f.objectname ='VIEWITEM' and f.attributename = 'CONFIRMCOSTDATE'
update maxattributecfg f set f.persistent = 1, f.columnname = f.attributename, f.entityname = f.objectname, f.searchtype = 'WILDCARD'
where f.objectname ='VIEWITEM' and f.attributename = 'INVOICE'
update maxattributecfg f set f.persistent = 1, f.columnname = f.attributename, f.entityname = f.objectname, f.searchtype = 'EXACT'
where f.objectname ='VIEWITEM' and f.attributename = 'UNITCOST'
update maxattributecfg f set f.persistent = 1, f.columnname = f.attributename, f.entityname = f.objectname, f.searchtype = 'EXACT'
where f.objectname ='VIEWITEM' and f.attributename = 'YONINVOICE'
insert into maxviewcolumn(viewname,viewcolumnname,tablename,tablecolumnname,maxviewcolumnid)
values('VIEWITEM','CURRENCYCODE','INVLOT','CURRENCYCODE',maxviewcolumnseq.nextval)
insert into maxviewcolumncfg(viewname,viewcolumnname,tablename,tablecolumnname,changed,maxviewcolumnid)
values('VIEWITEM','CURRENCYCODE','INVLOT','CURRENCYCODE','N',maxviewcolumncfgseq.nextval)
insert into maxviewcolumn(viewname,viewcolumnname,tablename,tablecolumnname,maxviewcolumnid)
values('VIEWITEM','CONFIRMCOSTDATE','INVLOT','CONFIRMCOSTDATE',maxviewcolumnseq.nextval)
insert into maxviewcolumncfg(viewname,viewcolumnname,tablename,tablecolumnname,changed,maxviewcolumnid)
values('VIEWITEM','CONFIRMCOSTDATE','INVLOT','CONFIRMCOSTDATE','N',maxviewcolumncfgseq.nextval)
insert into maxviewcolumn(viewname,viewcolumnname,tablename,tablecolumnname,maxviewcolumnid)
values('VIEWITEM','INVOICE','INVLOT','INVOICE',maxviewcolumnseq.nextval)
insert into maxviewcolumncfg(viewname,viewcolumnname,tablename,tablecolumnname,changed,maxviewcolumnid)
values('VIEWITEM','INVOICE','INVLOT','INVOICE','N',maxviewcolumncfgseq.nextval)
insert into maxviewcolumn(viewname,viewcolumnname,tablename,tablecolumnname,maxviewcolumnid)
values('VIEWITEM','UNITCOST','INVLOT','UNITCOST',maxviewcolumnseq.nextval)
insert into maxviewcolumncfg(viewname,viewcolumnname,tablename,tablecolumnname,changed,maxviewcolumnid)
values('VIEWITEM','UNITCOST','INVLOT','UNITCOST','N',maxviewcolumncfgseq.nextval)
insert into maxviewcolumn(viewname,viewcolumnname,tablename,tablecolumnname,maxviewcolumnid)
values('VIEWITEM','YONINVOICE','INVLOT','YONINVOICE',maxviewcolumnseq.nextval)
insert into maxviewcolumncfg(viewname,viewcolumnname,tablename,tablecolumnname,changed,maxviewcolumnid)
values('VIEWITEM','YONINVOICE','INVLOT','YONINVOICE','N',maxviewcolumncfgseq.nextval)