All Forums Tools
kusumayella 16 posts Joined 08/10
03 Dec 2010
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:
Fred 1096 posts Joined 08/04
06 Dec 2010

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'')

kusumayella 16 posts Joined 08/10
23 Dec 2010

Thanks for ur help fred.

narang.mohit 13 posts Joined 07/09
22 Mar 2011

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)

feinholz 1234 posts Joined 05/08
31 Mar 2011

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

Capgemini 5 posts Joined 10/04
21 Oct 2014

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
 
 

Vengal Jalagam
Sr. Teradata/ETL consultant

You must sign in to leave a comment.