Error while loading DATE column using TPT

Tools & Utilities
Enthusiast

Error while loading DATE column using TPT

Hi,

I am new to TPT (Version 14.10.00.04). I have a csv file from which i am trying to load data using TPT. Data in csv file has date defined in 'DD/MM/YYYY' format. The table has a DATE column with format defined as 'DD/MM/YYYY' and i am getting error while loading the data from csv file onto this Teradata table. Error is - "LOAD_OPERATOR: TPT10508: RDBMS error 3618: Expression not allowed in Fast Load Insert, column PTB_DATEVALUE"

Below is the script that i am using and is causing an error:

DEFINE JOB  TD_LD(

 DEFINE OPERATOR LOAD_OPERATOR

        TYPE LOAD

        SCHEMA *

        ATTRIBUTES

        (

        VARCHAR PrivateLogName ,VARCHAR TdpId, VARCHAR UserName ,

        VARCHAR UserPassword ,VARCHAR TargetTable , VARCHAR LogTable ,

        VARCHAR WorkingDatabase ,VARCHAR ErrorTable1 , VARCHAR ErrorTable2,

        INTEGER  MaxSessions

        );

 DEFINE OPERATOR DDL_OPERATOR()

        TYPE DDL

        ATTRIBUTES

        (

                VARCHAR TdpId = '<IP Address>',

                VARCHAR UserName = '<userid>',

                VARCHAR UserPassword = '<password>',

                VARCHAR WorkingDatabase = 'STGDB',

                VARCHAR ARRAY ErrorList = ['3807','3803','5980']

        );

 DEFINE SCHEMA TABLE1_SCH

        (

       TESTCASENUM VARCHAR(50) ,

      PTB VARCHAR(50) ,

      CTB VARCHAR(50) ,

      PTB_DATEVALUE VARCHAR(50) ,

      CTB_DATEVALUE VARCHAR(50)

                );

       DEFINE OPERATOR FILE_READER

        TYPE DATACONNECTOR PRODUCER

        SCHEMA TABLE1_SCH

        ATTRIBUTES

        (

        VARCHAR DirectoryPath ,VARCHAR FileName ,VARCHAR Format,

        VARCHAR OpenMode ,VARCHAR TextDelimiter ,VARCHAR AcceptMissingColumns

        ,VARCHAR AcceptExcessColumns

       );

STEP SETUP_TABLES

 (

  APPLY

  ('

CREATE MULTISET TABLE STGDB.TABLE1 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

             TESTCASENUM DECIMAL(8,2),

      PTB VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC,

      CTB VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC,

      PTB_DATEVALUE DATE FORMAT 'DD/MM/YYYY',

      CTB_DATEVALUE DATE FORMAT 'DD/MM/YYYY'

)

PRIMARY INDEX ( TESTCASENUM );

')

TO OPERATOR (DDL_OPERATOR());

);

STEP LOAD_TABLE

 (

 APPLY

   ('INSERT INTO STGDB.TABLE1

   (

 TESTCASENUM ,

      PTB  ,

      CTB  ,

       PTB_DATEVALUE  ,

      CTB_DATEVALUE  

 )

    VALUES

    (

      :TESTCASENUM ,

      :PTB  ,

      :CTB  ,

      CAST(:PTB_DATEVALUE  AS DATE FORMAT ''DD/MM/YYYY''),

      CAST(:CTB_DATEVALUE  AS DATE FORMAT ''DD/MM/YYYY'')

);

')

  TO OPERATOR

 (

  LOAD_OPERATOR[4]

  ATTRIBUTES

  (

    PrivateLogName = 'table1_log',

    TdpId = '<IP Address>,

    UserName = '<userid>',

    UserPassword = '<password>',

    TargetTable = 'stgdb.table1',

    LogTable = 'stgdb.table1_log',

    WorkingDatabase = 'stgdb',

    ErrorTable1 = 'stgdb.table1_E1',

    ErrorTable2 = 'stgdb.table1_E2',

    MaxSessions = 30

)

 )

 SELECT

 TESTCASENUM ,

      PTB,

      CTB  ,

      PTB_DATEVALUE  ,

      CTB_DATEVALUE  

FROM OPERATOR

 (

  FILE_READER[4]

  ATTRIBUTES

  (

    DirectoryPath = '/sample/',

    FileName = 'test.csv',

    Format = 'Delimited',

    OpenMode = 'Read',

    TextDelimiter = ',',

         AcceptMissingColumns = 'Y',

        AcceptExcessColumns = 'Y'

)

 );

);

);

Please help in understanding where the issue is. Need your valuable suggestions to resolve the issue.

1 REPLY
Teradata Employee

Re: Error while loading DATE column using TPT

The FastLoad protocol (which is used by the TPT Load operator) is very restrictive in the syntax of the INSERT statement.

The DBS is complaining about the CAST syntax.

It is not allowed.

I am not sure why you need it. If the column is defined with a format of DD/MM/YYYY and the data is in the same format, then a CAST should not be necessary.

-- SteveF