Date value mismatch btw delimited file and source table

Tools

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

Tags (1)
6 REPLIES
Teradata Employee

Re: Date value mismatch btw delimited file and source table

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

Re: Date value mismatch btw delimited file and source table

Thanks.

Can you tell me the syntax for ReportModeOn.

I tried -- VARCHAR ReportModeOn Yes

but it failed

Teradata Employee

Re: Date value mismatch btw delimited file and source table

Try

VARCHAR ReportModeOn = 'yes'

-- SteveF

Re: Date value mismatch btw delimited file and source table

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 

Teradata Employee

Re: Date value mismatch btw delimited file and source table

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

Re: Date value mismatch btw delimited file and source table

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