TPT casting w/ format issues

Tools
Enthusiast

TPT casting w/ format issues

Hi,
Is it possible to format a float when casting in a TPT script? I've tried several iterations using casts w/ formats in my TPT select statement and get the below error. I'm trying to format floats so don't land in scientific notation format in my text file. Thanks!

from TPT select (works find in queryman):
CAST( ( X1 (FORMAT '-(11)9.9999999')) AS CHAR(20) )

This is the TPT error that is returned (error goes away if I remove the format from the cast).

Error:
Teradata Parallel Transporter Version 13.10.00.02
TPT_INFRA: Syntax error at or near line 235 of Job Script File './ddm_mdl2.tpt':
TPT_INFRA: TPT03589: in SELECT part. Check with the syntax below.

APPLY {dml-list} TO OPERATOR
(operator-name {[n]} {ATTR (attribute-list)})
SELECT {(column-list) | *} FROM OPERATOR
(operator-name {[m]} {ATTR (attribute-list)});

Comma separated multiple APPLY statements can be specified.
UNION ALL separated multiple SELECT statements can be specified.

Compilation failed due to errors. Execution Plan was not generated.
Job script compilation failed.
Job terminated with status 8.
4 REPLIES
Teradata Employee

Re: TPT casting w/ format issues

Try doing the CAST in the SELECT statement for the producer operator (i.e. Selector or Export operator) and not the SELECT in the TPT APPLY-SELECT statement .
-- SteveF
Enthusiast

Re: TPT casting w/ format issues

I have a similar question. I have a TPT that imports from a flat file. It works fine but I import the data as varchar, followed by a BTEQ script to cast a few of the fields into correct datatype into a second table. I'd like to remove this 2nd step and apply the cast on the import itelf. I'm not sure how to do this.  Below is an excerpt from my TPT script.  The REC_CNT field is integer data and actually loads successfully as is. The RUN_DATE field needs to be cast as timestamp, using  CAST (RUN_DATE AS TIMESTAMP(0) FORMAT 'YYYYMMDDbHH:MI:SS') but I don't know where to put this?

thanks

STEP DROP_CREATE_TABLE

(

APPLY

('drop table mydb.MANIFEST_TMP;'),

('drop table mydb.MANIFEST_TMP_E1;'),

('drop table mydb.MANIFEST_TMP_E2;'),

('drop table mydb.MANIFEST_TMP_LT;'),

('CREATE MULTISET TABLE mydb.MANIFEST_TMP

(

FILE_NAME varchar(50),

DATA_SOURCE varchar(30),

RUN_DATE VARCHAR(30),

VERS varchar(20),

REC_CNT int,

HASH_CONTROL varchar(50),

COMMENT_TEXT varchar(50),

DELTA varchar(10)

) PRIMARY INDEX(FILE_NAME);'

)

      TO OPERATOR (DDL_OPERATOR_1 [1] );

);

STEP LOAD_DATA

(

APPLY

('INSERT INTO mydb.MANIFEST_TMP

VALUES 

(

:FILE_NAME,

:DATA_SOURCE,

:RUN_DATE,

:VERS,

:REC_CNT,

:HASH_CONTROL,

:COMMENT_TEXT,

:DELTA

);

')

TO OPERATOR (LOAD_OPERATOR () [1] )

SELECT * FROM OPERATOR (DATACON() [1] );

);

Enthusiast

Re: TPT casting w/ format issues

Create the target table with Timestamp as data type, instead of VARCHARs. In the TPT schema definition, define all varchars which match the data in the flat file. You ca



Something like this:

DEFINE SCHEMA SCHEMA1

(

FILE_NAME varchar(50),

DATA_SOURCE varchar(30),

RUN_DATE VARCHAR(30),

VERS varchar(20),

REC_CNT VARCHAR(10),

HASH_CONTROL varchar(50),

COMMENT_TEXT varchar(50),

DELTA varchar(10)

);

 

Target table where the data will be loaded, will have the actual datatypes like this:

CREATE TABLE TABLE1(

FILE_NAME varchar(50),

DATA_SOURCE varchar(30),

RUN_DATE TIMESTAMP(0) FORMAT 'YYYYMMDDbHH:MI:SS',

VERS varchar(20),

REC_CNT int,

HASH_CONTROL varchar(50),

COMMENT_TEXT varchar(50),

DELTA varchar(10)

);

 

Note: This is untested, but should work. 

If my answer is unclear, let me know. Please post the entire script (TPT, bteq etc) in future so that we can clearly understand what you are trying to do. 

Fan

Re: TPT casting w/ format issues

having problem loading the TEXT file where footer is also there, is it poosible to use two different schema ?

Data length implied by Data Schema (246) is not the same as record length (159)