Error Code 6706: The string contains an untranslatable character.

Database

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.

8 REPLIES
N/A

Re: Error Code 6706: The string contains an untranslatable character.

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.

Re: Error Code 6706: The string contains an untranslatable character.

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.

Re: Error Code 6706: The string contains an untranslatable character.

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.

N/A

Re: Error Code 6706: The string contains an untranslatable character.

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

Re: Error Code 6706: The string contains an untranslatable character.

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.

Re: Error Code 6706: The string contains an untranslatable character.

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.

Re: Error Code 6706: The string contains an untranslatable character.

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.

Re: Error Code 6706: The string contains an untranslatable character.

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.