Topic
  • 4 replies
  • Latest Post - ‏2010-12-15T21:58:30Z by SystemAdmin
SystemAdmin
SystemAdmin
15496 Posts

Pinned topic Cognos 8.4 and SQL Function errors

‏2010-12-08T17:29:01Z |
After upgrading to Cognos 8.4 from 8.3 we have one report that uses SQL functions (right, ltrim and str) that does not run.

The error is: UDA-SQL-0219 The function "right" 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.

I've seen other posts that suggest adding other data items that access the database. There is probably a couple dozen data items in this query that access the database. The report runs fine in 8.3. Is there some setting in Framework Manager that I might be missing?

Sandy
Updated on 2010-12-15T21:58:30Z at 2010-12-15T21:58:30Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Cognos 8.4 and SQL Function errors

    ‏2010-12-08T18:26:07Z  
    Additional Information-

    I see that no SQL functions work in any packages. We are using a new SQL database with the 8.4 upgrade. Is there anything that needs to be done on the database side that we might be missing to allow for using functions in Cognos?

    Sandy
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Cognos 8.4 and SQL Function errors

    ‏2010-12-08T23:54:59Z  
    Additional Information-

    I see that no SQL functions work in any packages. We are using a new SQL database with the 8.4 upgrade. Is there anything that needs to be done on the database side that we might be missing to allow for using functions in Cognos?

    Sandy
    The error that you are getting can occur if there is another operation which must be performed before the right() function which causes local processing. What happens in such a scenario is that the Cognos query engine will send as much as it can to the database up to the point where local processing must be applied. Then it will fetch the records back to the Cognos server and try to apply the remainder of the query operations. In your case the right() function cannot be sent to the initial database query so Cognos is trying to process it locally but it cannot because the right() function is a database function and not a local Cognos function.

    It will be necessary to do a bit more investigation to find the source of the local processing. I would recommend modifying the properties of the query in Report Studio to set the query to use Database Only processing. Then validate the report to see what messages are returned. The messages should give you some indication of the source of the local processing (you may need to temporarily delete the right() function from the query to expose other messages).
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Cognos 8.4 and SQL Function errors

    ‏2010-12-15T17:38:23Z  
    The error that you are getting can occur if there is another operation which must be performed before the right() function which causes local processing. What happens in such a scenario is that the Cognos query engine will send as much as it can to the database up to the point where local processing must be applied. Then it will fetch the records back to the Cognos server and try to apply the remainder of the query operations. In your case the right() function cannot be sent to the initial database query so Cognos is trying to process it locally but it cannot because the right() function is a database function and not a local Cognos function.

    It will be necessary to do a bit more investigation to find the source of the local processing. I would recommend modifying the properties of the query in Report Studio to set the query to use Database Only processing. Then validate the report to see what messages are returned. The messages should give you some indication of the source of the local processing (you may need to temporarily delete the right() function from the query to expose other messages).
    Thanks Phil for your explanation. I ended up taking out all of the SQL functions and using Cognos functions as best I could...Cognos functions do have their limitations.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Cognos 8.4 and SQL Function errors

    ‏2010-12-15T21:58:30Z  
    Thanks Phil for your explanation. I ended up taking out all of the SQL functions and using Cognos functions as best I could...Cognos functions do have their limitations.
    I didn't mean for you to take out the database functions. It would be better to find the source of the local processing which is causing the original problem. If you can identify why local processing is required then it may be possible to apply a different approach where local processing can be avoided. This will give you better performance in the long run.