COGNOiSe.com - The IBM Cognos Community

After upgrading to Cognos 8.2, some reports we had working properly in 8.1 are no longer working.  One function in particular is the following:

if( _months_between([field1],getdate())>=-13 and _months_between([field1],getdate())<=-2 ) then ( (moving-total([numerator],12) )/ (moving-total([denominator],12)) ) else (null)

We use this to get a rolling 12 month moving total.  The error I receive upon running the report is the following:

An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.

      Details 

UDA-SQL-0219 The function "getdate" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported. RSV-SRV-0042 Trace back: RSReportService.cpp(595): QFException: CCL_CAUGHT: RSReportService::process() RSReportServiceMethod.cpp(177): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_Request RSASyncExecutionThread.cpp(562): QFException: RSASyncExecutionThread::checkException RSASyncExecutionThread.cpp(179): QFException: CCL_CAUGHT: RSASyncExecutionThread::run(): asynchRunSpecification_Request RSASyncExecutionThread.cpp(612): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_Request Execution/RSRenderExecution.cpp(584): QFException: CCL_RETHROW: RSRenderExecution::execute Execution/RSRenderExecution.cpp(856): QFException: CCL_RETHROW: RSRenderExecution::processActiveDocuments Assembly/RSDocAssemblyDispatch.cpp(235): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssembly Assembly/RSLayoutAssembly.cpp(135): QFException: CCL_RETHROW: RSLayoutAssembly::assemble Assembly/RSDocAssemblyDispatch.cpp(295): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForward Assembly/RSReportPagesAssembly.cpp(113): QFException: CCL_RETHROW: RSReportPagesAssembly::assemble Assembly/RSDocAssemblyDispatch.cpp(254): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssembly Assembly/RSPageAssembly.cpp(247): QFException: CCL_RETHROW: RSPageAssembly::assemble Assembly/RSDocAssemblyDispatch.cpp(254): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssembly Assembly/RSTableRowAssembly.cpp(136): QFException: CCL_RETHROW: RSTableRowAssembly::assemble Assembly/RSDocAssemblyDispatch.cpp(254): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssembly Assembly/RSTableCellAssembly.cpp(94): QFException: CCL_RETHROW: RSTableCellAssembly::assemble Assembly/RSDocAssemblyDispatch.cpp(295): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForward RSQueryMgr.cpp(1007): QFException: CCL_RETHROW: RSQueryMgr::getResultSetIterator RSQueryMgr.cpp(1169): QFException: CCL_RETHROW: RSQueryMgr::createIterator RSQueryMgr.cpp(1455): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommand RSQueryMgr.cpp(1442): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommand RSQueryMgrExecutionHandlerImpl.cpp(164): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute() RSQueryMgrExecutionHandlerImpl.cpp(156): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute() RSQFSession.cpp(216): QFException: CCL_RETHROW: RSQFSession::DoRequest QFSSession.cpp(1479): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest() QFSSession.cpp(1477): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest() QFSSession.cpp(1444): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest() QFSConnection.cpp(771): QFException: CCL_RETHROW: QFSConnection::Execute QFSQuery.cpp(115): QFException: CCL_RETHROW: QFSQuery::Execute v2 CoordinationQFSQuery.cpp(3643): QFException: CCL_THROW: CoordinationPlanner 

This new field, the numerator and the denominator are all in the same query, which is then used in a line chart.  As mentioned this worked properly in 8.1 as well as Reportnet 1.1.

Any suggestions would be appreciated.

Thanks
Jeff

One of the "fixes" I've used before was to try to rebuild the report from scratch.

tried that and the same error appears.

thanks

"... UDA-SQL-0219 The function "getdate" is being used for local processing but is not available ..."

Sounds like Cognos problem. And to think of it, I believe I've had another customer reporting weird issues with getdate().

A temporary fix might be to create a user-defined function to work around this, let's say, undocumented feature ...  ;D

Found a fix...replaced all getdate() functions with current_date function and got the reports to run with correct output.

Fun, fun, fun ... isn't it?

Quote from: jguevin on 19 Apr 2007 07:26:08 AM
Found a fix...replaced all getdate() functions with current_date function and got the reports to run with correct output.

getdate() is a native MS SQL server function. Current_date is a Cognos function that has to be compiled to the correct RDBMS syntax.
perhaps version 8.2 is no longer happy with mixing Cognos and native functions in one expression?