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

Author Topic: Cognos trim and cast  (Read 8652 times)

Offline ov7531

  • Associate
  • **
  • Join Date: Jan 2019
  • Posts: 1
  • Forum Citizenship: +0/-0
Cognos trim and cast
« on: 30 Jan 2019 07:58:34 am »

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:


Offline Francis aka khayman

  • Statesman
  • ******
  • Join Date: Jun 2009
  • Posts: 548
  • Forum Citizenship: +26/-4
    • CognosM
Re: Cognos trim and cast
« Reply #1 on: 01 Oct 2019 09:44:39 am »
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   "