FastLoad - Data Conversions

Tools & Utilities

FastLoad - Data Conversions

Hello, 

I am having issues performing data conversions specially with dates and timestamps while using fastload. My script is pasted below. All my rows are being imported and sent to error_1 table because of column CLR_DT with error 2665. My table is being is created before using the fastload utility. Any commentary is appreciated.

Destination Table DDL:

CREATE MULTISET TABLE rz_scratch.zack_test3 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      CLIENT_ID VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      SRC_SYS_ID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      CO_CD VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      ACCT_NBR VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      TXN_TYPE_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      SPL_GL_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      CLR_DT DATE FORMAT 'YY/MM/DD' NOT NULL,

      DOC_NBR VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      ASGN_NBR VARCHAR(18) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

      FISCAL_YR INTEGER NOT NULL,

      ACCT_DOC_NBR VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      LINE_ITEM_NBR INTEGER NOT NULL,

      VNDR_ITEM_ST_CD VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      CRNCY_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      LCL_CRNCY_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      GBL_CRNCY_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      BSL_DT DATE FORMAT 'YY/MM/DD' NOT NULL,

      POST_DT DATE FORMAT 'YY/MM/DD' NOT NULL,

      EST_PYMT_DT DATE FORMAT 'YY/MM/DD' NOT NULL,

      ACTL_PYMT_DT DATE FORMAT 'YY/MM/DD' NOT NULL,

      ACCT_PAY_DOC_TYPE VARCHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      DRCR_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      PYMT_KEY_CD VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      PYMT_BLKD_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      REMIT_TO_SUPPL_ID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      PRD_ID INTEGER NOT NULL,

      CO_CTRY_CD CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      CO_REGION_CD VARCHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      NON_SAP_SRC_SYS_ID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      NON_SAP_SRC_SYS_PROC VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      PRIM_SIC_CD VARCHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      SUPPL_REF_DOC_NBR VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

      MFR_PART_NBR VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      LCL_EXCH_RATE_TYPE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      GBL_EXCH_RATE_TYPE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      SEC_GL_ACCT_NBR VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      RECNL_ACCT_NBR VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      SALES_PURCH_CD VARCHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      DOC_CRNCY_AMT DECIMAL(18,5) NOT NULL,

      PRIM_CASH_DISC_DAY_CNT INTEGER NOT NULL,

      PYMT_STAT VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      ORIG_SRC_SYS_ID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      ORIG_SRC_SYS_PROC_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      SRC_SYS_UPD_TS TIMESTAMP(0) NOT NULL,

      ORIG_CRNCY_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

      ORIG_LCL_CRNCY_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

      DW_ANOMALY_FLG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS 'N',

      DW_MOD_TS TIMESTAMP(0) NOT NULL,

      DW_JOB_ID VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS 'J_ICtoRZ_VndrItemF_LRF')

PRIMARY INDEX NUPI_VNDR_ITEM_F ( ACCT_DOC_NBR ,LINE_ITEM_NBR );

FastLoad SCRIPT:

.LOGMECH LDAP;

LOGON DB_IP_ADDRESS/dbc,dbc;

DATABASE rz_scratch;

DROP TABLE zack_test3_ERROR_log1;

DROP TABLE zack_test3_ERROR_log2;

BEGIN LOADING rz_scratch.zack_test3

ERRORFILES zack_test3_ERROR_log1,zack_test3_ERROR_log2

CHECKPOINT 1000; 

SET RECORD VARTEXT '~';

DEFINE 

CLIENT_ID (VARCHAR(150)),

SRC_SYS_ID (VARCHAR(150)),

CO_CD (VARCHAR(150)),

ACCT_NBR (VARCHAR(150)),

TXN_TYPE_IND (VARCHAR(150)),

SPL_GL_IND (VARCHAR(150)),

CLR_DT (VARCHAR(19)),

DOC_NBR (VARCHAR(150)),

ASGN_NBR (VARCHAR(150)),

FISCAL_YR (VARCHAR(150)),

ACCT_DOC_NBR (VARCHAR(150)),

LINE_ITEM_NBR (VARCHAR(150)),

VNDR_ITEM_ST_CD (VARCHAR(150)),

CRNCY_CD (VARCHAR(150)),

LCL_CRNCY_CD (VARCHAR(150)),

GBL_CRNCY_CD (VARCHAR(150)),

BSL_DT (VARCHAR(150)),

POST_DT (VARCHAR(150)),

EST_PYMT_DT (VARCHAR(150)),

ACTL_PYMT_DT (VARCHAR(150)),

ACCT_PAY_DOC_TYPE (VARCHAR(150)),

DRCR_IND (VARCHAR(150)),

PYMT_KEY_CD (VARCHAR(150)),

PYMT_BLKD_IND (VARCHAR(150)),

REMIT_TO_SUPPL_ID (VARCHAR(150)),

PRD_ID (VARCHAR(150)),

CO_CTRY_CD (VARCHAR(150)),

CO_REGION_CD (VARCHAR(150)),

NON_SAP_SRC_SYS_ID (VARCHAR(150)),

NON_SAP_SRC_SYS_PROC (VARCHAR(150)),

PRIM_SIC_CD (VARCHAR(150)),

SUPPL_REF_DOC_NBR (VARCHAR(150)),

MFR_PART_NBR (VARCHAR(150)),

LCL_EXCH_RATE_TYPE (VARCHAR(150)),

GBL_EXCH_RATE_TYPE (VARCHAR(150)),

SEC_GL_ACCT_NBR (VARCHAR(150)),

RECNL_ACCT_NBR (VARCHAR(150)),

SALES_PURCH_CD (VARCHAR(150)),

DOC_CRNCY_AMT (VARCHAR(150)),

PRIM_CASH_DISC_DAY_CNT (VARCHAR(150)),

PYMT_STAT (VARCHAR(150)),

ORIG_SRC_SYS_ID (VARCHAR(150)),

ORIG_SRC_SYS_PROC_NM (VARCHAR(150)),

SRC_SYS_UPD_TS (VARCHAR(150)),

ORIG_CRNCY_CD (VARCHAR(150)),

ORIG_LCL_CRNCY_CD (VARCHAR(150)),

DW_ANOMALY_FLG (VARCHAR(150)),

DW_MOD_TS (VARCHAR(150)),

DW_JOB_ID (VARCHAR(150))

FILE = DEV_data10.txt;

INSERT INTO zack_test3( 

:CLIENT_ID,

:SRC_SYS_ID,

:CO_CD,

:ACCT_NBR,

:TXN_TYPE_IND,

:SPL_GL_IND,

:CLR_DT(date, format 'yy-mm-dd'),

:DOC_NBR,

:ASGN_NBR,

:FISCAL_YR,

:ACCT_DOC_NBR,

:LINE_ITEM_NBR,

:VNDR_ITEM_ST_CD,

:CRNCY_CD,

:LCL_CRNCY_CD,

:GBL_CRNCY_CD,

:BSL_DT,

:POST_DT,

:EST_PYMT_DT,

:ACTL_PYMT_DT,

:ACCT_PAY_DOC_TYPE,

:DRCR_IND,

:PYMT_KEY_CD,

:PYMT_BLKD_IND,

:REMIT_TO_SUPPL_ID,

:PRD_ID,

:CO_CTRY_CD,

:CO_REGION_CD,

:NON_SAP_SRC_SYS_ID,

:NON_SAP_SRC_SYS_PROC,

:PRIM_SIC_CD,

:SUPPL_REF_DOC_NBR,

:MFR_PART_NBR,

:LCL_EXCH_RATE_TYPE,

:GBL_EXCH_RATE_TYPE,

:SEC_GL_ACCT_NBR,

:RECNL_ACCT_NBR,

:SALES_PURCH_CD,

:DOC_CRNCY_AMT,

:PRIM_CASH_DISC_DAY_CNT,

:PYMT_STAT,

:ORIG_SRC_SYS_ID,

:ORIG_SRC_SYS_PROC_NM,

:SRC_SYS_UPD_TS,

:ORIG_CRNCY_CD,

:ORIG_LCL_CRNCY_CD,

:DW_ANOMALY_FLG,

:DW_MOD_TS,

:DW_JOB_ID

); 

END LOADING; 

LOGOFF;
4 REPLIES
Teradata Employee

Re: FastLoad - Data Conversions

To which type of data conversions are you referring?

The FastLoad script is just loading data.

Teradata expects the Date/Time/Timestamp data to be in a very specific format.

If the incoming data does not match the format as described in the table creation, then the row will be placed in the error table.

You defined the column as: CLR_DT DATE FORMAT 'YY/MM/DD' NOT NULL

You also indicated the incoming data was in "delimited" (VARTEXT) format. Thus, the data in the data file must be in the format of YY/MM/DD.

-- SteveF

Re: FastLoad - Data Conversions

Hey Steve, 

I am exporting the data from the same table. Creating a new empy version of the same table and fastloading the data that was exported from the original table. I am working on a tool to create fastload scripts on the fly however I need to figure out these syntax errors along the way so I can account for them in my programming. 

The data was exported using Fast Export 10,000 rows with a delimiter of '~'. So unless I am missing some FastExport options in SQLAssistant the data should be a perfect match since it originally came from the same system.

If I remove the (date, format 'yy/mm/dd') I get the same error and rows are loaded to the error table like they are now. 

Any ideas to bypass this error?

Thanks

Re: FastLoad - Data Conversions

Sorry to double post but I believe I side stepped your question. The data in the exported file is DATE with FORMAT 'YY/MM/DD'. The table has this same type and format. However, as I understand RECORD VARTEXT requires all columns to be 1 of 3 types. In this case VARCHAR is what I set CLR_DT to in the 'Define' statement. So I need to cast the text from VARCHAR to DATA as a result even though the exported data is in fact in the correct date format.

--Zack

Teradata Employee

Re: FastLoad - Data Conversions

FastExport itself cannot export the data and write it out in delimited format. So, I guess you must be CASTing your SELECT statement.

Do you have to use SQLAssistant?

Have you tried TPT?

Can you provide me with a sample row from your data file?

-- SteveF