报表报错UDA-SQL-0107 "first_value" requires a

xiaoerp

郁闷ing，做了一个报表，里面开始的时候只有一个泡泡图，运行没有问题，现在又加了一个泡泡图，运行报错。我把后面建立的图删掉也报同样的错了，我测了一下图背后的语句如果用list显示是没有问题，改成交叉表展示也出现一样的错误。

错误信息如下：
UDA-SQL-0107 A general exception has occurred during the operation "prepare".ERROR: window function "first_value" requires a window specification with an ordering clause; No query has been executed with that handleRSV-SRV-0042 Trace back:RSReportService.cpp(722): QFException: CCL_CAUGHT: RSReportService::process()RSReportServiceMethod.cpp(263): QFException: CCL_RETHROW: RSReportServiceMethod::process(): promptPagingForward_RequestRSASyncExecutionThread.cpp(808): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(260): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): promptPagingForward_RequestRSASyncExecutionThread.cpp(864): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): promptPagingForward_RequestExecution/RSRenderExecution.cpp(670): QFException: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(291): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(79): QFException: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(358): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(179): QFException: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(308): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(303): QFException: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(308): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(177): QFException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(308): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(137): QFException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(358): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSTableAssembly.cpp(119): QFException: CCL_RETHROW: RSTableAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(358): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSTableRowAssembly.cpp(177): QFException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(308): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(137): QFException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(358): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSDocAssemblyDispatch.cpp(308): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSChartAssembly.cpp(278): QFException: CCL_RETHROW: RSChartAssembly::assembleRSQueryMgr.cpp(1131): QFException: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(1295): QFException: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgr.cpp(1569): QFException: CCL_RETHROW: RSQueryMgr::executeRSAPiCommandRSQueryMgr.cpp(1559): QFException: CCL_RETHROW: RSQueryMgr::executeRSAPiCommandRSQueryMgrExecutionHandlerImpl.cpp(168): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()RSQueryMgrExecutionHandlerImpl.cpp(160): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()QFSSession.cpp(1147): QFException: CCL_RETHROW: QFSSession:rocessDoRequest()QFSSession.cpp(1145): QFException: CCL_CAUGHT: QFSSession:rocessDoRequest()QFSSession.cpp(1102): QFException: CCL_RETHROW: QFSSession:rocessDoRequest()QFSSession.cpp(1078): QFException: CCL_RETHROW: QFSSession:rocessDoRequest()QFSConnection.cpp(788): QFException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(213): QFException: CCL_RETHROW: QFSQuery::Execute v2CoordinationQFSQuery.cpp(4456): QFException: CCL_THROW: CoordinationPlanner

xiaoerp

1.首先看看你在Cognos的FM或者RS,有没有设置SQL的最长运行时间。
2.如果没有在Cognos设置，应该是Cognos传递给DB的SQL运行时间太长了，DB超时没有返回结果

xiaoerp

所谓prepare是指 sql解析阶段，ORA-00942是Oracle抛出的表或视图未找到异常。
如果在PLSQL工具里运行正常，而在report中抛该异常，则很有可能是你PLSQL工具连接Oracle db所用的用户并非是Cognos connection所用的连接用户。

所以建议在PLSQL中使用到的任何表，都加上前置user，然后再复制到Cognos Query SQL中。
即: 如果 表test 是创建在 用户 testuser下，则SQL中建议写成 testuser.test ，养成好习惯。

当然，也可通过synonym 解决，但这种方法一般需要DBA统筹考虑。