How to split column values into multiple rows based on a delimiter - Informatica PowerCenter

Question posted on Informatica Forum:

Can you please suggest me logic on how to make single row into multiple rows?

CURRENT OUTPUT

                    COLUMN-A     COLUMN-B      COLUMN-C

ROW1          1                         2                    MY||SUPPORT||INFORMATICA

 EXPECTED OUTPUT

                    COLUMN-A     COLUMN-B      COLUMN-C

ROW1          1                         2                    MY

ROW2          1                         2                    SUPPORT

ROW3          1                         2                    INFORMATICA

The number of rows to be generated depending on COLUMN-C. 

in this example in column-c there are 3 concatenated values so number of rows to generated are 3 and column-a and column-b should be unique for 3 rows...

like if there are 4 concatenated values in column-c then number of rows to be generated are 4.

Solution:

select CASE  WHEN  a.l = 1

              THEN  substr(col, 1, instr(col,',',1,a.l)-1)

               ELSE  substr(col, instr(col,',',1,a.l-1)+1,

                              CASE WHEN instr(col,',',1,a.l)-instr(col,',',1,a.l-1)-1 > 0

                                   THEN instr(col,',',1,a.l)-instr(col,',',1,a.l-1)-1

                                   ELSE length(col)

           END

           ) END EXPECTED_OUTPUT

          from

(select replace('MY||SUPPORT||INFORMATICA','||',',') col from dual)t,

(SELECT level l FROM DUAL CONNECT BY LEVEL <=

(SELECT Max(length(replace('MY||SUPPORT||INFORMATICA','||',',')) - length(replace('MY||SUPPORT||INFORMATICA','||',''))+1) FROM dual )) a

Oracle Database as source: Use above SQL query

Flat File Source: Use above SQL query in SQL Transformation and pass the column value as input.

Note:- Query is just for your reference, and you may have to change the query as per your requirement.

Reading data from Oracle VARRAY data type field - Informatica PowerCenter

VARRAY is an Oracle data type used to support columns containing multivalued attributes

Syntax for Varray:

CREATE OR REPLACE TYPE Vcol1 AS VARRAY(5) OF VARCHAR2(20);

/

CREATE TABLE varray_table (Field1 number, Field2 Vcol1);

Requirement: (Below was the requirement posted in a forum?)

Data in Table 1:

- F1 = "A;B;C"

- F2 = "Informatica"

- F3 = "Power"

But when I run the workflow and the mapping to extract this data, Informatica extract this data and load it in Table 2 like that

 ( F1 is a field to type "Array" and dimension 3 )

Data in Table 2 :

- F1 = "A"        =>        F1[0]

- F2 = "B"        =>        F1[1]

- F3 = "C"        =>        F1[2]

My problem is that I want to get same data in the same field in Table 1 and Table2 :

I want this in my Table 2 :

- F1 = "A;B;C"

- F2 = "Informatica"

- F3 = "Power"

Solution:

Informatica doesn't support "Varry table data type". It will be imported as Varchar2 by default within Informatica.

so make sure to change the source qualifier's sql query as below. (Assuming Oracle 11g been used)

WITH T AS (

   SELECT ROWID a, F2, F3, T2.COLUMN_VALUE col

   FROM Table1 T1, TABLE(T1.F1) T2)

   SELECT LTRIM (SYS_CONNECT_BY_PATH(COL,';'),';') COL, F2, F3

   FROM (

   SELECT a, F2, F3, COL, ROW_NUMBER() OVER ( partition by a order by a) FILA

   FROM T)

   WHERE CONNECT_BY_ISLEAF=1

   START WITH FILA =1

   CONNECT BY PRIOR a = a

   and prior FILA+1=FILA

  

Output:

F1          F2               F3

A;B;C     Informatica  Power

Varchar2 and nvarchar2 --- Informatica driver option

Difference b/w varchar2 and nvarchar2:




varchar2 -- stores data in the databases base character set

nvarchar2 -- stores data in the national character set



Now we could have a column with data stored in 7 bit ascii and other column with

data stored in utf8 in the same database.

Rather than make everything multi-byte, we can just store the small amount of multi-byte(If required) within the same database.

Otherwise single byte data is simple, fixed width, smaller & faster.



Enable the below option if you are using Informatica’s Data Direct Driver to read the exact database data type. ( To retain Varchar2 and char2 )

Informatica by default treats all the string data types to multi-byte, example varachar2 will be nvarchar2 in Informatica.