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'')
Thanks for ur help fred.
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)
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.
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
);'
)