MAXIMO中增加视图

 

增加视图，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)