I have to convert a measure which is loaded from SSIS to Cognos as a character data type.
I tried using cast() function. It did not work, may be because there are some NULL values in the column.
Can any one please help.
wrap a coalesce function around the measure prior to casting: coalesce([measure],'0') Better check first whether nulls are present. This approach will not work for empty string values
Quote from: shwethagp on 27 Oct 2011 02:27:05 PM
I have to convert a measure which is loaded from SSIS to Cognos as a character data type.
I tried using cast() function. It did not work, may be because there are some NULL values in the column.
Can any one please help.
Since the DB is SQL server, Use the 'replace' function to replace null values to a character string as '0' (zero) then use the cast function around it.
You can also try -
1> Create a calculated column with this expression :
If ([Date Item]) is null then ('0') else ([Data Item]).
2> Create another calculated column and cast the above data item to integer/decimal