UDA-SQL-0219 The function "substr" is being used for local processing

[adam_mc]

I have modified a FM model and published a new package in our DEV environment.
Subsequently, I have copied a report from Production (which runs without issue) down to the DEV environment.

As a run a test of the report in DEV, it is failing with a "UDA-SQL-0219 The function "substr" is being used for local processing but is not available as a built-in function" error message.
In isolating the error, I can see that two queries are joined in the report and the resulting query has a substr calculation (but, this is the same in Production).
The only difference (that I am aware of) is the newly published package in DEV.
Due to the package change the driving queries of the join have different SQL's compared to Production (I would expect this).
I cannot get the SQL of the failing query in the report as it is failing!

I have set the Data Source in the package to allow Oracle functions so I am not sure what else may have caused this.
Is there something at a Package or Environment level that could be causing this?
Ideally, I do not want to change the Oracle functions to be native Cognos functions as there may be significant other report changes if this is caused by the new package.

Any thought would be greatly appreciated.
Thanks in advance,
Adam.

[MFGF]

I have modified a FM model and published a new package in our DEV environment.
Subsequently, I have copied a report from Production (which runs without issue) down to the DEV environment.

As a run a test of the report in DEV, it is failing with a "UDA-SQL-0219 The function "substr" is being used for local processing but is not available as a built-in function" error message.
In isolating the error, I can see that two queries are joined in the report and the resulting query has a substr calculation (but, this is the same in Production).
The only difference (that I am aware of) is the newly published package in DEV.
Due to the package change the driving queries of the join have different SQL's compared to Production (I would expect this).
I cannot get the SQL of the failing query in the report as it is failing!

I have set the Data Source in the package to allow Oracle functions so I am not sure what else may have caused this.
Is there something at a Package or Environment level that could be causing this?
Ideally, I do not want to change the Oracle functions to be native Cognos functions as there may be significant other report changes if this is caused by the new package.

Any thought would be greatly appreciated.
Thanks in advance,
Adam.

Hi,

As I see it, you have two choices here. Firstly, you can try to find what change in your FM model has caused this to happen. Typically you see this kind of issue where the query processing isn't being passed to the database. Substr() isn't a Cognos function, so the only way it will run is if it's passed to the database. Secondly, you could try replacing the Substr() function with a Substring() function, which is an inbuilt Cognos function. I'd generally recommend you use the inbuilt functions wherever possible to give yourself the best chance of avoiding issues like this one.

Cheers!

MF.

[adam_mc]

Unfortunately, removing the Oracle functions from the report and replacing them with native Cognos functions is not a practical solution as this FM model is likely used in hundreds, if not thousands, of reports which may have similar Oracle functions. I agree that it is not best practice to have vendor specific functions in the Presentation Layer, but this report long pre-dates me at this company.
Therefore, in looking into this further, I was able to resolve the issue by restoring to a previous version of the model (luckily, we save in BitBucket) and then remake the changes I made in Framework Manager in a better manner. 
It is likely that I either had added circular or cross join functionality that I didn't want (it's a single huge messy model where a simple error can have big consequences).
My presumption is that Cognos was throwing this error when it was forced to do queries in the Presentation Layer (local processing) rather than returning queries to the database, thus the failure of the Oracle functions.

In any case, the FM model is now working as expected, testing of a number of reports has not compromised them, and the new functionality added via the model is working as expected.

Thanks for your help,
Adam.

[MFGF]

Unfortunately, removing the Oracle functions from the report and replacing them with native Cognos functions is not a practical solution as this FM model is likely used in hundreds, if not thousands, of reports which may have similar Oracle functions. I agree that it is not best practice to have vendor specific functions in the Presentation Layer, but this report long pre-dates me at this company.
Therefore, in looking into this further, I was able to resolve the issue by restoring to a previous version of the model (luckily, we save in BitBucket) and then remake the changes I made in Framework Manager in a better manner. 
It is likely that I either had added circular or cross join functionality that I didn't want (it's a single huge messy model where a simple error can have big consequences).
My presumption is that Cognos was throwing this error when it was forced to do queries in the Presentation Layer (local processing) rather than returning queries to the database, thus the failure of the Oracle functions.

In any case, the FM model is now working as expected, testing of a number of reports has not compromised them, and the new functionality added via the model is working as expected.

Thanks for your help,
Adam.

Hi,

It can be very frustrating when you inherit someone else's model that is messy and quirky like this. I totally get that it would be hugely risky to start switching out functions. Looks like you did a great job in figuring out how to re-do the changes in a way that doesn't break anything. Huge kudos!

Cheers!

MF.