validate whether cast will work or not

Database

validate whether cast will work or not

Is there a function to check whether a cast will work for individual records?  Currently, if one record in a set will fail a cast, the complete SQL will fail with error such as:  "SELECT Failed. 2621:  Bad character in format or data of TABLE.COLUMN."

Is there function like the following ones defined below that could do this check per record  (a function that returns 0 or 1 per record per success/failure)?  We could then delete or filter out those records before running actual cast.

1)   isCastValid(COLUMN, "type")

2)  isValid(cast(COLUMN as <type>))

3)   or some other option

If not, is there something in the Teradata APIs that will help support a UDF such as this?   Thanks.

2 REPLIES
Fan

Re: validate whether cast will work or not

I believe the only chance you would face this is while casting some non-numeric to a numeric data type. If so, please see if you have got is_num() function available in your DB. 

is_num (100) returns 100

while

is_num(world1000) returns null ...

so you can nail that record by checking the is_NUM() is null!

hope this helps

Re: validate whether cast will work or not

isNum is not enough.  My starting point is strings, but I want to be able to convert to any data type without the SQL blowing up (can only do by removng bad records first - why I want the function).   Here are a couple of examples that fail too (numeric overflow, then bad date, I am sure there are others).

select cast('100000' as SMALLINT);

select cast('2013-23-03' as DATE);

Maybe there is no way, but just checking.  If you wanted to implement ELT approach and always loaded data as strings with TPT, this type of function would seem necessary for data type validations (main reason I am asking).  Thanks.