执行错误失败，bi再加载的时候不会提交参数

报表中两个文本控件中有两个参数:
开始日期和结束日期
本人认为，报表打开时应该会从这两个组件中获取参数，然后执行SQL。
再将数据插入导下方的下拉控件中。

但实际并不是按这个顺序执行。
是3个控件一起执行，会报错，下拉控件中也没有数据

select *from (select trunc(sysdate-1,'dd') D,'test1' S from dual union all
select trunc(sysdate-2,'dd'),'test2' from dual union all
select trunc(sysdate-3,'dd'),'test3' from dual union all
select trunc(sysdate-4,'dd'),'test4' from dual union all
select trunc(sysdate-5,'dd'),'test5' from dual union all
select trunc(sysdate-6,'dd'),'test6' from dual union all
select trunc(sysdate-7,'dd'),'test7' from dual union all
select trunc(sysdate-8,'dd'),'test8' from dual union all
select trunc(sysdate-9,'dd'),'test9' from dual union all
select trunc(sysdate-10,'dd'),'test10' from dual union all
select trunc(sysdate-11,'dd'),'test11' from dual union all
select trunc(sysdate-12,'dd'),'test12' from dual union all
select trunc(sysdate-13,'dd'),'test13' from dual union all
select trunc(sysdate-14,'dd'),'test14' from dual union all
select trunc(sysdate-15,'dd'),'test15' from dual union all
select trunc(sysdate-16,'dd'),'test16' from dual union all
select trunc(sysdate-17,'dd'),'test17' from dual union all
select trunc(sysdate-18,'dd'),'test18' from dual union all
select trunc(sysdate-19,'dd'),'test19' from dual union all
select trunc(sysdate-20,'dd'),'test20' from dual union all
select trunc(sysdate-21,'dd'),'test21' from dual union all
select trunc(sysdate-22,'dd'),'test22' from dual union all
select trunc(sysdate-23,'dd'),'test23' from dual union all
select trunc(sysdate-24,'dd'),'test24' from dual union all
select trunc(sysdate-25,'dd'),'test25' from dual union all
select trunc(sysdate-26,'dd'),'test26' from dual union all
select trunc(sysdate-27,'dd'),'test27' from dual union all
select trunc(sysdate-28,'dd'),'test28' from dual union all
select trunc(sysdate-29,'dd'),'test29' from dual union all
select trunc(sysdate-30,'dd'),'test30' from dual union all
select trunc(sysdate-31,'dd'),'test31' from dual union all
select trunc(sysdate-32,'dd'),'test32' from dual union all
select trunc(sysdate-33,'dd'),'test33' from dual) a where  1=1 <开始日期> and  D>=trunc(?{开始日期},'dd')</开始日期> <结束日期>and D<trunc(?{结束日期},'dd')+1</结束日期>


您把sql改成以上。
另外8.0已经过了维护期，不再支持。建议您联系商务购买MA，推进升级。

理解的没问题呢，具体的报错发下

补充报错信息

文本参数默认时间是当月第一天和最后一天

进入页面加载会报错，下拉参数没有数据。
把两个文本参数中的日期变动，下拉参数就可以获得数据。

实际是什么情况呢

报错的日志完全生成上传不上去，只能发文本了。

[tr][tr]022年6月28日 09:32:59.419[INFO]RTDashboard.removePending: Open db 未命名-1-1564 first page area cost 2042022年6月28日 09:33:04.716[INFO]AbsSQLExecutor.initQry: g5.qry.sql.impl.OracleExecutor@5a2748eb get ResultSet over.2022年6月28日 09:33:04.716[INFO]AbsSQLExecutor.init: g5.qry.sql.impl.OracleExecutor@5a2748eb init finished, grid: g5.qry.impl.QGrid@8918055, time occupied: 13829ms.2022年6月28日 09:33:04.716[INFO]QueryPool$QueryRunnable.run: g5.qry.sql.impl.OracleExecutor@5a2748eb init slow, cost: 13829ms.2022年6月28日 09:33:04.716[INFO]ARSExecutor.load: g5.qry.sql.impl.OracleExecutor@5a2748eb load data finished, grid: g5.qry.impl.QGrid@8918055, time occupied: 0, load rows: 12022年6月28日 09:33:04.716[INFO]GQuery.getPreGrid: g5.gqry.SQLGQuery@656fb775 execute one query 'g5.qry.sql.SQLQuery@274f2063', result 'g5.qry.impl.QGrid@8918055', detail 'false', hit cache 'false', size '2', occupied 13844ms. 2022年6月28日 09:33:04.716[INFO]GQuery.getPreGrid: =====the cache key==== g5.gqry.SQLGQuery@656fb775, dataCacheKey=QRY[ SELECT SUM("收入金额") AS "收入金额" FROM (select '门诊' 类别,trunc(a.SFRQ) 时间,a.SQKS 科室代码,co_qx_xtbm.bmmc 科室名称,a.fb 费别代码,sys_fybz_sflb.dmmc 费别,sum(a.sfje) 收入金额from v_mzgh_hzfymx1 a,co_qx_xtbm,sys_fybz_sflb where a.SQKS=co_qx_xtbm.bmnbbh and a.fb=sys_fybz_sflb.dm and ZT>1<开始日期> and a.SFRQ >= ?{开始日期} <结束日期> and a.SFRQ < (?{结束日期}+1)  group by trunc(a.SFRQ),a.SQKS,co_qx_xtbm.bmmc,a.fb,sys_fybz_sflb.dmmcunion allselect '住院' 类别,trunc(a.JZRQ) 时间,a.SZKS 科室代码,co_qx_xtbm.bmmc 科室名称,a.fb 费别代码,sys_fybz_sflb.dmmc 费别,sum(a.ssje) 收入金额 from v_zyjs_hzfymx a,co_qx_xtbm,sys_fybz_sflb where a.SZKS=co_qx_xtbm.bmnbbh and a.fb=sys_fybz_sflb.dm<开始日期> and a.JZRQ >= ?{开始日期} <结束日期> and a.JZRQ < (?{结束日期}+1)   group by trunc(a.JZRQ),a.SZKS,co_qx_xtbm.bmmc,a.fb,sys_fybz_sflb.dmmc) "SUB_QRY":JDBCConn: 1,jdbc:oracle:thin:@192.168.200.4:1521:orcl,oracle.jdbc.driver.OracleDriver,30,true,xnzyy,false,^mi3b592046095b1854,,,,null;收入金额00Sum0收入金额;QCI:<4,,_MAX_ROWS_:5000000,开始日期:2022-06-27,结束日期:2022-06-27>], where=null2022年6月28日 09:33:04.731[INFO]GQuery.getGrid: Execute one query 'g5.gqry.SQLGQuery@656fb775', result 'g5.qry.impl.QGrid@8918055', occupied 13844ms.   Related Dashboard: '1院长查询-1816'   Related Query: '全院收入--费别'   Related Element: '图表4'   Run user: 'guest'        SQL statement: ' SELECT SUM("收入金额") AS "收入金额" FROM (select '门诊' 类别,trunc(a.SFRQ) 时间,a.SQKS 科室代码,co_qx_xtbm.bmmc 科室名称,a.fb 费别代码,sys_fybz_sflb.dmmc 费别,sum(a.sfje) 收入金额from v_mzgh_hzfymx1 a,co_qx_xtbm,sys_fybz_sflb where a.SQKS=co_qx_xtbm.bmnbbh and a.fb=sys_fybz_sflb.dm and ZT>1<开始日期> and a.SFRQ >= ?{开始日期} <结束日期> and a.SFRQ < (?{结束日期}+1)  group by trunc(a.SFRQ),a.SQKS,co_qx_xtbm.bmmc,a.fb,sys_fybz_sflb.dmmcunion allselect '住院' 类别,trunc(a.JZRQ) 时间,a.SZKS 科室代码,co_qx_xtbm.bmmc 科室名称,a.fb 费别代码,sys_fybz_sflb.dmmc 费别,sum(a.ssje) 收入金额 from v_zyjs_hzfymx a,co_qx_xtbm,sys_fybz_sflb where a.SZKS=co_qx_xtbm.bmnbbh and a.fb=sys_fybz_sflb.dm<开始日期> and a.JZRQ >= ?{开始日期} <结束日期> and a.JZRQ < (?{结束日期}+1)   group by trunc(a.JZRQ),a.SZKS,co_qx_xtbm.bmmc,a.fb,sys_fybz_sflb.dmmc) "SUB_QRY"'2022年6月28日 09:33:04.731[INFO]AQuery.getGrid: Execute one AQuery 'g5.aqry.AQuery@4b137e46', result 'g5.qry.impl.QGrid@8918055', occupied 13859ms.2022年6月28日 09:33:04.731[INFO]ChartPresenter.createVChart0: Generate LChart g5.chart.LChart@46edda9c, occupied 0ms.2022年6月28日 09:33:04.731[INFO]ChartPresenter.createVChart0: Generate VChart g5.chart.VChart@4b3a62ad, occupied 0ms.2022年6月28日 09:33:04.731[INFO]RTDashboard.removePending: Open db 1院长查询-1816 first page area cost 149062022年6月28日 09:33:17.341[INFO]QueryPool.add: executor QueryExecutor8039 has been added into pool with grpCost: [0] and qryCost: [0].2022年6月28日 09:33:17.341[INFO]GQuery.getPreGrid1: g5.gqry.SQLGQuery@106380c0 waiting to get grid by g5.qry.sql.impl.OracleExecutor@67e5c8eb2022年6月28日 09:33:17.341[INFO]AbsSQLExecutor.init: g5.qry.sql.impl.OracleExecutor@67e5c8eb start init, with params: {_MAX_ROWS_=10000, 结束日期=2022-06-30, _DISCARDED_GRID_=false, _SEL_STRATEGY_=5, _Running_GQuery_Already_Added_=true, 下拉参数1=null, 开始日期=null, _MAX_LOAD_ROWS_=0}2022年6月28日 09:33:17.341[INFO]AbsSQLExecutor.init0: g5.qry.sql.impl.OracleExecutor@67e5c8eb start to execute SQL:______________________________ SELECT "出院科室" FROM (select CO_QX_XTBM.BMMC 出院科室, zyjs_HZJSb.JSSJ 结算时间,ZYYS_ZDXX.JMMC 单病种名称,decode(Zyjs_Hzjbxx.CYYY,'03',1,0) 死亡计数, 1 计数,FYZE 总费用,CYRQ-RYRQ 住院天数 from ZYJS_HZJBXX,zyjs_HZJSb,ZYYS_ZDXX,CO_MLLR,CO_QX_XTBM where zyjs_HZJSb.JSLX=3 and  Zyjs_Hzjbxx.Zybah=zyjs_HZJSb.zybah  and Zyjs_Hzjbxx.Zybah=ZYYS_ZDXX.Zybah and ZYYS_ZDXX.ZDFL='3'  and  ZYYS_ZDXX.ZDLX='1' and ZYYS_ZDXX.ZDDM=CO_MLLR.DM and CO_MLLR.LXID='294' and CO_QX_XTBM.BMNBBH=Zyjs_Hzjbxx.DQKSDM and  and  zyjs_HZJSb.JSSJ[tr] zyjs_HZJSb.JSSJ>=trunc(?{开始日期},'DD') and <结束日期> zyjs_HZJSb.JSSJ[tr]) "SUB_QRY" GROUP BY "出院科室" ORDER BY "出院科室" ASC:JDBCConn: 1,jdbc:oracle:thin:@192.168.200.4:1521:orcl,oracle.jdbc.driver.OracleDriver,30,true,xnzyy,false,^mi3b592046095b1854,,,,null;出院科室00;QCI:<4,,_MAX_ROWS_:10000,开始日期:null,结束日期:2022-06-30>], where=null2022年6月28日 09:33:17.341[WARNING]GQuery.getGrid: Execute one query 'g5.gqry.SQLGQuery@106380c0' with error, ', occupied 0ms.   Related Dashboard: '未命名-1-1564'   Related Query: '单病种报表'   Related Element: '下拉参数1'   Run user: 'admin' Caused by: java.sql.SQLException   执行SQL失败：“ORA-00936: 缺失表达式”        SQL statement: ' SELECT "出院科室" FROM (select CO_QX_XTBM.BMMC 出院科室, zyjs_HZJSb.JSSJ 结算时间,ZYYS_ZDXX.JMMC 单病种名称,decode(Zyjs_Hzjbxx.CYYY,'03',1,0) 死亡计数, 1 计数,FYZE 总费用,CYRQ-RYRQ 住院天数 from ZYJS_HZJBXX,zyjs_HZJSb,ZYYS_ZDXX,CO_MLLR,CO_QX_XTBM where zyjs_HZJSb.JSLX=3 and  Zyjs_Hzjbxx.Zybah=zyjs_HZJSb.zybah  and Zyjs_Hzjbxx.Zybah=ZYYS_ZDXX.Zybah and ZYYS_ZDXX.ZDFL='3'  and  ZYYS_ZDXX.ZDLX='1' and ZYYS_ZDXX.ZDDM=CO_MLLR.DM and CO_MLLR.LXID='294' and CO_QX_XTBM.BMNBBH=Zyjs_Hzjbxx.DQKSDM and <开始日期> zyjs_HZJSb.JSSJ>=trunc(?{开始日期},'DD') and <结束日期> zyjs_HZJSb.JSSJ[tr]) "SUB_QRY" GROUP BY "出院科室" ORDER BY "出院科室" ASC'java.sql.SQLException: 执行SQL失败：“ORA-00936: 缺失表达式”        at g5.qry.sql.AbsSQLExecutor.convertException(Unknown Source)        at g5.qry.sql.AbsSQLExecutor.init0(Unknown Source)        at g5.qry.sql.AbsSQLExecutor.access$700(Unknown Source)        at g5.qry.sql.AbsSQLExecutor$SQLRunnable.run(Unknown Source)        at g5.thread.CachedThreadPool$GThread.run0(Unknown Source)        at g5.thread.CachedThreadPool$GThread.run(Unknown Source)Caused by: java.sql.SQLException: ORA-00936: 缺失表达式        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799)        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)        at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839)        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)        at g5.qry.sql.AbsSQLExecutor.getRS(Unknown Source)        at g5.qry.sql.AbsSQLExecutor.initQry(Unknown Source)        ... 5 more2022年6月28日 09:33:17.341[WARNING]RunQryMgr.addErrorQuery: g5.audit.AuditUpdaterMgr.auditQueryError()2022年6月28日 09:33:17.341[INFO]AQuery.getGrid: Execute one AQuery 'g5.aqry.AQuery@40086794', result 'null', occupied 0ms.2022年6月28日 09:33:17.341[WARNING]RTDashboard.execData: 执行SQL失败：“ORA-00936: 缺失表达式”[Related resource]:   Dashboard: '未命名-1'   Element: '下拉参数1'   Query: '单病种报表'   Column: 'null'java.sql.SQLException: 执行SQL失败：“ORA-00936: 缺失表达式”        at g5.qry.sql.AbsSQLExecutor.convertException(Unknown Source)        at g5.qry.sql.AbsSQLExecutor.init0(Unknown Source)        at g5.qry.sql.AbsSQLExecutor.access$700(Unknown Source)        at g5.qry.sql.AbsSQLExecutor$SQLRunnable.run(Unknown Source)        at g5.thread.CachedThreadPool$GThread.run0(Unknown Source)        at g5.thread.CachedThreadPool$GThread.run(Unknown Source)Caused by: java.sql.SQLException: ORA-00936: 缺失表达式        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799)        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)        at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839)        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)        at g5.qry.sql.AbsSQLExecutor.getRS(Unknown Source)        at g5.qry.sql.AbsSQLExecutor.initQry(Unknown Source)        ... 5 more</trunc(?{结束日期},'dd')+1</trunc(?{结束日期},'dd')+1</trunc(?{结束日期},'dd')+1</trunc(?{结束日期},'dd')+1

把上面两个的默认时间修改一下呢

yhdata_bear 发表于 2022-6-28 10:08
把上面两个的默认时间修改一下呢

没有作用。

yhdata_XVqnsYUK 发表于 2022-6-28 10:59
没有作用。

报告导出来看下？
trunc(?{开始日期},'DD')  

humming 发表于 2022-6-28 11:10
报告导出来看下？
trunc(?{开始日期},'DD')

我觉得可能是版本问题 cs3.zip (3.64 KB, 下载次数: 3)

2022-6-28 15:35 上传

点击文件名下载附件