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.
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.
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?
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.
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.
VARCHAR and VARDATE are 2 different things.
Different code paths.
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.