All Forums Tools
Rahul_Verma 4 posts Joined 06/14
02 Jun 2014
Date value mismatch btw delimited file and source table

Hello,
 
I am using TPT to export data from a Teradata table to delimited file.
Below is the script:
--------------------------------------

DEFINE JOB EXPORT_DELIMITED_FILE 

DESCRIPTION 'Export rows from a Teradata table to a delimited file' 

DEFINE SCHEMA PRODUCT_SOURCE_SCHEMA 

FLGHT_ID VARCHAR(10),

FLGHT_TRCKNG_NUM VARCHAR(50),

VLD_SRLZD_ARCRFT_ID VARCHAR(10),

VLD_FLGHT_FROM_LOC_ID VARCHAR(6),

VLD_FLGHT_TO_LOC_ID VARCHAR(6),

FLGHT_DTTM VARCHAR(20),

SRC_FLGHT_FROM_LOC_ID VARCHAR(50),

FLGHT_FROM_VLDTN_DT VARCHAR(20),

SRC_FLGHT_TO_LOC_ID VARCHAR(50),

FLGHT_TO_VLDTN_DT VARCHAR(20),

SRC_TL_NUM VARCHAR(50),

SRLZD_ARCRFT_VLDTN_DT VARCHAR(20),

FALLOUT_IND VARCHAR(3),

JOB_CONTROL_ID VARCHAR(10)

); 

DEFINE OPERATOR SQL_SELECTOR 

TYPE SELECTOR 

SCHEMA PRODUCT_SOURCE_SCHEMA 

ATTRIBUTES ( 

VARCHAR PrivateLogName = 'XXXX/Flight_TPT.log', 

VARCHAR TdpId = 'XXXX', 

VARCHAR UserName = 'XXXX', 

VARCHAR UserPassword = 'XXXX', 

VARCHAR SelectStmt = 'SELECT TRIM(FLGHT_ID)(VARCHAR(10)) ,COALESCE(FLGHT_TRCKNG_NUM,'''')(VARCHAR(50)) ,COALESCE(TRIM(VLD_SRLZD_ARCRFT_ID),'''')(VARCHAR(10)) ,COALESCE(VLD_FLGHT_FROM_LOC_ID,'''')(VARCHAR(6)) ,COALESCE(VLD_FLGHT_TO_LOC_ID,'''')(VARCHAR(6)) ,FLGHT_DTTM(char(19))(VARCHAR(19)) ,COALESCE(SRC_FLGHT_FROM_LOC_ID,'''')(VARCHAR(6)) ,FLGHT_FROM_VLDTN_DT(VARCHAR(19)) ,COALESCE(SRC_FLGHT_TO_LOC_ID,'''')(VARCHAR(6)) ,FLGHT_TO_VLDTN_DT(char(19))(VARCHAR(19)) ,COALESCE(SRC_TL_NUM,'''')(VARCHAR(20)) ,SRLZD_ARCRFT_VLDTN_DT(char(19))(VARCHAR(19)) ,COALESCE(FALLOUT_IND,'''')(varchar(3)) ,COALESCE(TRIM(JOB_CONTROL_ID),'''')(VARCHAR(10)) FROM XXXX.FLIGHT;', 

VARCHAR ReportModeOn 

);

DEFINE OPERATOR FILE_WRITER 

TYPE DATACONNECTOR CONSUMER 

SCHEMA * 

ATTRIBUTES 

VARCHAR PrivateLogName = 'XXXX/XXXX/dataconnector_log', 

VARCHAR DirectoryPath = '/XXXX/XXXX/', 

VARCHAR FileName = 'XXXX', 

VARCHAR Format = 'Delimited', 

VARCHAR OpenMode = 'Write', 

VARCHAR TextDelimiter = ',' 

); 

APPLY TO OPERATOR (FILE_WRITER) 

SELECT * FROM OPERATOR (SQL_SELECTOR); );

------------------------------

My issue is for the date column export where the data value extracted is not a date value at all.

Source value: "2012-10-23 10:01:51"

Value in file: "1:51-10-23 10:01:51"

 

I have 3 date clolumn in the query and the issue varies from one run to other. I hav not got a single stagnent issue with a date value, it varies on every run (row and column). with use of where clause to limit the number of records, the data looks fine. 

But for the xtract of full table, in this requiremnet of 22 Gigs, I am unable to get desired data set in the file.

 

Appreciate your suggestion.

 

Thanks,

Rahul Verma

Rahul
feinholz 1234 posts Joined 05/08
03 Jun 2014

When using the Selector operator, you need to enable the ReportModeOn attribute.
You have assigned a value to that attribute (default is "no").
 
What version of TPT are you using?
 
I believe in 14.10 we allow you to use the Export operator and have the DC operator convert the binary data to delimited format.

--SteveF

Rahul_Verma 4 posts Joined 06/14
03 Jun 2014

Thanks.
Can you tell me the syntax for ReportModeOn.
I tried -- VARCHAR ReportModeOn Yes
but it failed
 

Rahul

feinholz 1234 posts Joined 05/08
03 Jun 2014

Try
VARCHAR ReportModeOn = 'yes'
 

--SteveF

Rahul_Verma 4 posts Joined 06/14
04 Jun 2014

I am still getting the issue.
No improvement with the reportmode set on.
0 05:05:00 05:05:00 is the data timestamp value from the export file.
 
Please help!
 
Thanks,
Rahul 

Rahul

feinholz 1234 posts Joined 05/08
04 Jun 2014

Which column is failing?
What is the exact error you are getting from TPT (or is the job completing successfully, but the data is incorrect)?
What version of TPT are you using?
What platform?
Since you are using the Selector operator and report mode, I am not sure why you need all of the Coalesce and Trim operations.
The report mode will provide the data to the Selector operator in character format.
Thus, try changing your Select statement to "Select * from <tablename>;"

--SteveF

Rahul_Verma 4 posts Joined 06/14
04 Jun 2014

Hi Steve,
 
SRLZD_ARCRFT_VLDTN_DT column is timestamp field and has the issue. Its not stable with it, and there is data issues on for Timestamp other fields as well.
We have 13.1 version of TPT.
The script succeeds with the data issue. There is no error from tbuild.
 
I will try Select * from tablename and repond.
Thanks

Rahul

You must sign in to leave a comment.