If you are unable to create a new account, please email support@bspsoftware.com



MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Cognos trim and cast

Started by ov7531, 30 Jan 2019 07:58:34 AM

Previous topic - Next topic



I want to cast the following [After] results to decimal.

Before                          After
Strike Rate(%): 3.25         3.25
Strike Rate:  4.00%         4.00
None                                     0
NA                                     0
Strike Rate(%): 0             0

I am using the following expression in Cognos to extract the number appearing after the colon from [Before]

The original field type is text to begin with.

    case when  trim( trim( LEADING ':', substring(replace([Before],'%',''), position(':', [Before])) ))  = 'None' Then '0' 

    { ..case also goes on to include .....= 'NA'  Then '0'    ; ...... = ''  Then '0'  ;  ....IS NULL Then '0' ....  else  trim( trim( LEADING ':', substring(replace([Before],'%',''), position(':', [Before])) ))  }

The problem is that when cast I try casting the [After] results to decimal , such as:       cast([Results_above]as decimal(5,4))   I keep getting error:  UDA-EE-0011 A "freeform" number is invalid.UDA-SQL-0460...... which I think indicates there are some other text strings I am not accounting for. 

Is there a way to blanket treat all the text string results from the case statement as .......  = 'unknown result' Then '0' so that I could then cast the results to decimal?

Or perhaps a better way overall to accomplish this:


Francis aka khayman

try to cast the individual values first and see if cognos agrees...

example: cast('3.25' as decimal(5,4)), cast('4.00' as decimal(5,4)), etc

if all values are ok, then you are getting values that are not what they seem to be... example 0 may actually be "0   "