Report error when migrated to 10.2.2 version

[Universe1418]

Hi,

We are currently migrating our environment from 10.1.1 to 10.2.2, new content store was also created for this migration (Oracle).

However we experienced an error while testing some of the migrated reports:

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'. UDA-SQL-0460 A general exception has occurred during local processing. UDA-EE-0094 The operation "extract" is invalid for the following combination of data types: "character" and "integer"

The report was not modified at all and confirmed working in 10.1.1 version, as I try to isolate the issue I found out that the error is a result of a detail filter syntax related to this code:

to_date(?End Date?, 'YYYY-MM-DD HH24:MI:SS') + 1

It's a high risk to modify all reports using this kind of syntax and we know it is working well in 10.1.1 but how come not in 10.2.2 version.

Hoping that you can share your thoughts and advice on this issue of mine. Thank You!

[AnalyticsWithJay]

Are you positive that's what was causing the issue? Did the report run after disabling that detail filter?

Cognos has gotten more strict in each release, and we saw a lot of this in the move from C8 to C10. Previously, it allowed developers to get away with "loose" or bad syntax. For example, it allowed you to say Date = ?myPrompt?, where the value of prompt was a string. After upgrading, many people experienced the same error message below, but for "date" and "character".

The message you're getting is very similar in that it seems you're comparing a character to an integer somewhere. Do you have a "EXTRACT(MONTH FROM DATE_COLUMN) + 1" syntax anywhere? This is what it looks like to me.

You'll have to fix this syntax for the upgrade.

[Universe1418]

Hi,

Yes, The report is running after disabling the said detail filter (to_date(?End Date?, 'YYYY-MM-DD HH24:MI:SS') + 1).

The weird thing is that we don't have an extract function created in the report with this code:

EXTRACT(MONTH FROM DATE_COLUMN) + 1

Only extract(year,?End Date?) which is working correctly, I'm looking into a way to not modify the affected reports for this migration.

Kindly let me know if you have some ideas. Thank You!

[AnalyticsWithJay]

Yes, The report is running after disabling the said detail filter (to_date(?End Date?, 'YYYY-MM-DD HH24:MI:SS') + 1).

I would log a support ticket with Cognos in that case, as you don't have any "bad" syntax in this statement. You're adding an integer to a day, and you're doing it within Oracle. I tested it and can see it in 10.1.1 being sent within the database query and working correctly.

The error indicating you're comparing a character to an integer looks suspicious.

Out of curiosity, if you remove the "+ 1" from the filter, does it work? This would help confirm the issue.

[Universe1418]

Hi,

by removing + 1 the issue still remains, it seems that the issue is related in the to_date function because after modifying the code to something like this it run:

From:

to_date(?End Date?, 'YYYY-MM-DD HH24:MI:SS') + 1

To:

_add_days(?End Date?, 1)

However modifying all reports is really risky to don in this point and I know it is working in 10.1.1 how come not in 10.2.2

I've raised a PMR in IBM but even them is having a hard time to give me solutions that does not require modifications in the report.

[AnalyticsWithJay]

Hi,

by removing + 1 the issue still remains, it seems that the issue is related in the to_date function because after modifying the code to something like this it run:

From:

to_date(?End Date?, 'YYYY-MM-DD HH24:MI:SS') + 1

To:

_add_days(?End Date?, 1)

However modifying all reports is really risky to don in this point and I know it is working in 10.1.1 how come not in 10.2.2

I've raised a PMR in IBM but even them is having a hard time to give me solutions that does not require modifications in the report.

We're getting closer to the issue. to_date() is a database function, and _add_days() is a locally processed function by Cognos.

Could you test something to narrow this down further? Could you create a new list report with one data item only? The definition of the data item will be: to_date(?End Date?, 'YYYY-MM-DD HH24:MI:SS')

When prompted, enter 2016-05-02 18:05:05

I'm interested in knowing what the behavior is when nothing else is on the report. We know there's no local processing anywhere.

[Universe1418]

Hi,

I tried the suggested test and got the error below as response:

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'. UDA-SQL-0219 The function "to_date" 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.
 
Hoping that you can give more insights, your suggestions are really helpful. Thank You!

[AnalyticsWithJay]

Hi,

I tried the suggested test and got the error below as response:

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'. UDA-SQL-0219 The function "to_date" 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.
 
Hoping that you can give more insights, your suggestions are really helpful. Thank You!

I'm pretty sure I know why this happened. You received this particular error because I asked that you only create this data item. Since there was no need to go to the database to query, Cognos did everything locally and tried to use to_date (which isn't a local function of course).

Could we repeat the exercise please, but this time add one additional data item to the query so Cognos goes to the database?

Thanks,
Jay

[Universe1418]

Hi Jay,

By adding another data item which came from the package, The test did run successfully.

It is weird that it shows error when run from the migrated reports.

See attached images for references.

[AnalyticsWithJay]

Hi Jay,

By adding another data item which came from the package, The test did run successfully.

It is weird that it shows error when run from the migrated reports.

Wonderful. Now we know the issue is due to something specifically in those reports. I still think this is going to eventually lead us to my original answer in this post.

I think there are multiply things happening:
1. There's local processing in your report (that's fine)
2. The way your query is designed, it's trying to process the date returned by to_date()
3. There's more processing happening on that date, and it doesn't like comparing a character to an integer (something which Cognos "allowed" in some cases in the past)

It's not so easy to trouble this part without having the report. If you're able to post the report specs and the SQL generated by the query, I can take a look.

I don't think you will find a solution where these reports upgrade without any modification, but at least if you know the root cause you could make a more informed decision.

Cheers,
jay