In one of my FM query subjects I'm trying to cast a BLOB to varchar. This is my expression:
CAST([Database Layer].[my table].[field_data_bytes], varchar(255))
Cognos seemingly will accept that as is - the blue arrow will light up and show. But when I try to run and validate it, I get the following error. What am I doing wrong and is there a better way to convert a BLOB?
RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'.
UDA-SQL-0107 A general exception has occurred during the operation "prepare".
ORA-00904: "coguda#cast_varchar": invalid identifier
open a PMR with IBM support as the query engine is generating an invalid statement which ORACLE is rejecting.
ORACLE itself provides packages with built-in functions which can allow you to type convert a BLOB to varchar format.
see https://docs.oracle.com/database/121/ARPLS/d_lob.htm
Why does your data mart contain a blob column that you want to convert to a regular varchar in your visualisation tool metalayer?