All Forums Database
Abh27 3 posts Joined 12/14
29 Dec 2014
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:
dnoeth 4628 posts Joined 11/04
30 Dec 2014

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

Dieter

Abh27 3 posts Joined 12/14
05 Jan 2015

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.
 

 

dnoeth 4628 posts Joined 11/04
05 Jan 2015

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

Dieter

Abh27 3 posts Joined 12/14
06 Jan 2015

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
 

dnoeth 4628 posts Joined 11/04
06 Jan 2015

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.

Dieter

You must sign in to leave a comment.