Cognos trim and cast

[ov7531]

Hi, 

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:



Thanks.

[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   "