06-26-2014 12:09 PM
I'm tring to substring a character field (eg. 00000000012345678912) in proc sql and and need to convert it to numeric. I only need the 1234567891 from the field (position 10 for 11 fields). I'm using this format :
cast((substr( AR_NO,10,11)) as integer) as AR_NO_SUB , but getting an error (below)
ERROR: Teradata row not delivered (trget): Overflow occurred computing an expression involving DB.AR_NO.
if I do cast((substr( AR_NO,4,11)) as integer) as AR_NO_SUB i get 69267, but
if I do cast((substr( AR_NO,5,11)) as integer) as AR_NO_CUB I get the error as above in red.
Thankss for your help.
06-26-2014 01:01 PM
If this is the correct way of doing it
max(length(a.AR_NO)) as max, min(length(a.AR_NO)) as min, then the results are 20 max and 1 min.. Knowing this info, is there a fix to get this working ?
06-26-2014 01:20 PM
My question would be why is the length less than what you expect.
To deal with it from a programming perspective, you can use a case statement.
case when length(ar_no)>20 then cast ....
else Null end as new_var
06-26-2014 12:42 PM
Good question.. but I guess I should of mentioned that I need to connect to Teradata first, since it is data from the Enterprise Datawarehouse. So this I'm actually doing in Teradata SQL embedded in proc sql.