Mload in with multiple delimiter

Database
Fan

Mload in with multiple delimiter

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.

Tags (1)
5 REPLIES
Senior Apprentice

Re: Mload in with multiple delimiter

In TD14 you could use '^' as delimiter in MLOAD and STRTOK(actvy_msg_txt, '|', n) for the subparts within the SQL INSERT.

Fan

Re: Mload in with multiple delimiter

Thanks dnoeth!!

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(

FIELD1=:STRTOK(actvy_msg_txt,'|',1)

FIELD2=:STRTOK(actvy_msg_txt,'|',2)

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.

Senior Apprentice

Re: Mload in with multiple delimiter

FIELD1 = :STRTOK(actvy_msg_txt,'|',1) is no valid syntax for an INSERT, should be something like STRTOK(:actvy_msg_txt, '|', 1)

Fan

Re: Mload in with multiple delimiter

Hi dnoeth,

I am working on TD 13 .Is there any way in TD 13 we can do this?? I have tried using as below

FIELD1=substr(:ACTVTY_TXT,0,index(:ACTVTY_TXT,'|'))

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

Senior Apprentice

Re: Mload in with multiple delimiter

The STRTOK function is similar to one of the Ebay UDFs (eListGetValByIdx) found at 

http://developer.teradata.com/blog/madmac/2010/03/a-few-basic-scalar-string-udfs

Ask your DBA to install it.