-
-
姝数 数据达人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]最近看过此主题的会员
最佳答案
姝数 发表于 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
吗？不需要数据。然后就是麻烦提供一下您的数据库的类型和版本。我们看能不能复现一下再给您解决方案。
16个回答
wut 发表于 2018-9-3 17:22
请问你的sql在数据库执行报错吗？
SQL在数据库执行时不报错，，客户说主要是加了个left join，里面语句内容是一个计算数值的，然后在计算时就报错了
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
wut 发表于 2018-9-3 17:22
请问你的sql在数据库执行报错吗？
SQL在数据库执行时不报错，，客户说主要是加了个left join，里面语句内容是一个计算数值的，然后在计算时就报错了
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
姝数 发表于 2018-9-4 10:16
SQL在数据库执行时不报错，，客户说主要是加了个left join，里面语句内容是一个计算数值的，然后在计算时 ...
您可以把完整的sql语句以文件的形式给我一下。然后把用到的数据可以导给我吗？单从这个报错信息，我没法判断原因
wut 发表于 2018-9-4 10:32
您可以把完整的sql语句以文件的形式给我一下。然后把用到的数据可以导给我吗？单从这个报错信息，我没法 ...
发您了，您看这样行不
error-asa-2.rar
(1.88 KB, 下载次数: )
本帖最后由 西瓜小姐 于 2018-9-4 11:41 编辑
“ASA Error -131: Syntax error near ',' on line 37”
这里看看第37行是不是有语法错误或者数据类型错误
姝数 发表于 2018-9-4 14:21
应该不是，因为这语句别地是对的
“order by aa.device_kind,aa.link_sn”这一句您先用一个试试？order by aa.device_kind
试过了，不排序也不行，还是原来的错误提示 ASA Error -660: Server '': /sun4bld1/gusers/sabld/asofpool/release902_iq/src.90/db/engine/omni/./qdexpr.c:1004
Query Decomposition: Unknown Node Type
