Selecting from file operator in TPT script, "missing SEMOCIL_" error

Tools
N/A

Selecting from file operator in TPT script, "missing SEMOCIL_" error

I am trying to load data into a table, that contains some date fields, format of the data in my sourcefile is out of my own control and date fields have to be changed when loaded to date fields in a teradata table. the source files is large (some 80+ gb) and my batch windows is not big enough to transform the file before loading into teradata.

da in input file could be something like:
4 30 2011 23:59:59;655609;12 31 2099 00:00:00;IP;NOK;0.0000000000000000
4 30 2011 23:59:59;655609;12 31 2099 00:00:00;MD;DKK;45000.0000000000000000

I am trying to alter a script from the wizard to meet my needs, but i get errors i do not understand.

The script

DEFINE JOB hy_cashflow_sequence
(
DEFINE OPERATOR TD_HY_CASHFLOW_SEQUENCE_WRITER
TYPE LOAD
SCHEMA *
ATTRIBUTES
(
VARCHAR UserName,
VARCHAR UserPassword,
VARCHAR LogTable,
VARCHAR TargetTable,
VARCHAR TdpId
);

DEFINE SCHEMA RP_HY_CASHFLOW_SEQUENCE
(
RESDATE VARCHAR(19),
CONTRACTID VARCHAR(50),
EVENTDATE VARCHAR(19),
EVENTKIND VARCHAR(5),
EVENTCURRENCY VARCHAR(3),
CFS VARCHAR(50)
);

DEFINE OPERATOR RP_HY_CASHFLOW_SEQUENCE_READER
TYPE DATACONNECTOR PRODUCER
SCHEMA RP_HY_CASHFLOW_SEQUENCE
ATTRIBUTES
(
VARCHAR FileName,
VARCHAR Format,
VARCHAR OpenMode,
VARCHAR DirectoryPath,
VARCHAR IndicatorMode,
VARCHAR TextDelimiter
);

APPLY
(
'INSERT INTO EDWWrk_U.TST_HY_CASHFLOW_SEQ (CONTRACTID,RESDATE,EVENTDATE,EVENTKIND,EVENTCURRENCY,CFS) VALUES (:CONTRACTID,:RESDATE,:EVENTDATE,:EVENTKIND,:EVENTCURRENCY,:CFS);'
)
TO OPERATOR
(
TD_HY_CASHFLOW_SEQUENCE_WRITER[1]

ATTRIBUTES
(
UserName = 'usrnm',
UserPassword = 'pwd',
LogTable = 'logtab',
TargetTable = 'ttab',
TdpId = 'tdpid'
)
)
SELECT RESDATE, CONTRACTID, EVENTDATE, EVENTKIND, EVENTCURRENCY, CFS FROM OPERATOR
(
RP_HY_CASHFLOW_SEQUENCE_READER[1]

ATTRIBUTES
(
FileName = 'CASHFLOW_SEQ.CSV',
Format = 'DELIMITED',
OpenMode = 'Read',
DirectoryPath = 'D:\testliq\tpt_test',
IndicatorMode = 'N',
TextDelimiter = ';'
)
);

);

works as it should:

C:\Program Files\NCR\Teradata Parallel Transporter\12.0\scripts>tbuild -f cashfl
ow_sequence1.txt
Teradata Parallel Transporter Version 12.00.00.00
Execution Plan generation started.
Execution Plan generation successfully completed.
Job log: C:\Program Files\NCR\Teradata Parallel Transporter\12.0\logs/u2230-30.
out
Job id is u2230-30, running on BAS-B2-X-NA-02
Teradata Parallel Transporter DataConnector Version 12.00.00.00
RP_HY_CASHFLOW_SEQUENCE_READER: DataConnector Producer operator Instances: 1
RP_HY_CASHFLOW_SEQUENCE_READER: Operator instance 1 processing file 'HY_CASHFLOW
_SEQUENCE_test.BCP'.
Teradata Parallel Transporter Load Operator Version 12.00.00.00
TD_HY_CASHFLOW_SEQUENCE_WRITER: private log not specified
TD_HY_CASHFLOW_SEQUENCE_WRITER: connecting sessions
TD_HY_CASHFLOW_SEQUENCE_WRITER: preparing target table
TD_HY_CASHFLOW_SEQUENCE_WRITER: entering Acquisition Phase
TD_HY_CASHFLOW_SEQUENCE_WRITER: entering Application Phase
TD_HY_CASHFLOW_SEQUENCE_WRITER: disconnecting sessions
RP_HY_CASHFLOW_SEQUENCE_READER: Total files processed: 1.
Job step MAIN_STEP completed successfully
Job u2230 completed successfully

But when i want to transform resdate and eventdate to dates or ansidate formatet varchar replacing:
SELECT RESDATE, CONTRACTID, EVENTDATE, EVENTKIND, EVENTCURRENCY, CFS FROM
with:
SELECT CAST(SUBSTR(RESDATE,1,10) AS DATE FORMAT 'MM-DD-YYYY'), CONTRACTID, CAST(SUBSTR(EVENTDATE,1,10) AS DATE FORMAT 'MM-DD-YYYY'), EVENTKIND, EVENTCURRENCY, CFS FROM OPERATOR
or:
SELECT
CONTRACTID,
CASE SUBSTR(RESDATE,1,1)
WHEN ' ' THEN SUBSTR(RESDATE,7,4)||'-'||SUBSTR(RESDATE,4,2)||'-0'||SUBSTR(RESDATE,2,1)
ELSE SUBSTR(RESDATE,7,4)||'-'||SUBSTR(RESDATE,4,2)||'-'||SUBSTR(RESDATE,1,2)
END AS RESDATE,
CASE SUBSTR(EVENTDATE,1,1)
WHEN ' ' THEN SUBSTR(EVENTDATE,7,4)||'-'||SUBSTR(EVENTDATE,4,2)||'-0'||SUBSTR(EVENTDATE,2,1)
ELSE SUBSTR(EVENTDATE,7,4)||'-'||SUBSTR(EVENTDATE,4,2)||'-'||SUBSTR(EVENTDATE,1,2)
END AS EVENTDATE,
EVENTKIND,
EVENTCURRENCY,
CFS FROM OPERATOR

I get this error from tbuild:

C:\Program Files\NCR\Teradata Parallel Transporter\12.0\scripts>tbuild -f cashfl
ow_sequence1.txt
Teradata Parallel Transporter Version 12.00.00.00
line 54: syntax error at "SELECT" missing SEMICOL_ in Rule: Job Definition Body
Compilation failed due to errors. Execution Plan was not generated.
Job script compilation failed

Coud someone explane what i am doing wrong?

Kindly regards Ken Madsen
Tags (2)
1 REPLY
Teradata Employee

Re: Selecting from file operator in TPT script, "missing SEMOCIL_" error

The SELECT statement in the APPLY-SELECT clause of the TPT script language is not a SELECT statement that is sent to Teradata, and does not support all of the SQL syntax.

The TPT script language does not support the SUBSTR() function.

Please refer to the TPT documentation for the proper use of the APPLY-SELECT statement and the syntax that is supported.
-- SteveF