Any assistance would be welcomed. I cannot seem to put the Interval in a format that Teradata likes. The column data looks like "00 02:00:00.000000". We are extracting it from ORacle and loading to Teradata version 15.10. I am able to load most other data types no problem, but I cannot find the format needed to make this happy.
Problem column is "DELAY_INTERVAL INTERVAL DAY(2) TO SECOND(6)" below.
Table is defined as such:
CREATE MULTISET TABLE DB1.PROBLEM_TABLE (
STREAM_TAG VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
DELAY_INTERVAL INTERVAL DAY(2) TO SECOND(6),
CODE_TAG VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC)
NO PRIMARY INDEX ;
Data row looks like (delimitter is pipe here): TAGDATAYADAYADA|+00 02:00:00.000000|MORE TAGDATAYADAYADA
Both BTEQ and TPT Fail with:
TPT: (all going to Error table with Invalid DELAY_INTERVAL column - Errorcode 7452 on every row.
$LOAD: Total Rows Sent To RDBMS: 17
$LOAD: Total Rows Applied: 0
$LOAD: Total Rows in Error Table 1: 17
$LOAD: Total Rows in Error Table 2: 0
$LOAD: Total Duplicate Rows: 0
*** Warning: Out of data.
*** Failure 7452 Invalid interval.
Statement# 1, Info =0
*** Total elapsed time was 1 second.
It actually loads if data is formatted without the plus (+). Negative works. I am not sure why, but looks like I will have to replace the plus with null when I pull from Oracle. So, 00 02:00:00.000000 or 55 02:00:00.000000 or -01 02:00:00.000000 all work, but +00 02:00:00.000000 does not.
Is it possible to join data from a flat file with table in TPT? I am having around 1K records in flat file and I need to look up a column in the file with a table and load it into another table? Kindly let me know if there is any possible way to handle it in update/Load operator. Thanks!
I just reformatted and replaced the plus sign when pulling data out of ORacle as such:
,REPLACE(TO_CHAR(CAST(MAX_HOURS as interval day( 4) to second( 6))),'+',null)
OR from DYNAMIC SQL to generate above SQL for this data type:
WHEN (c.ColumnType = 'DS') THEN 'REPLACE(TO_CHAR(CAST('||ColumnNameNew||' as interval day('||DecimalTotalDigits||') to second('||DecimalFractionalDigits||'))),''+'',null)'
FROM "DBC"."ColumnsV" as c .....
It seems to work, but doesn't seem to be the optimal solution.
I am not sure why users fail to provide us with the version of TPT they are using when reporting issues, but since you are loading data into Teradata 15.10, I am assuming that the version of TPT is close to 15.10.
TPT has a feature whereby it can reformat Data/Time/Timestamp data into any format desired.
The features requires the use of keywords in the schema (FORMATIN and FORMATOUT), and using the keyword VARDATE as the data type for the column.
It is possible that you can use this feature to have TPT strip the "+" sign from the string.
Take a look at the TPT documentation for how to use this feature.
Let me know if it helps.