How to resolve 'string contains untranslatable character' while using XMLAGG in Teradata

Database
N/A

How to resolve 'string contains untranslatable character' while using XMLAGG in Teradata

I am trying to use XMLAGG to transpose the row-wise data to comma separated value in Teradata 15. Its working fine with English (LATIN character set) characters. But while I am using other characters (Unicode character set) I am getting 'string contains untranslatable character' error.

Please suggest how to resolve the issue.

CREATE SET TABLE rv ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
coll INTEGER,
val VARCHAR(20) CHARACTER SET unicode NOT CASESPECIFIC)
PRIMARY INDEX ( coll );

insert into rv(1,'test 1');
insert into rv(1,'test 10');
insert into rv(2,'test 2');
insert into rv(2,'test 20');
insert into rv(3,'และ43831'); -- because of this row I am getting error, If we remove it below query is working fine.

sel coll,TRIM(TRAILING ',' FROM (XMLAGG(TRIM(val)|| ',' ORDER BY 1 desc) (VARCHAR(1000))))as Test_Id
from rv group by 1
3 REPLIES
Teradata Employee

Re: How to resolve 'string contains untranslatable character' while using XMLAGG in Teradata

What client character set are you using for the INSERT?

Have you checked, e.g. using CHAR2HEXINT, to be sure proper UNICODE values have been stored?

N/A

Re: How to resolve 'string contains untranslatable character' while using XMLAGG in Teradata

After using varchar(1000), character set UNICODE issue is solve.

Re: How to resolve 'string contains untranslatable character' while using XMLAGG in Teradata

what was the solution you implemented to get the XMLAGG to work?

Thanks ahead of time