All Forums Database
itib 2 posts Joined 02/16
16 May 2016
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

 

Fred 1096 posts Joined 08/04
16 May 2016

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?
 

itib 2 posts Joined 02/16
17 May 2016

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

huskyboy 1 post Joined 04/16
01 Jun 2016

what was the solution you implemented to get the XMLAGG to work?
 
Thanks ahead of time

You must sign in to leave a comment.