TPT: using VARDATE and empty string

Tools & Utilities
Enthusiast

TPT: using VARDATE and empty string

Hello,

when I used VARDATE  in TPT transformation everythink worked perfectly until the DATE column was filled in the source extract.

First row containing empty date field causes error:

DATACON_O2EV: TPT10672 Error: Data contains a separator character which is unmatched by the formatting string.

DATACON_O2EV: TPT19003 TPT Exit code set to 12.

Is it correct?

How can I convert DATE with empty / undefined fields in TPT using VARDATE functionality?

Thanks

         Tom

Tags (2)
7 REPLIES
Teradata Employee

Re: TPT: using VARDATE and empty string

The idea behind VARDATE is to convert data.

An empty field contains no data to convert.

The VARDATE feature is not intended to insert some type of default value.

-- SteveF
Enthusiast

Re: TPT: using VARDATE and empty string

Empty fields represent unknown, undefined (null) values.

It is strange that the transformation of the DATE (using VARDATE) is possible only on not null fields.

Are you sure this is the correct behavior?

Teradata Employee

Re: TPT: using VARDATE and empty string

Yes, I am sure.

I do not think it is strange. You are not providing anything to convert.

The feature converts data from one format to another.

What is VARDATE supposed to convert from/to?

If you want to populate a column in Teradata with a pre-defined value, then use Teradata's "default" feature in the column definition.

-- SteveF
Enthusiast

Re: TPT: using VARDATE and empty string

OK, I wrote transformation, but I mean formating.

I have DATA CONNECTOR (PRODUCER) defined as delimited file with quoteddata=Y option.

NullColumns = 'Y' - zero length columns are to be NULLed

If I have in schema definition VARCHAR(10) data type, zero lenght date are NULLed and other value are propagated 1:1.

If I use VARDATE(10) FORMATIN('DD.MM.YYYY') FORMATOUT('YYYY-MM-DD') data type in schema definition, DATE values are reformated, but zero length column (NULLed) caused error.

It there ability in TPT how to propagate NULL (empty) values and use VARDATE formating functionality for other values?

Schema specific VARDATE column data type is perfect and I plan to use it. 

But without the ability to handle null values ​​is to use VARDATE very limited for us.

Teradata Employee

Re: TPT: using VARDATE and empty string

VARCHAR and VARDATE are 2 different things.

Different code paths.

Different functionality.

VARDATE is specific to Dates/Times/Timestamps. And once you use the formatting options, it expects certain information.

We do not mix functionality of empty data with an attempt to convert data.

As I wrote before, in order to use the FORMATIN/FORMATOUT features, the data has to contain content. More specifically, it has to contain information according to the string you provided in the FORMATIN option.

I understand what you would like, and we can take it into consideration for a future release.

BTW, "zero lengh string" is not the same as NULL.

A 0-length string is just a string that is empty.

NULL is a characteristic of a column.

-- SteveF
Enthusiast

Re: TPT: using VARDATE and empty string

It will be perfect if there will ability to pass through undefined value in future release.

I think it will useful for all users.

Teradata Employee

Re: TPT: using VARDATE and empty string

We will put it on our list of enhancements for the future.

Thanks!

-- SteveF