DSXchange

Source is sending the time in Decimal (Decimal 7,0) format. We are reading the data using the Schema file (Input = ASCII) and trying to load into Teradata table. We would like load the time field with proper time format.

Field defination in Schema:
XYZ_TIME:nullable Time {time_format='%hh%nn%ss'}

Input recs:
XYZ_Time:
223,644
1,349
72,506
2,123
60,517
337

expected Output data from the target table:

22:36:44 - fine
00:13:49 - rejected
07:25:06 - rejected
00:21:23 - rejected
06:05:17 - rejected
00:03:37 - rejected

There are no issues if the time comes in 6 digits (hh:nn:ss). But schema is rejecting the record if hh or part of nn is missing in the input file.
Is there any way to handle this in schema file. Due to some other architectural / techinal constraints I dont have any other option other than fixing the schema file to fix this.

Any suggesstion?

Quick question: Are you keeping the type as decimal in your job?

Job is using the schema file to load the data.
In the schema the field is defined as TIME in the below format.
XYZ_TIME:nullable Time {time_format='%hh%nn%ss'}

Those "double letters" in your format tell it the format is fixed, i.e. always two digits. Start by trying single letters. I don't think it will solve all your problems but give it a shot and let us know.

The time format tag is missing the colons that were shown in the expected output example.

That format is for the incoming string, not the expected output so should not include the colons.

I am a little bit confused by what was stated above:

"reading the data using the Schema file"

"using the schema file to load the data"