Failed 6706: The string contains an untranslatable character error

General
Enthusiast

Failed 6706: The string contains an untranslatable character error

Hi,

My requirement is I want to convert a column into different rows based on a particular value(i.e. semicolon is the value as mentioned below).

I have a table with 2 two colums which has the records as mentioned below.

Col1      col2

100       abc;defg;xyz;pqrs

 I want the result in following format.

 col1    col2

100    abc

100    defg

100    xyz

100    pqrs

I am trying to write a recursive query in teradata. The logic is as mentioned below.

With recursive recursive_table(col1,col2,col3) as (

SEL

col1 as col1

,SUBSTR(col2,1,INSTR(col2,';')-1) as col2

,SUBSTR(col2,INSTR(col2,';')+1,char(col2))  as col3

FROM Source_tbale_name

UNION ALL

sel

col1 ,

CASE when instr(col3,';') >0

then SUBSTR(col3,1,INSTR(col3,';')-1)

else SUBSTR(col3,1) 

end (varchar(25)) as col2

,CASE When instr(col3,';') >0

then SUBSTR(col3,INSTR(col3,';')+1,CHAR(col3))

else NULL

end as col3

FROM recursive_table

where instr(col3,';') >=0

 

)

SEL col1,col2 from recursive_table;

But i am getting "Failed 6706: The string contains an untranslatable character error" error.

Can anyone please provide me the solution for this error with complete syntax.

Thanks,

Praveen Kumar

2 REPLIES
Junior Contributor

Re: Failed 6706: The string contains an untranslatable character error

Hi Praveen,

seems like col2 is in Character Set Unicode and there's a cast to Latin.

What's your Teradata release? INSTR is built-in in TD14, earlier you might have an own UDF implementation limited to Latin.

Btw, in TD14 there's a new STRTOK_SPLIT_TO_TABLE function to do exactly what you want.

Enthusiast

Re: Failed 6706: The string contains an untranslatable character error

Thanks Dieter, its working now.

Thanks a lot for suggesting STRTOK_SPLIT_TO_TABLE function name. Its also working fine.