Cognos extract number from char field

[schurers]

Does the function isnumeric exist in Cognos? I have a field of credit scores that are stored in the warehouse as charlength(16). There are all kinds of text within that field including spaces, dashes, symbol, and numbers but I need to able to filter for credit scores less than 700. Is this possible in Cognos without having to do the work in ETL ? Thanks in advance....
Ex: 'Unknown'
'No# / 632'
'677'

 

[schurers]

I was able to find that Cognos does have isnumeric
I created dataitem1 ISNUMBERIC([Credit Check Score])
I created a second dataitem to cast dateitem1 as a number

CASE WHEN
[dateitem1] = 1
then
cast([Credit Check Score],float)
else
0
END

I added a filter to find any records with a credit score less than 610

[dateitem2] between 1 and 609


This post can be closed.