The string contains an untranslatable character during Fastexport

Tools

The string contains an untranslatable character during Fastexport

Hi Experts,

While fastexporting from a table to a text file in record mode, i am getting "The string contains an untranslatable character" error.In my fastexport, i am casting a decimal field to BIGINT and its causing this error.As soon as i change the casting from decimal to varchar, its working amazingly fine.Below is the DDL for my source table and the Fastexport script i am using.Also, the target is a default ASCII character set, so no issues with that:

DDL:-

 

CREATE MULTISET TABLE ETL_TEMP_RHI.LZ_EDW_DIAG_WRK , NO FALLBACK , NO BEFORE JOURNAL , NO AFTERJOURNAL , CHECKSUM = DEFAULT , DEFAULT MERGEBLOCKRATIO

CLM_LINE_SRVC_STRT_DT INT ,

SRCID DEC ( 28 , 0 ) NOT NULL ,

  PATIENTID VARCHAR ( 30 ) CHARACTER SET LATIN NOT CS ,

   DIAG_CD_ORDR_NBR DEC ( 5 , 0 ) NOT NULL ,

   DIAGNOSIS_CODE VARCHAR ( 7 ) CHARACTER SET LATIN NOT CS ,

    POA_CODE VARCHAR ( 10 ) CHARACTER SET LATIN NOT CS ,

    LOAD_LOG_KEY BIGINT NOT NULL ,

    ICD_VRSN_CD CHAR ( 10 ) CHARACTER SET LATIN NOT CS NOT NULL DEFAULT '9         ' ) PRIMARY INDEX ( SRCID ) ;

 

FASTEXPORT script :

 

    ************************************************************/

     .BEGIN EXPORT SESSIONS 32;

0009 .export outfile

     /gpfs05/prod/edl/pcenterdata/TgtFiles/RHI/RHI_EDW_Diagnosis_Codes_201305012

     22530.DAT format text mode record;

0010 /*******************************************************************************

     ******************************************************* 

     SQL to export data into Diagnosis Codes data File for Load Process

     ***************************************************************************

     ************************************************************/

     SELECT

     CAST(

             CAST(CASE WHEN CLM_LINE_SRVC_STRT_DT IS NULL OR

     CLM_LINE_SRVC_STRT_DT NOT BETWEEN '1900' AND '2079' THEN '1900' 

        ELSE CAST(CLM_LINE_SRVC_STRT_DT AS CHAR(4)) END AS VARCHAR(4))|| '?' ||

             TRIM(CAST(SRCID AS BIGINT))|| '?' ||

        TRIM(CAST(DIAG_CD_ORDR_NBR AS BIGINT))|| '?' ||

             TRIM(PATIENTID)|| '?' ||

        CAST(CASE WHEN DIAGNOSIS_CODE IN('UNK','NA',' ') OR DIAGNOSIS_CODE IS NULL

     THEN '0' ELSE DIAGNOSIS_CODE END AS VARCHAR(7))|| '?' ||

             CAST(CASE WHEN POA_CODE IN('UNK','NA',' ','~01','~02','~03') OR

     POA_CODE IS NULL THEN '' ELSE POA_CODE END AS VARCHAR(1))|| '?' ||

     /*****Removed from layout as part of ICD10 Remediation******/

     --      'DG'|| '?' ||

             'ETL_PROCESS'|| '?' ||

             CAST(CAST(CURRENT_DATE  AS DATE FORMAT 'YYYY-MM-DD' ) AS

     VARCHAR(10) ) || '?' ||

             'ETL_PROCESS'|| '?' ||

             CAST(CAST(CURRENT_DATE  AS DATE FORMAT 'YYYY-MM-DD' ) AS

     VARCHAR(10) ) || '?' ||

             TRIM(LOAD_LOG_KEY) || '?' ||

     /*****Added to layout as part of ICD10 Remediation******/

             TRIM(ICD_VRSN_CD)

     AS CHAR( 170))

     FROM

     LZ_EDW_DIAG_WRK

     ;

0011 .end export;

Please help.

 

 

 

2 REPLIES

Re: The string contains an untranslatable character during Fastexport

When i change TRIM(CAST(SRCID AS BIGINT)) to TRIM(CAST(SRCID AS VARCHAR)) & TRIM(CAST(DIAG_CD_ORDR_NBR AS BIGINT)) to TRIM(CAST(DIAG_CD_ORDR_NBR AS VARCHAR)), its giving no error and working fine....

Teradata Employee

Re: The string contains an untranslatable character during Fastexport

format "text" is used for character data types like CHAR, VARCHAR, LONG VARCHAR etc. What you are doing here is exporting integer data types in text format. While this scenario will work in some cases but it is not recommended to use TEXT format for integer data types.

See FastExport manual for details. Here is what it says:

Note: TEXT format should only be specified for character data. Do not
specify TEXT format for binary data, such as, INTEGER, BYTEINT,
PERIOD, and other binary data. Depending on the actual byte values of
the binary data, unexpected results may occur.