TPT - LOAD TWO FIELDS IN ONE COLUMN (concatenate)

Tools & Utilities
Enthusiast

TPT - LOAD TWO FIELDS IN ONE COLUMN (concatenate)

Hi everyone,

I need to load a column that is a concatenation from two fields of a file. One field is a date other is time type. I would like to load both resulting in a timestamp field but i wanted to do it during the tpt load. Is it possible? How can i concatenate two fields in the insert statetment?

The ideia is shown in the script below , the fourth column is a the concatenation from the third and the second column. 

DEFINE JOB TSTHEX

DESCRIPTION 'TESTA CARACTERE DELIMITADOR PARA CARGA'

(

INCLUDE 'ze_tpt.sch'

  STEP Tst_Delm (

    APPLY 'INSERT INTO DHPVP2IT_W.TESTE_TPT_ZE

             (  COD_CHTA_PRSO_FUNL

               ,HOR_PRSO_FUNL

               ,DAT_PRSO_FUNL

               ,DAT_HOR_PRSO_FUNL )

            VALUES

             ( :COD_CHTA_PRSO_FUNL,

               :HOR_PRSO_FUNL (TIME, FORMAT''HH:MI:SS'') ,

               :DAT_PRSO_FUNL (DATE, FORMAT''YYYY-MM-DD'') ,

               :DAT_PRSO_FUNL||:HOR_PRSO_FUNL (TIMESTAMP(0), FORMAT''YYYY-MM-DDBHH:MI:SS'')  );' 

    TO OPERATOR ( $LOAD()[1] )

    SELECT * FROM OPERATOR ( $FILE_READER(TAB_TPT)[1] );

  );

);

3 REPLIES
Enthusiast

Re: TPT - LOAD TWO FIELDS IN ONE COLUMN (concatenate)

Does anyone have any insight on this?

Teradata Employee

Re: TPT - LOAD TWO FIELDS IN ONE COLUMN (concatenate)

It is possible. You need to define 2 schemas. One as the input schema for the DataConnector operator and one as the output schema for the DataConnector operator.

Something like:

DEFINE SCHEMA <name_1>

(

F1 CHAR(10),

F2 CHAR(10)

);

DEFINE SCHEMA <name_2>

(

COL1 CHAR(20)

);

Then in your DC operator definition:

DEFINE OPERATOR <some-name>

TYPE DATACONNECTOR PRODUCER

INPUT SCHEMA name_1

OUTPUT SCHEMA name_2

. . . . .

And then in the SELECT statement (in the APPLY-SELECT):

    APPLY 'INSERT INTO DHPVP2IT_W.TESTE_TPT_ZE

             (  COL1  )

            VALUES

             ( :COL1 )

    TO OPERATOR ( $LOAD()[1] )

    SELECT F1 || F2 AS COL1 FROM OPERATOR ( $FILE_READER[1] );

(I think something like this should work.)

-- SteveF
Enthusiast

Re: TPT - LOAD TWO FIELDS IN ONE COLUMN (concatenate)

Thank you very much Steve!