UDA-SQL-0219 The function "to_number" is not available as an external, database,

[mgibson]

Hi,

I'm getting this error "UDA-SQL-0219 The function "to_number" is not available as an external, database, or built-in function.", and the local Cognos support guys have been unable to resolve it for us.  They've indicated that it's a bug with a workaround, but have been unable to obtain details on that workaround.  It's all very strange!

The 'Model' Query Item within the FM model used to work fine, but then something changed making it produce the error, and we can't get it back to it's functioning state.

Our own workaround has been to move the function into the original underlying SQL Query Subject via a calulation, but we want it to work as a Model Query Item.

Any ideas?

Thanks
Mick

[larsonr]

This could be caused by a number of different issues. 

Check the following...

The query item you are using it in is referencing tables in a single or multiple databases?

Is the query you are running invoking local processing.  Try switching the query without the function to database only and see if it runs.  Sometimes if Cognos cannot run the data against the database but brings the set back to the server then runs the to_number it will fail because to_number is not recognized by Cognos.  I found this to be the case in several occasions.

Are you using Cognos macros in your table at all?  If you are, make sure they are built correctly and queryable.

These are some items I have had to check when building reports in the past.  Hope it helps.