[RS C8.2] Function in RS Report No longer working post upgrade to 8.2

[jguevin]

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

[COGNOiSe administrator]

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

[jguevin]

tried that and the same error appears.

thanks

[COGNOiSe administrator]

"... 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

[jguevin]

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

[COGNOiSe administrator]

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

[blom0344]

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?