Loading a timestamp column with Fastload

Tools & Utilities
Enthusiast

Loading a timestamp column with Fastload

Fastload is rejecting my attempts to load a timestamp column with the Invalid Timestamp Error 6760. I am attempt to load data like this:

20010429 04:07:55.940000

with this statement in the DDL part of my fastload

, CHN_TS TIMESTAMP(6) FORMAT 'YYYYMMDDBHH:MI:SS.S(F)'

with this defining my flat file field

,CHN_TS (varchar(255))

When I attempt a cast using the data there is no problem

e.g. select cast ('20010429 04:07:55.940000' as timestamp(6)
format 'YYYYMMDDBHH:MI:SS.S(F)')

Your advice on how to overcome this would be very gratefully received. The flat file has come from Oracle in the first place and I can modify the format of the soure data. I am running fastload in Unix.

Thanks,

Susan
7 REPLIES
Junior Contributor

Re: Loading a timestamp column with Fastload

Hi Susan,
did you try an explicit typecast in your INSERT?

:CHN_TS (TIMESTAMP(6), FORMAT 'YYYYMMDDBHH:MI:SS.S(6)')

Dieter
Enthusiast

Re: Loading a timestamp column with Fastload

No, I had not. Vielen dank, that worked wonderfully.

Thank you very much,

Susan
Highlighted

Re: Loading a timestamp column with Fastload

Hi,

I facing a problem with TPT FASTLOAD. I have a column in my Teradata table with datatype TIMESTAMP(6). When I am trying to load data from a binary flat file (the binary flat file is created using TPT FASTEXPORT) into my TD table, I am getting some problems.

My TD table is :

create table edw_base.test_binary_load (col1  CHAR(20) , col2 TIMESTAMP(6));

Now, the problems I am facing with FASTLOAD are:

1.     When I had tried-

:col2(timestamp(6),format 'yyyy-mm-ddBhh:mi:ss.SSSSSS')

ERROR:

       TPT_INFRA: At "yyyy" missing RPAREN_ in Rule: DML Statement List

TPT_INFRA: TPT02932: Error: Invalid token near line 33 (text was ':')

TPT_INFRA: TPT02932: Error: Invalid token near line 33 (text was ':')

Compilation failed due to errors. Execution Plan was not generated.

Job script compilation failed.

2.     When I had tried-

:col2(timestamp(6),format "yyyy-mm-ddBhh:mi:ss.SSSSSS")

ERROR:

Syntax error, expected something like a string or a Unicode character literal between the 'format' keyword and the word 'yyyy-mm-ddBhh:mi:ss.SSSSSS'.

3.      Another problem with NULL value. The column “col2” takes NULL also. Now, when I am trying to load data from the binary flat file into TD table, I am getting 6760 error code.

Please tell me how to solve these problems . 

Teradata Employee

Re: Loading a timestamp column with Fastload

#1 is the corect solution (from a Teradata SQL point of view).

But you are embedding single quotes within single quotes, and so you need to escape the single quotes for the format statement.

format '''yyyy-mm-ddBhh:mi:ss.SSSSSS''')

-- SteveF
Enthusiast

Re: Loading a timestamp column with Fastload

Hi, 

I have very similar issue. I am trying to Load Date column from oracle to Teradata and the source data is "10/9/2014 00:00:00".

When I am trying to do the implicit cast in the Apply section in TPT, "date" (timestamp(0), format 'MM/DD/YYYYBHH:MI:SS'), i was thrown an error 

TPT_INFRA: At "MM" missing RPAREN_ in Rule: DML Statement List

TPT_INFRA: TPT02932: Error: Invalid token near line 114 (text was ':')

TPT_INFRA: TPT04017: Exception "Invalid token" caught during job script file par

sing/compilation.

could you suggest?

Teradata Employee

Re: Loading a timestamp column with Fastload

That just looks like a scripting issue.

Looks like you may not have escaped single-quotes correctly.

If you post the entire script (or at least the complete section that results in this error) I would be able to help you out.

-- SteveF
Enthusiast

Re: Loading a timestamp column with Fastload

Thanks feinholz. I am able to load the data without having any issues. As you rightly said, it is due to the escape sequence.