0

I am facing an issue with the fastload where my Timestamp fields are getting rejected to error table. Below is the value of timestamp(6) field in my flat file. 23-06-2016 11:51:21.000000 23-06-2016 11:51:21.000000

Below is my code:

SET RECORD VARTEXT "¡";

DEFINE

TRANSACTION_SOURCE_TYPE_ID (VARCHAR(54))
,TRANSACTION_SOURCE_TYPE_CODE (VARCHAR(20))
,TRANSACTION_SOURCE_TYPE_DESC (VARCHAR(110))
,EFFECTIVE_START_DATE (VARCHAR(54))
,EFFECTIVE_END_DATE (VARCHAR(54))
,COUNTRY_CODE (VARCHAR(13))
,SOURCE_SYSTEM_ID (VARCHAR(54))
,DW_LOAD_TIMESTAMP (VARCHAR(76))
,DW_UPD_LOAD_TIMESTAMP (VARCHAR(76))
,FORCE_SKEW_KEY (VARCHAR(51))


FILE=?INPUT_FILE;
SHOW;

INSERT INTO ?DWSBKPDB.TRANSACTION_TMP
(
TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_SOURCE_TYPE_CODE
,TRANSACTION_SOURCE_TYPE_DESC
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,COUNTRY_CODE
,SOURCE_SYSTEM_ID
,DW_LOAD_TIMESTAMP
,DW_UPD_LOAD_TIMESTAMP
,FORCE_SKEW_KEY
)
VALUES
(
:TRANSACTION_SOURCE_TYPE_ID
,:TRANSACTION_SOURCE_TYPE_CODE
,:TRANSACTION_SOURCE_TYPE_DESC
,:EFFECTIVE_START_DATE (DATE, FORMAT 'YYYY-MM-DD')
,:EFFECTIVE_END_DATE (DATE, FORMAT 'YYYY-MM-DD')
,:COUNTRY_CODE
,:SOURCE_SYSTEM_ID
,:DW_LOAD_TIMESTAMP (TIMESTAMP, FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)')
,:DW_UPD_LOAD_TIMESTAMP (TIMESTAMP, FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)')
,:FORCE_SKEW_KEY
);

DW_LOAD_TIMESTAMP is creating problem here. Any idea as to why this is happening.

Regards, Amit

user3901666
  • 399
  • 11
  • 29

1 Answers1

1

Your data obviously doesn't match the FORMAT.

:DW_LOAD_TIMESTAMP(TIMESTAMP, FORMAT 'DD-MM-YYYYBHH:MI:SS.S(6)')
dnoeth
  • 59,503
  • 4
  • 39
  • 56