[RS] Is it possible to validate a varchar field before casting it?

[workdan]

Hi all,

I've got the following problem: I have a transactional DB with a 'free' varchar field that users can use for any purpose. One client wants to use this field to enter a numeric percentage for some records, and then have this value used in a calculation. I've found that I cannot simply include the text data item in an arithmetic operation, like so:

[Numeric Qty] * (1 + ([Free Text Field] / 100))

I must first cast [Free Text Field] to a numeric type. However, since the contents of that field are not restricted to be numbers, is there a way to check if the contents of a row are numeric before performing the cast?

Eg. I have three records, whose [Free Text Field] fields contain the following:
'13',
'7.25',
'Pizza'

When the CAST function attempts to process the third row, it throws an error and the report crashes with "Invalid conversion of varchar to numeric". Is there any way I can check that the field contains non-numeric data (aside from NULLS and empty strings, which I can filter out with <> '') before calling CAST? I can't find a function that does this, similar to Visual Basic's IsNumeric(). Any other ideas?

The current workaround is to require the users that want this functionality to ensure that there is no non-numeric text in that field for any record, but we would of course prefer a more robust solution that doesn't require a business rule. Any suggestions would be appreciated.

Thanks!

Dan

[almeids]

Hey Dan - I hate to rain on your parade, but the "robust solution" is to add a proper numeric column to the database for this purpose!  Assuming that's not feasible, what db are you running?  Depending on where/how you are converting the varchar you might be able to use something like this Oracle code...

LENGTH(TRIM(TRANSLATE(X,' 0123456789.+-',' ')))

...which returns null for numerics and a positive value for anything else.
HTH
Steve

[workdan]

Hey Dan - I hate to rain on your parade, but the "robust solution" is to add a proper numeric column to the database for this purpose!

Haha, my sentiments exactly. I intend to request such a thing, but it'll take a while to occur, so I'm looking for an alternative in the meantime.

Assuming that's not feasible, what db are you running?  Depending on where/how you are converting the varchar you might be able to use something like this Oracle code...

LENGTH(TRIM(TRANSLATE(X,' 0123456789.+-',' ')))

...which returns null for numerics and a positive value for anything else.

I'm using MSSQL, not Oracle, but I'll see if I can accomplish something similar with the functions available to me. Thanks for your input, Steve.

[blom0344]

isnumeric() is a regular database function in SQL server 2000 and higher.

[workdan]

Good call, blom, but isNumeric doesn't seem to be available as a SQL function in Cognos. Too bad...

[blom0344]

Yes, you are right. It seems that Cognos does not offer ANY bolean native database function.
That is pretty weird.
I'd say we have to get to the bottom of this   >:(

However, I just validated an expression as isnumeric(period_year) and ran a report. Works like a charm.

The conclusion should be that even if you cannot find it in the standard listing a database function may still work if you add it manually.

[COGNOiSe administrator]

But you can always plug in your own functions.

[workdan]

But you can always plug in your own functions.

At the risk of encouraging a response of "RTFM" - how does one do that? :)

I tried the same thing, blom, calling isNumeric() even though it isn't in the function listing, but I received the expected "isNumeric is not a system, database, etc. function" error.

[blom0344]

When publishing the framework package, did you allow for using native RDBMS functions?
[create package - select function lists]

If not, you should not be able to use ANY native RDBMS function.

My deduction is that it is all or nothing. The list within the reporting tool is just for reference (main functions)

But I stand to be corrected..

[added: our own inhouse guru mentions adding an entry in an .ini file, but that is all I can get from him  :)  ]

[COGNOiSe administrator]

Chapter 2 of FM Dev Guide talks about user-defined functions.

[workdan]

Thanks for the feedback, guys.

I talked to my FM guy about the RDBMS functions and he told me we have SQL Server functions turned on, all other vendor functions off. He tried enabling all of them, but when I tried SQL Server functions afterwards - datediff, month, pi, left, isnumeric - none of them worked. I seem to recall being able to use functions like left and possibly some of the date functions successfully earlier, but then the date functions ceased to be available. Now none of the SQL Server functions appear to be available.

Not sure what the heck I'm seeing here. I'll pass any suggestions on to our FM guy, and I'll talk to him about user-defined functions.

Thanks again,

Dan

[blom0344]

Chapter 2 of FM Dev Guide talks about user-defined functions.

regular SQL server functions  <> UDF

This is not about UDF's (user-defined functions). Normally there is a dialogue screen when publishing a package and then you can choose to enable a set of native database functions.
However, I seem to recall that that only happens during the first publication

[COGNOiSe administrator]

Is IsNumeric() part of the SQL Server package in FM? If not, then this is about UDF ...  ;D

[larsonr]

Is isNumeric() available under Database Specific functions in Report Studio and Framework manager.  If it is, then you may have other errors in that report.

Cognos has admitted that its engine throws the "the function ......... is not available" when another error is happening.  Its cryptic and not the most logical response but happens.

Ex. we had a session parameter not available in Production but was used in Dev.  We kept getting the function is not available errors but it was actually caused by the session parameter. Also Check your SQL being generated by your reports to verify they run against your databases.  Then verify that Framework is up-to-date with your tables in your databases.

Also if you wish to create your own function, you could have your DBA create a function to help you through your process.  Ex..  have them create something that based on regular expressions that will send back the datatype of the item you pass it. Based on that datatype value you could conditionally format your statement or use the same logic to automatically set the value you are looking for.