All Forums Tools
dreis 6 posts Joined 06/09
14 Jul 2014
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] );
  );
);

dreis 6 posts Joined 06/09
16 Jul 2014

Does anyone have any insight on this?

feinholz 1234 posts Joined 05/08
16 Jul 2014

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

dreis 6 posts Joined 06/09
16 Jul 2014

Thank you very much Steve!

You must sign in to leave a comment.