Monday, July 16, 2012

IBM COGNOS - Playing around the system to solve the BLOB issue / error

IBM COGNOS 8 does not support text field on most versions. So what do we do ???? Play around the system to solve it :)


My scenario -- out of the 10 columns on the report, 2 were text fields. I built metadata for the 8 columns and wrote a SQL query with substr function for the 2 text columns. Then, I joined these two queries subjects with the common key.


One more thing, I used Pass Through SQL option. Reason, if we use the Native sql --- we cannot add a prompt on the sql. If we use COGNOS sql --- we cannot add the a substr function. So, we use Pass Through sql which allows you to add substr function as well as prompt on the query for better performance.


Here is an syntax / example from the IBM website



http://www-01.ibm.com/support/docview.wss?uid=swg21341148



Select * from GOSL.dbo.ORDER_HEADER 
Where ORDER_DATE = {ts # sq(prompt('TestDate', 'datetime') )# }

F Y I

http://www-01.ibm.com/support/docview.wss?uid=swg21341127


Problem(Abstract)

This document describes the difference between Native SQL, Cognos SQL and Pass-through SQL in Framework Manager and Report Studio (SQL Queries).

Resolving the problem

Framework Manager generates the most optimized SQL possible while preserving its functionality. In this way, Cognos SQL offers the most added value.
Native SQL is the SQL the data source uses such as Oracle SQL. You cannot use Native SQL in a model query subject that references more than one data source in the project.

Pass-through SQL lets you use Native SQL without any of the restrictions the data source imposes on sub queries. Pass-Through SQL query subjects are not processed as subqueries. Instead, the SQL for each query subject is sent directly to the data source where the query results are generated.

Further details can be found in the Framework Manager User Guide in the chapter on Modeling Relational Metadata.
I guess this helps.