Cognos Analytics

 View Only
Expand all | Collapse all

UDA-SQL-0219 The function "substr" is being used for local processing but is not available as a built-in function

  • 1.  UDA-SQL-0219 The function "substr" is being used for local processing but is not available as a built-in function

    Posted Fri January 27, 2023 01:31 PM
    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.

    ------------------------------
    Adam McIlravey
    ------------------------------


  • 2.  RE: UDA-SQL-0219 The function "substr" is being used for local processing but is not available as a built-in function

    Posted Mon January 30, 2023 02:41 AM
    Hi Adam,

    I would remove the substr to be sure that there is no other issue in the report. If there is no other issue I would try to use "substring". You can also try to use substr in the framework manager modell in a new query or in a blank report to see if there a general problem with this function. I would expect that it should work.

    In the past we got some issues in reports like this because of the dqm. We solved this by moving the functionality before/ after the join. In your case you should try to do the substr in the query before the join - it shouldnt change the result.

    Well, you can also deploy the productive package in a separate folder on dev to be sure that its working with the "old" package on dev and there is no changed setting on the cognos dev enviroment.

    Regards,
    Hendrik

    ------------------------------
    Hendrik Gebert
    Managing Consultant
    ACP Digital Analytics
    ------------------------------

    Original Message


  • 3.  RE: UDA-SQL-0219 The function "substr" is being used for local processing but is not available as a built-in function

    IBM Champion
    Posted Mon January 30, 2023 04:14 AM
    Hi Adam,

    Hendrik makes some really valid points on this thread regarding diagnosis. The issue reported is that substr is thinking it is database function and its not. As Hendrik says it might be a different function and if you check underneath the list of functions it might just be a case of reselecting the function.

    Another thing that has bitten me in the past is the Package Versioning technology which I always disable now. I had an example where I made a really insignificant change and after publishing my package I got a strange unrelated error. This was due to the package storing previous 'working' versions of the package and my latest publish knocked the last working one off the list.

    Regards
    Andy

    ------------------------------
    Andrew Copeland
    ------------------------------

    Original Message