Problem to load data into teradata table by using tpt

Tools
Enthusiast

Problem to load data into teradata table by using tpt

hi,

I am trying to load data into my teradata staging table,
I defined ACTIONTIMESTAMP column as TIMESTAMP but my input file have data like '20101203151515' for ACTIONTIMESTAMP
column but I can load '2010 12 03 15 15 15'.
for that in tpt script I have used substr function just to add space between while inserting into the staging table.
I got an error with the code :3760:String not terminated before end of text.
Do I need to add anything else or is there any other way to aceive the same.
Can any one faced this issue,please share with me the solution if u have any.

Insert query in my tpt script:

APPLY
(
'INSERT INTO ICRM_STAGE_DEV.SAMPLE_STG(
MKTNG_PGM_NBR,
REGIS_CNSMR_ID_VAL,
CAMPAIGNID,
EMAILADDR,
VALID,
DATELASTMODIFIED,
UNSUB,
ACTIVE,
DEPLOYEDDATE,
TOTALCLICKTHROUGH,
HTMLCAPABLE,
ACTIONTIMESTAMP,
MESSAGESTATUS,
LINKURL
)
VALUES
(
:MKTNG_PGM_NBR,
:REGIS_CNSMR_ID_VAL,
:CAMPAIGNID,
:EMAILADDR,
:VALID,
:DATELASTMODIFIED,
:UNSUB,
:ACTIVE,
:DEPLOYEDDATE,
:TOTALCLICKTHROUGH,
:HTMLCAPABLE,
SUBSTR(:ACTIONIMESTAMP,1,4)||''||SUBSTR(:ACTIONTIMESTAMP,5,2)||''||SUBSTR(:ACTIONTIMESTAMP,7,2)||''||SUBSTR(:ACTIONTIMESTAMP,9,2)||''||SUBSTR(:ACTIONTIMESTAMP,11,2)||''||SUBSTR(:ACTIONTIMESTAMP,13,2),
:MESSAGESTATUS,
:LINKURL
);'
)
Tags (1)
5 REPLIES
Teradata Employee

Re: Problem to load data into teradata table by using tpt

Because the DML statement is already a quoted string, you need to use two single quotes in a row inside the SUBSTR to produce a single quote in the statement text. But inserting spaces doesn't leave you with a valid default format for timestamp. You could insert fixed delimiters to get standard format
YYYY-MM-DD HH:MI:SS
or just specify the format of your data
CAST(:ACTIONTIMESTAMP AS TIMESTAMP(0) FORMAT ''YYYYMMDDHHMISS'')
Enthusiast

Re: Problem to load data into teradata table by using tpt

Thanks for ur help fred.
Enthusiast

Re: Problem to load data into teradata table by using tpt

I tried the same but it is not working for me

CAST(:REPORTING_DATE AS DATE FORMAT ''MM/DD/YYYY''),

PRODUCER_OPERATOR: Total files processed: 0.
CONSUMER_OPERATOR: aborting due to the following error:
CONSUMER_OPERATOR: Expression not allowed in Fast Load Insert, column REPORTING_DATE.
Job step MAIN_STEP terminated (status 12)
Teradata Employee

Re: Problem to load data into teradata table by using tpt

You cannot use any expressions in the INSERT statement when using the Load operator.
That is a DBS restriction for the FastLoad protocol.
Try it using the Update operator (MultiLoad protocol). That protocol will allow expressions in DML statements.
-- SteveF
Enthusiast

Re: Problem to load data into teradata table by using tpt

I have similar problem using TPT in informatica. I am trying to do upsert. UPI is store_number and x_date. 

The source have mm/dd/yyyy . i can insert into table but update is not working.

I used update else insert option for upsert. Insterted all rows. I re-ran same file but no rows updating.

We have done upsert for other infromatica for non date key fields.

Please share better way do upsert for matching on date fields from file to table.

thanks

Vengal