Report Studio: CAST() Failing with Processing mode as Database only

[LEO]

Hi,

COGNOS 8.4

In a report studio report, when the query being used is:

select sum(amnt) from table_name;

It works fine.
I could find in the ODBC driver log file, this same query is being sent to the database server for processing.

This is working in both " limited local processing" and "database only" modes.

However, when the below query is being used:

select cast(amnt,char(20)) from table_name;

If processing mode="limited local processing", it works fine.

If processing mode="database only" mode, it fails.

The below modified query is being sent to the database server for processing is:

"select amnt from table_name;"


Later it gives error :

"PREPARE failed because the query requires local processing of the data. The option to allow local processing has not been enabled.
UDA-SQL-0535 CAST operation on given datatypes is not supported by the database. "


Can anybody help in understanding, why query is not being sent as it is?

Do we need to modify any .ini files?

How actually the query is processed from cognos connection to database server?

[MFGF]

Hi,

The problem is most likely twofold - the cast() function in the form you are using it looks like a Cognos function call rather than a database function call, and it looks like your query has no projection list when you are adding the calculation via the cast() function.  Calling a Cognos 8 function obviously requires local processing, and Cognos 8 can add the projection list, but this too requires local processing to do so.

What database are you reporting off?  MS SQL Server?  If so, try using cast(amnt as char(20)) syntax instead.  You should probably also give this derived column a name, so follow it with "as char_amnt" (or some other suitable name).

This may be enough to get it to work.  If not, you may need to encapsulate it into an outer select to provide your projection list - something along the lines of:

select q1.char_amnt from (select cast(amnt as char(20)) as char_amnt from table_name) q1

Regards,

MF.