I have a case where I need to use multiple delimiter in mload script .I have data in below format:
49AS29317^A080a448c219f5b36fa7af2a4bec4893e^A2014-12-09 11:14:07.000000^A4000007^AEND USER^AISSO^A1^AENG|USA|1170c376-94f3-4dc8-a3c7-dcce062bd1ab|360|280061032%188%1%|0%SUCCESS%280061032%%188%CSP_MIG+CHNG_USER_NM%LGIN_SCURT+LGIN_SCURT%INGD%FEENIX_CC_TEST%%|^A10.118.225.114^A
The highlighted field is actvy_msg_txt from this field data is flowing to several target column.
This is having 6 fixed part separated by PIPE ‘|’.In these 6 part last two part are again having several subpart seperated by '%' delimiter.
can anyone help me to load this data using mload.
In TD14 you could use '^' as delimiter in MLOAD and STRTOK(actvy_msg_txt, '|', n) for the subparts within the SQL INSERT.
Can you please explain it bit more .I tried using below but it didnot worked out.
defined layout as :
.FIELD actvy_msg_txt * VARCHAR(1024);
In SQL INSERT as
INSERT INTO SCHEMA.TEST(
and so on.
but while executiing it is showing error as
RDBMS failure, 3706: Syntax error: Data Type "actvy_msg_txt" does not match
a Defined Type name.
FIELD1 = :STRTOK(actvy_msg_txt,'|',1) is no valid syntax for an INSERT, should be something like STRTOK(:actvy_msg_txt, '|', 1)
I am working on TD 13 .Is there any way in TD 13 we can do this?? I have tried using as below
but for all 6 field it is very much lengthy and confusing.
Pls suggest if it can be done in more simplier method.
Thanks In advance
The STRTOK function is similar to one of the Ebay UDFs (eListGetValByIdx) found at
Ask your DBA to install it.