0

I have a data in a table as 

ID  Service                 Amount
1   Mpesa,Credit,topup,App  2000
2   Credit,topup,App        1000
3   Topup,App               500

I need the output as 

ID  Service Amount
1   Mpesa   2000
1   Credit  2000
1   topup   2000
1   App     2000
2   Credit  1000
2   topup   1000
2   App     1000
3   Topup   500
3   App     500

This to be implemented in ODI 11g, Please help me to proceed with this case. RegEXp function will be doing the needful but need to understand how REGEXP function could implement in ODI 11g.

Thanks, Gowtham Raja S

Naeel Maqsudov
  • 1,352
  • 14
  • 23
Gowtham Raja
  • 39
  • 2
  • 4
  • I can even add more details on request,Please expecting a solution for this issue. – Gowtham Raja Dec 15 '15 at 12:59
  • If you can write how did you achieved in REGEXP, I may help with ODI implementation. In worst case you can use ODI Procedures. – Canburak Tümer Jan 21 '16 at 10:54
  • Thanks for the response #Canburak , Will be happy if you could share any example procedure which satisfies my need of using REGEXP_SUBSTRING in ODI implementation. Appreciate any kind of info/help to resolve my requirement .. – Gowtham Raja Jan 22 '16 at 11:45

2 Answers2

0

The problem you are trying to solve is "Split delimited column into multiple rows". Pure REGEXP_SUBSTR is not enough. Very similar task is described on AskTom.

Its difficult to do this natively from ODI. The most easy way for ODI is to create a VIEW (see link above) and then implement a mapping.

UPD: Additioaly look at the similar question

Naeel Maqsudov
  • 1,352
  • 14
  • 23
0

using this query you can achieve what you want:

SELECT SUBSTR(columnname, 1, INSTR(columnname,',') - 1) FROM tablename;

so you can use the substr in Mapping column:

SUBSTR(Service, 1, INSTR(Service,',') - 1)

There is also another way to apply regexp_substr and windowing functions in ODI 11g but that requires Custom Edited Knowledge Modules so this is a workaround which will do the work in this case

Lorik Berisha
  • 243
  • 1
  • 2
  • 20