-
-
姝数 数据达人Lv4
发表于2018-9-3 17:18
悬赏2
已解决
楼主
提示
在连接池fenxi中解析数据源rs1的语句：
select part_name,device_kind,aa.link_sn as link_sn_sn,custom_name,link_speed,desp_speed,aa.device_name as device_name_a,port_sn,vlan,valid,gg.kt,hold_type,maxin,maxout,nodename,addr,addr as addr_zj
from
(
select part_name,device_kind,link_sn,link_speed,desp_speed,custom_name,device_name,port_sn,vlan,valid,hold_type,max_in/1000.0/1000.0 as maxin,max_out/1000.0/1000.0 as maxout
from f_ip_diaodu_id_ext_2018
where f_date=20180901 and hold_type='在用专线' and valid='否'
) aa
left join
(
select link_sn,count(*) as kt from f_ip_diaodu_id_ext_2018
where f_date>20180802 and
f_date<=20180901 and hold_type='在用专线' and valid='否'
group by link_sn
) gg
on aa.link_sn=gg.link_sn
left join
(
select nodename,device_name,addr from
(select distinct nodecode,addr,nename from NASINFO_TBL where f_date=20180901) bb
left join
(
select dd.device_name,bras_device_name from
(select distinct device_name from zx_avg_rate_t where device_kind='紫金' and f_date=20180901) dd
left join
(select distinct device_name,bras_device_name from ip_diaodu_2018
where f_date=20180901 and device_kind='紫金' and bras_device_name is not NULL) ee
on dd.device_name=ee.device_name
) aa
on bb.nename=aa.bras_device_name
left join
(select nodecode,nodename from NODECODE_TBL where f_date=20180901) cc
on bb.nodecode=cc.nodecode
where device_name is not NULL
) ff
on aa.device_name=ff.device_name
where part_name='8区局' or nodename='八区'
order by device_kind,aa.link_sn
时出错：ASA Error -660: Server '': /sun4bld1/gusers/sabld/asofpool/release902_iq/src.90/db/engine/omni/./qdexpr.c:1004
Query Decomposition: Unknown Node Type
2018-09-03 16:51:39.165 开始计算报表：B76925_cp114（内存情况：FREE=3.1G TOTAL=8G MAX=8G）
com.esen.exception.RuntimeException4I18N: 在连接池fenxi中解析数据源rs1的语句：
select part_name,device_kind,aa.link_sn as link_sn_sn,custom_name,link_speed,desp_speed,aa.device_name as device_name_a,port_sn,vlan,valid,gg.kt,hold_type,maxin,maxout,nodename,addr,addr as addr_zj
from
(
select part_name,device_kind,link_sn,link_speed,desp_speed,custom_name,device_name,port_sn,vlan,valid,hold_type,max_in/1000.0/1000.0 as maxin,max_out/1000.0/1000.0 as maxout
from f_ip_diaodu_id_ext_2018
where f_date=20180901 and hold_type='在用专线' and valid='否'
) aa
left join
(
select link_sn,count(*) as kt from f_ip_diaodu_id_ext_2018
where f_date>20180802 and
f_date<=20180901 and hold_type='在用专线' and valid='否'
group by link_sn
) gg
on aa.link_sn=gg.link_sn
left join
(
select nodename,device_name,addr from
(select distinct nodecode,addr,nename from NASINFO_TBL where f_date=20180901) bb
left join
(
select dd.device_name,bras_device_name from
(select distinct device_name from zx_avg_rate_t where device_kind='紫金' and f_date=20180901) dd
left join
(select distinct device_name,bras_device_name from ip_diaodu_2018
where f_date=20180901 and device_kind='紫金' and bras_device_name is not NULL) ee
on dd.device_name=ee.device_name
) aa
on bb.nename=aa.bras_device_name
left join
(select nodecode,nodename from NODECODE_TBL where f_date=20180901) cc
on bb.nodecode=cc.nodecode
where device_name is not NULL
) ff
on aa.device_name=ff.device_name
where part_name='8区局' or nodename='八区'
order by device_kind,aa.link_sn
时出错：ASA Error -660: Server '': /sun4bld1/gusers/sabld/asofpool/release902_iq/src.90/db/engine/omni/./qdexpr.c:1004
Query Decomposition: Unknown Node Type
at com.sybase.jdbc3.tds.Tds.a(Unknown Source)
at com.sybase.jdbc3.tds.Tds.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.ResultGetter.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.queryLoop(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.executeQuery(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.executeQuery(Unknown Source)
at com.esen.jdbc.pool.PooledStatement.getQureyResultSet(PooledStatement.java:54)
at com.esen.jdbc.pool.impl.sybase.SybaseIQPooledStatement.executeQuery(SybaseIQPooledStatement.java:25)
at com.esen.jdbc.SqlExecuter.excuteQurey(SqlExecuter.java:475)
at com.esen.jdbc.SqlExecuter.executeSql(SqlExecuter.java:153)
at com.esen.jdbc.SqlExecuter.executeQuery(SqlExecuter.java:105)
at com.esen.jdbc.dialect.impl.DialectImpl.getResultMetaData(DialectImpl.java:263)
at com.esen.jdbc.dialect.impl.DialectImpl.getResultMetaData(DialectImpl.java:234)
at com.esen.jdbc.dialect.impl.DialectImpl.getQueryResultMetaData(DialectImpl.java:300)
at com.esen.irpt.rp.analyse.AnalyseDataSource_SQL.compile(AnalyseDataSource_SQL.java:222)
at com.esen.irpt.rp.analyse.AnalyseDataSourceMgr.compileDataSource(AnalyseDataSourceMgr.java:184)
at com.esen.irpt.rp.analyse.AnalyseDataSourceMgr.compile(AnalyseDataSourceMgr.java:177)
at com.esen.bi.reportpage.analyse.ReportPageAnalyse.doCompile(ReportPageAnalyse.java:811)
at com.esen.bi.reportpage.reporttemplate.ReportTemplate.doCompile(ReportTemplate.java:106)
at com.esen.bi.reportpage.analyse.ReportPageAnalyse.compile(ReportPageAnalyse.java:761)
at com.esen.bi.reportpage.analyse.ReportPageAnalyse.compile(ReportPageAnalyse.java:552)
at com.esen.bi.reportpage.calc.ReportCalc_calc_rpttpl.calcAnalyseResult(ReportCalc_calc_rpttpl.java:63)
at com.esen.bi.rp.calc.impl.ReportCalc_calc_abs.calc_analyseResult(ReportCalc_calc_abs.java:252)
at com.esen.bi.rp.calc.impl.ReportCalc_calc_abs.calc(ReportCalc_calc_abs.java:118)
at com.esen.irpt.req.RequestTask_Calc.calc(RequestTask_Calc.java:95)
at com.esen.irpt.req.RequestTask_Calc.innerRun(RequestTask_Calc.java:57)
at com.esen.irpt.req.RequestTask_Calc_Abstract.doRun(RequestTask_Calc_Abstract.java:59)
at com.esen.irpt.req.AbstractRequestTask.call(AbstractRequestTask.java:85)
at com.esen.platform.common.req.RequestTaskRunner.call(RequestTaskRunner.java:74)
at com.esen.thread.LocaleTask.call(LocaleTask.java:60)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
at java.util.concurrent.FutureTask.run(FutureTask.java:166)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
at java.lang.Thread.run(Thread.java:679)
Caused by: com.esen.jdbc.pool.impl.sybase.SybaseIQPooledSQLException: ASA Error -660: Server '': /sun4bld1/gusers/sabld/asofpool/release902_iq/src.90/db/engine/omni/./qdexpr.c:1004
Query Decomposition: Unknown Node Type
... 37 more
页面地址为：【[url=]/bi/js/reqmgr.do?@ymd3=20180901;action=calc;rptid=EBI$12$0U4RNYP2OLKJMKRUT7K3U7XKP3US32T2$1$I0YMPO81UR2CVOM0IUZCTYUABU0VZSUO.rpttpl[/url]】
JavaScript StackTrace:
throwError@http://132.77.67.83:8880/bi/xui/sys.js:1987:14
AbstractReqObj.prototype.onqueryobjfinish@http://132.77.67.83:8880/bi/xui/util.js:1:92881
_AbstractReqObj_onQueryFinish@http://132.77.67.83:8880/bi/xui/util.js:1:93709
[url=mailto
ueryObj.prototype._onreadystatechange@http://132.77.67.83:8880/bi/xui/util.js:1:87025]QueryObj.prototype._onreadystatechange@http://132.77.67.83:8880/bi/xui/util.js:1:87025[/url][url=mailto
ueryObj.prototype._sendRequest/this.hp.onreadystatechange@http://132.77.67.83:8880/bi/xui/util.js:1:87348]QueryObj.prototype._sendRequest/this.hp.onreadystatechange@http://132.77.67.83:8880/bi/xui/util.js:1:87348[/url]最近看过此主题的会员
16个回答
姝数 发表于 2018-9-4 16:19
是不是BI的SQL可能对生成的数据集有限制，或者不能用
有这种可能吗？
一般来说，在数据库里可以正常执行的SQL都可以被BI的SQL数据源所查询。方便提供一下这几个表的表结构f_ip_diaodu_id_ext_2018
NASINFO_TBL
zx_avg_rate_t
ip_diaodu_2018
NODECODE_TBL
吗？不需要数据。然后就是麻烦提供一下您的数据库的类型和版本。我们看能不能复现一下再给您解决方案。
left join
(
select link_sn,kct from zx_abnormal_rate_t where f_date<=<#=@ymd3#> and hold_type='在途专线'
and kct=30 ) gg2
on aa.link_sn=gg2.link_sn
SQL已经改成这样了，有一个事实表，可以执行了。原因是BI的SQL不支持选出来的数据集做为from的虚表
left join
(
select link_sn,count(*) as kt from f_ip_diaodu_id_ext_2018
where f_date>20180802 and
f_date<=20180901 and hold_type='在用专线' and valid='否'
group by link_sn
) gg
on aa.link_sn=gg.link_sn
'想问一下这2个表达方式在BI的环境下，有什么不同？
姝数 发表于 2018-9-5 13:56
left join
(
select link_sn,kct from zx_abnormal_rate_t where f_date20180802 and
f_ip_diaodu_id_ext_2018 是视图？
