I have a file with fixed-length records, but they need to be interpreted in various ways, depending on first field (let's call it RECORD_TYPE). We currently do it easily with Multiload:
.FIELD RECORD_TYPE 1 CHAR(5);
/* First variant */
.FIELD variant1_field1 6 CHAR(15);
.FIELD variant1_field2 * CHAR(15);
/* Second variant */
.FIELD variant2_field1 6 CHAR(10);
.FIELD variant2_field2 * CHAR(12);
.FIELD variant2_field3 * CHAR(8);
/* (......) */
.IMPORT INFILE file_name
APPLY DML_for_variant1 WHERE RECORD_TYPE='TYPE1'
APPLY DML_for_variant2 WHERE RECORD_TYPE='TYPE2';
Can something similar be done with TPT?
I can't find a way - other than multiple passes through the file, which is unacceptable.
Data connector producer may have only one schema and the schema is just a collection of fields, it's not possible to create variant records (you can't specify offset or starting position of field).
Is it a case where Multiload cannot be replaced with TPT?
Thanks Raja. I haven't seen this particular post, but I am aware of CASE DML construct in APPLY statement. It however does not solve my problem.
My question is not only about applying changes to different tables according to conditions based on input schema fields (yes, it can be easily done with CASE DML), but first of all about interpreting the same string of input bytes differently (via different schemas) based on one of attributes. It's like an union in C language.
In my example, for each record, bytes beginning from 6-th will contain something completely different, and the info what exactly, is encoded in the first five bytes.
I mentioned C union only to picture the idea of a variant record. I have no intention of developing any new tool :).
I just wonder whether it is possible to do in TPT what we currently do in Multiload. It seems that no. I would be happy if someone from TPT engineering team would answer.
If you are talking about the MultiLoad feature whereby you can specify the byte position in the input stream for the various columns, such as:
COL1 1 INTEGER
COL2 10 CHAR(10)
COL3 5 CHAR(5)
then no, TPT does not yet support that feature. It is on our list of features to do, but there has not been a high enough demand to bump its priority.