All Forums Database
superjuanito88 17 posts Joined 05/12
09 Jul 2012
Error Code 6706: The string contains an untranslatable character.

Greetings, running this query I get the error message. Error Code 6706: The string contains an untranslatable character. I find it very strange and really had not seen him, if I could give an indication of how to find the solution they are grateful.

INSERT INTO  EDW_ODS.AGENDA_CANCELACION(       

      CLIENTE_PACIENTE_ID

     ,CODIGO_CLIENTE_PACIENTE_OP

     ,CODIGO_TIPO_IDENT_PAC_OP

     ,FECHA_HORA_CITA

     ,CLIENTE_PROFESIONAL_ID

     ,CODIGO_CLIENTE_PROFESIONAL_OP

     ,CODIGO_TIPO_IDENT_PRO_OP

     ,SERVICIO_CITA_ID

     ,NUMERO_CONS_HORA_CORRELATIVO

     ,CLIENTE_IPS_ID

     ,CODIGO_CLIENTE_IPS_OP

     ,CODIGO_TIPO_IDENT_IPS_OP

     ,PLAN_CITA_ID

     ,PLAN_AGRUPADOR_CITA_ID

     ,IND_TIPO_CANCELACION

     ,CAUSA_CANCELACION_CITA_ID

     ,USUARIO_RED_ASIGNA_TXT

     ,USUARIO_RED_CANCELA_TXT

     ,NUMERO_CONSECUTIVO_CANCELACION

     ,OBSERVACION_CANCELACION_TXT

     ,IND_SOBRECUPO

     ,EDW_AUDIT_TRAIL_USER_NAME

     ,EDW_AUDIT_TRAIL_DTTM

     ,EDW_AUDIT_TRAIL_PROCESS_CD

)                            

 SELECT                                 

      CAST(B8.CLIENTE_ID AS INTEGER) CLIENTE_PACIENTE_ID

     ,CODIGO_CLIENTE_PACIENTE_OP

     ,CODIGO_TIPO_IDENT_PAC_OP

     ,CAST(FECHA_HORA_CITA AS TIMESTAMP(0))

     ,CAST(C2.CLIENTE_ID AS INTEGER)  CLIENTE_PROFESIONAL_ID

     ,CODIGO_CLIENTE_PROFESIONAL_OP

     ,CODIGO_TIPO_IDENT_PRO_OP

     ,CAST(S.SERVICIO_CITA_ID  AS INTEGER)

     ,NUMERO_CONS_HORA_CORRELATIVO

     ,CAST(E5.CLIENTE_ID  AS INTEGER) CLIENTE_IPS_ID

     ,CODIGO_CLIENTE_IPS_OP

     ,'CSIPS'

     ,CAST(P.PLAN_CITA_ID   AS INTEGER)

     ,CAST(PA.PLAN_AGRUPADOR_CITA_ID   AS INTEGER)

     ,IND_TIPO_CANCELACION

     ,CAST(CC.CAUSA_CANCELACION_CITA_ID   AS INTEGER)

     ,USUARIO_RED_ASIGNA_TXT

     ,USUARIO_RED_CANCELA_TXT

     ,NUMERO_CONSECUTIVO_CANCELACION

     ,OBSERVACION_CANCELACION_TXT

     ,IND_SOBRECUPO

     ,USER

     ,CURRENT_TIMESTAMP(0)

     ,1600011

               FROM EDW_STAGE.PE712_AGCANC TEMP

                 LEFT OUTER JOIN  EDW_VIEW.V1_IDENTIFICATION_TYPE Q4

                 ON (Q4.EXT_IDENTIFICATION_TYPE_OP = TEMP.CODIGO_TIPO_IDENT_PAC_OP )

               

                 LEFT OUTER JOIN  EDW_VIEW.VM_EPS_IDENT_CLIENTE_ACT B8

                 ON (B8.NUMERO_IDENTIFICACION = TEMP.CODIGO_CLIENTE_PACIENTE_OP

                AND Q4.EXT_IDENTIFICATION_TYPE_CD  = B8.TIPO_IDENTIFICACION_CD )

               

                 LEFT OUTER JOIN  EDW_VIEW.V1_IDENTIFICATION_TYPE R5

                 ON (R5.EXT_IDENTIFICATION_TYPE_OP = TEMP.CODIGO_TIPO_IDENT_PRO_OP )

               

                 LEFT OUTER JOIN  EDW_VIEW.VM_EPS_IDENT_CLIENTE_ACT C2

                 ON (C2.NUMERO_IDENTIFICACION = TEMP.CODIGO_CLIENTE_PROFESIONAL_OP

                 AND R5.EXT_IDENTIFICATION_TYPE_CD  = C2.TIPO_IDENTIFICACION_CD)

               

                LEFT OUTER JOIN  EDW_VIEW.V1_IDENTIFICATION_TYPE S4

                 ON (S4.EXT_IDENTIFICATION_TYPE_OP = 'CSIPS')

               

                 LEFT OUTER JOIN  EDW_VIEW.VM_EPS_IDENT_CLIENTE_ACT E5

                 ON (E5.NUMERO_IDENTIFICACION = TEMP.CODIGO_CLIENTE_IPS_OP

                AND S4.EXT_IDENTIFICATION_TYPE_CD  = E5.TIPO_IDENTIFICACION_CD )

               

                 LEFT OUTER JOIN  MDB_CONSULTAS.VC_SERVICIO_CITA S

                 ON (S.CODIGO_SERVICIO_CITA_OP = TEMP.CODIGO_SERVICIO_OP )

               

                 LEFT OUTER JOIN  MDB_CONSULTAS.VC_PLAN_CITA P

                 ON (P.CODIGO_PLAN_CITA_OP = TEMP.CODIGO_PLAN_OP )

               

                 LEFT OUTER JOIN  MDB_CONSULTAS.VC_PLAN_AGRUPADOR_CITA PA

                 ON (PA.CODIGO_PLAN_AGRUPADOR_CITA_OP = TEMP.CODIGO_PLAN_AGRUPADOR_CITA_OP )

               

                 LEFT OUTER JOIN  MDB_CONSULTAS.VC_CAUSA_CANCELACION_CITA CC

                 ON (CC.CODIGO_CAUSA_CANCELACION_OP = TEMP.CODIGO_CAUSA_CANCELACION_OP )

                 WHERE

                 B8.CLIENTE_ID IS NOT NULL

                AND C2.CLIENTE_ID IS NOT NULL

                AND S.SERVICIO_CITA_ID IS NOT NULL

                AND E5.CLIENTE_ID IS NOT NULL

                AND P.PLAN_CITA_ID IS NOT NULL

                AND PA.PLAN_AGRUPADOR_CITA_ID IS NOT NULL

                AND CC.CAUSA_CANCELACION_CITA_ID IS NOT NULL;

 

Error Code 6706:  The string contains an untranslatable character. 
ulrich 816 posts Joined 09/09
09 Jul 2012

There is some inplicit character conversion ongoing.

Most likely the character set of your source table differ from your target table.

If your source table contains unicode character fields which also contains real unicode values and your target table field is defined as latin character set you will get this error code.

Check the Translate (with Error Option) and Translate_Check function.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

superjuanito88 17 posts Joined 05/12
09 Jul 2012

Yes, thanks indeed that was the problem the temporary table was the Unicode character set, but the table would be fine where the data was the latin character set, changing the character of the temporary and set them as the final error disappeared and insert records.

virenag 6 posts Joined 09/12
08 Oct 2012

I get this error when I CAST an Integer/Decimal/Float field to VARCHAR and concatenate to VARCHAR field in a SELECT clause. e.g.
 
SELECT  CAST(COALESCE(A,"") AS VARCHAR(11)) || '|' || TRIM(B) FROM <DB>.<TB>
gives 6706 error (Untranslatable character)
However,
SELECT CAST(A AS VARCHAR(11)), TRIM(B) FROM <DB>,<TB>
works fine.
 
So, I am not sure if it is a Unicode/Latin issue.

ulrich 816 posts Joined 09/09
08 Oct 2012

its likely to be -
check Dieters reply on http://forums.teradata.com/forum/database/implicit-data-type-conversion-to-char-ends-up-in-unicode
you can validate by

create volatile table sql_test as (
SELECT  CAST(COALESCE(A,"") AS VARCHAR(11)) || '|' || TRIM(B)  as test FROM <DB>.<TB>
) with no data 
no primary index
on commit preserve rows;

show table sql_test;
I guess test will be unicode

 

I think 

SELECT  COALESCE(cast(A as varchar(11)),"" ) || '|' || TRIM(B)  as test FROM <DB>.<TB>

should work

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

virenag 6 posts Joined 09/12
08 Oct 2012

Ulrich,
 That did the trick. Yes, it comes out as UNICODE if you use COALESCE first and then CAST.
If CAST first and then COALESCE , it is LATIN.
I wonder why does it do that way implicitly.
Does it say that anywhere in Teradata DOC?
Thanks for the help.
 

virenag 6 posts Joined 09/12
08 Oct 2012

Teradata TPT Job ignores OUTLIMIT value.
BTEQ RETLIMIT and FEXP OUTLIMIT work fine.
Does anyone know why TPT ignores OUTLIMIT?
I defined INTEGER OutLimit = 1, in TPT Job but it retrieved all the rows anyway. 
What is the threshold of Data Volume where Data Volume is defined as (Num of records * record length) for using TPT over FEXP? 
Reasons to use TPT and FEXP?
I use BTEQ below 20MB of data.

jcllings 3 posts Joined 07/12
25 Jan 2013

I think I found the pesky blighter. If you open Word and type "--[enter]" you will notice that it becomes one long dash. Paste the resulting long dash character into SoapUI and hit a simple inbound web service with a TD backend that does only plain Jane inserts and you see this error. This is a cut 'n paste deal from Office, I bet.
Jim C.

MrMartinJKelly 1 post Joined 12/14
04 Dec 2014

Appreciate this is an old post but top of popular search engine so thought I would add my resolution.
I get this error a lot when I am running code designed for IBM Personal Communications on Teradata SQL Assistant. There is often a little square at the bottom of the code (essentially a tab but held in text), SQL assistant cannot handle this. If you delete this then the code runs.

You must sign in to leave a comment.