top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Transformation to choose datatable based on value in separate table

0 votes
60 views

I have three tables (Oracle source), lets call them tables 1, 2, and 3.

I would like to check a boolean field in table 1, and if it is T I want only data from table 2, and if it is F, I want only table 3.

Which transformation would be the most efficient for doing so, and how would I go about implementing it?

I'm experimenting with the Filter, Java, and Expression transformations, but if the expressions are checking on a row-by-row basis, then it seems like overkill for the expression to run on every row instead of just checking once and using the appropriate table.

Both tables 2 and 3 have a field with the same name, and I want that field for just one of the tables, based on the condition.

posted Dec 29, 2014 by Sunil

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

Create a mapping similar to the following diagram:

src_TABLE2 --> sq_TABLE2 --|
                           |--> union --> (further processing)
src_TABLE3 --> sq_TABLE3 --|

 and use the following Source Filter conditions* in the source qualifiers:
-- for sq_TABLE2
'T' = ( SELECT FLAG FROM TABLE1 )

-- for sq_TABLE3
'F' = ( SELECT FLAG FROM TABLE1 )

There are two source tables in the mapping and the union transformation merges data from these two pipelines. However, because of the filtering conditions at any given time data will be retrieved only from one of the source tables.

answer Dec 30, 2014 by Shweta Singh
Similar Questions
0 votes

Hi All,

I want to implement the below scenaio without using pl/sql procedure or unix script. I want to be implement by using Pwoercenter only.

Kinly suggest me.

1.I have a table called emp_details with coulmns (empno,ename,salary,emp_status,flag,date1) .

2.if someones updates the columns emp_status='abc' and flag='y' ,Informatica WF 1 would be in contunous running status and checking emp_status value "ABC"

3.If it found record / records then query all the records and it will invoke WF 2.

  1. WF 1 will pass value ename,salary,Date1 to WF 2 (Wf2 will populate will insert the records into the table emp_details2).

Kindly tell me the informatica approach instead of plsql and shell script...

Thank you

+1 vote

I want to implement the below scenario without using pl/sql procedure or unix script. I want to be implement by using Powercenter only.

Kindly suggest me.

  1. I have a table called emp_details with columns (empno,ename,salary,emp_status,flag,date1) .
  2. If someones updates the columns emp_status='abc' and flag='y' , Informatica WF 1 would be in continuous running status and checking emp_status value "ABC"
  3. If it found record / records then query all the records and it will invoke WF 2.
  4. WF 1 will pass value ename,salary,Date1 to WF 2 (Wf2 will populate will insert the records into the table emp_details2).

    Kindly tell me the informatica approach instead of plsql and shell script...

0 votes

I have a workflow which loads data from a Flat file to a Stage Table after a few basic checks on a few columns. In the mapping, each time my check fails (meaning if the column has an invalid value) , I make an entry to a ErrorFlatFile with an error text.

Now , I have two targets in my mapping. One being the Stage table and the other is the Error Flat File.

What i want to achieve is this ? Even if there is one entry in the ErrorFlatFile (indicating there is an error in the source file ) , I want to truncate the Target Stage Table.

Can someone Please help me with how i can do this at the session level.

+1 vote

In my source table data is

s_name,p_name,value
s1 ,   p1,     10
s1 ,   p2,     xyz
s1 ,   p3 ,    abc
s2 ,   p1 ,    20
s2 ,   p2 ,    xyz
s2 ,   p3 ,    abc

I want two target tables, first table is based on s_name s1, second table based on s_name s2. Both table contains contains p_name and value.

The target table data like as

Table s1

p_name,value 
p1,    10
p2,    xyz
p3,    abc 

Table s2

p_name,value 
p1,    20
p2,    xyz
p3,    abc 
...