CODEWITHSUNDEEP
sqloracle-databaseoracle-data-integrator
M_Gh
M_Gh

Reputation: 1142

How to split a string field in several rows in Oracle Data Integrator 12c

I am a newbie in ODI 12c. I have recently installed it and had several tests on it. My problem is that I have a table like this:

enter image description here

I want to split RULES column in different rows like this in ODI 12c:

enter image description here

Would you please guide me how I can do that in ODI 12c?

Any help is really appreciated.

Upvotes: 0

Views: 949

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

REGEXP_SUBSTR() cannot be directly used within ODI (except can be used within a DB view) while REGEXP_REPLACE() can be for version 12. But it would be easier to operate through use of REGEXP_SUBSTR() function in order to split if it could be used. But the other function is harder to use for such splitting purposes.

Alternatively, you can use standard former functions such as SUBSTR(), INSTR() and LENGTH() along with a Hierarchical Query in order to generate rows as per split such as :

 SELECT timestamp,
        CASE WHEN level = 1 
             THEN SUBSTR( rules, 1, INSTR(rules,',') - 1 )
             WHEN level = LENGTH(rules) - LENGTH(REPLACE(rules,',')) + 1
             THEN SUBSTR( rules, INSTR(rules,',',1,level-1)+1,
                                 LENGTH(rules)-INSTR(rules,',',1,level-1)+1)
             ELSE SUBSTR( rules, INSTR(rules,',',1,level-1)+1,  
                                 INSTR(rules,',',1,level)-INSTR(rules,',',1,level-1)-1)
              END AS rules
   FROM t
CONNECT BY level <= LENGTH(rules) - LENGTH(REPLACE(rules,',')) + 1
    AND PRIOR SYS_GUID() IS NOT NULL
    AND PRIOR timestamp = timestamp

Demo

Upvotes: 1

EJ Egyed
EJ Egyed

Reputation: 6094

I don't have much experience with ODI, but a query like the one below should split your rules so that each rule has its own line.

WITH
    some_data (timestamp, rules)
    AS
        (SELECT SYSDATE, '2,4,5' FROM DUAL
         UNION ALL
         SELECT SYSDATE - 1, '3,6' FROM DUAL)
  SELECT sd.timestamp,
         REGEXP_SUBSTR (sd.rules,
                        '[^,]+',
                        1,
                        lines.COLUMN_VALUE)    AS rule_number
    FROM some_data sd,
         TABLE (CAST (MULTISET (    SELECT LEVEL     AS level_num
                                      FROM DUAL
                                CONNECT BY INSTR (sd.rules,
                                                  ',',
                                                  1,
                                                  LEVEL - 1) > 0) AS SYS.odciNumberList)) lines
ORDER BY timestamp, rule_number;

Upvotes: 1

Related Questions