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