Unconnected Lookup Transformation -- Returning Multiple Values

> We can achieve Multiple values from Unconnected Lookup-Tr. We just need to override the default lookup sql query, there in one of the return port we can concatnate the values using ||.

> The concatnated multiple values will be returned from lookup to calling expression. In the expression we can extract / separate the concatnated string value (multiple values concatnated in lookup) and can pass to the target…

SELECT DEPT.DNAME||' '||DEPT.LOC as OUT_MUL_Values, DEPT.DNAME as DNAME, DEPT.LOC as LOC, DEPT.DEPTNO as DEPTNO FROM DEPT

> Before overrideing we should create one return port in the lookup-tr (here OUT_MUL_Values )

> Devide that OUT_MUL_Values in an expression

O_DNAME =     REG_EXTRACT(OUT_MUL_Values,'(\w+)\s+(\w+)',1)

O_LOC  =           REG_EXTRACT(OUT_MUL_Values,'(\w+)\s+(\w+)',2)

> Connect to target...

So here we are getting Multiple values using Unconnected Tr.

2nd Workflow needs to run after 1st workflow finishes

Solution - 1

> Create a worklet_1 for running all of the session that you are running in wkf1 and create another worklet worklet_2 to run all of the session in wkf2.

> Now create a new Workflow and connect
start - > worklet_1 -> worklet_2.

> Edit the connection link between worklet_1 -> worklet_2 and set the condition as
worklet_1.Status=Succeeded.

---------------------------------------------------------------------------------------------

Solution - 2

> In this case you have to create a flat file at the end of wkf1 run.. by using
touchxyz.txt ......

> and in wkf2 use event wait task and use file watch event on that flatfile.

> In this solution if you run the wkf2 it will wait for the flat file to appear.. if not it
won't run

-- Ram

Having some cols in Aggregator and not given 'Sorted Input' as checked and we done GroupBy . What will happen? Whether it sorts the data first and do group by.. or ..directly do groupby.

      If your incoming data is in sorted manner and if we forgot to enable the option in Aggr-tr, then Integ serv still treates the rows as unsorted, and try to scan from the first Record onwards. It will sorts the data in the Group By port and next does Grouping..

     For example we have NAMES column which contains alphabet names, and we checked on group by on NAMES, then it will sorts the data first and does group by with corresponding columns.

SID      SNAME

1        Ram

1      Pothina

2      Anil

2      Kumar

  3      Doppal

In this above example .. if we do Group by on SNAME…It will sorts the names and then does Group By..

2              Anil

3              Doppal

2              kumar

1              Pothina

1              Ram

-- Ram

To load only requird Rows........

If you want to load only records 3rd, 5th, 10th..

SELECT * FROM (SELECT ROWNUM AS RNO,EMP.* FROM EMP)

WHERE RNO IN (3,5,10)

 

-- Ram

Sumup of Current Record and Previous Record

i have a source table which has only one column of some quantity.

and i want the sum of the last two rows  in the target table.

problem something like this...

Source  Target

Field       Field

1              1

2              3

3              5

4              7

5              9

6              11

Solutions

-- Ram

REGEX_MATCH() -- Example..

Source is a Flat File.  The Name Columns Contain Non-Alphabetic Character at any Position Like

  (0-9) (!@........so on) . We need to separate the data.

Names
Anudeep
Rajes4h
Pravee@n
Ra5mesh
Kuldeep

$reddy

Maya_

Target should be loaded as..

Target1                 Target2    

Anudeep                   Rajes4h
Kuldeep                     Pravee@n

                                    Ra5mesh

                                     $reddy  

Solution

>  Use the Informatica data comparision function REGEX_MATCH( ) to achieve this..

> Use Filter Condition as  REG_MATCH(Names,'^[a-zA-Z]+$')

-- Ram

Half Records to one Target Half to another -- If Source is a Flat File ..

If your Source is FLAT FILE..

Design the two pipelines as shown above..

First Create a Mapping Variable $$Rec_Count

Second Pipeline

> Idea is to pick up Count of Rows using Aggrigator transformation using COUNT() function..... CNT = COUNT(*)

> Using Exp-Tr that value will be assigned to Mapping variable using SETVARIABL($$Rec_Count, CNT)

> Filter Tr is used with conditon set as FALSE.. becoz no records need to be loaded into target.

Here our idea is to find out RowCount and assigingin it to Mapping variable only..

First Pipeline

> In the Exp-Tr create a new port Rec_count (variable port) and increment it with 1 -- Rec_count + 1

This is becz inorder to generate seqnum with out using Seq-Gen-Tr ...

> Create o/p port  O_Rec_count = Rec_count

> Pass all ports to Router .. Create a group  and conditon as

Rec_Count <= ($$Rec_Count / 2)

Here $$Rec_Count is mapping variable.....

> Connect respective groups to targets.. mean default group to second target..

NOTE :  In this example Second pipeline should execute first so set ..

Target Load Order  : Second Pipeline first  -- First Pipeline next