All Forums Tools
EUsha 25 posts Joined 03/14
04 May 2016
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.

EUsha
feinholz 1234 posts Joined 05/08
04 May 2016

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

You must sign in to leave a comment.