top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration

Extract data from two tables of DB2 database and load into a temporary table

+2 votes

I am creating an informatica workflow which can extract data from two tables of DB2 database and load into a temporary table.

Suppose the two source tables name are Account (Parent) and Activities (Child).
They have 1:M relationship. Means an Account can have many Activities (Account.PK = Activities.FK).

Activities table has two columns- first 'Type' whose value could be 'Paid', 'Will-Pay' or 'Not-Paid'.And second column is 'Created_Date' datetime datatype, whenever you create new activity record, date and time would get stamp in this field.

Now, condition to load data in temporary table is - "For an Account record, it would 1st check in Activities table for today's Paid activities (Type = Paid). If it finds more than one paid activities, then it would pick the Latest created one (Created_Date column) out of them. If there is no Paid activity record for the Account, then it would pick latest created 'Will-Pay' activity."

Means, it should pick latest Paid activity for today (Sysdate) for an Account, if it is not present then only It will pick latest Will-pay activity for today.

Please help me to understand how I can implement this logic in Informatica workflow and which transformations I should use and how?

Thanks alot. Kindly help.

posted Apr 25, 2014 by Pooja Bhanout

Share this question
Facebook Share Button Twitter Share Button Google+ Share Button LinkedIn Share Button Multiple Social Share Button

1 Answer

0 votes

Best way to do it on SQL cause realize business logic on ETL it's not good. But if you insist it can be created by many ways. As example:

With SQL override You can create 3 lookup transformation for Activities table with overrided SQL (and columns too) and one expression transformation for condition.
1.Lookup to find more than one 'paid' activities accounts
2.Lookup to find last 'paid' activity per account
3.Lookup to find last 'will pay' activity per account
4.Expression to return correct Activities key based from 1-3 lookup results

Without SQL override you need to recreate similar logic with filter, aggregator, joiner transformations

answer Apr 28, 2014 by Shweta Singh
Similar Questions
0 votes

How can I insert data into one table from two other tables where i have three tables namely users, role and userrole.
Now I want to insert the data into userrole table from users table and role table with a single statement.

0 votes

Design a mapping to load the first record from a flat file into one table A, the last record from a flat file into table B and the remaining records into table C?

+1 vote

In my source table data is

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

p1,    10
p2,    xyz
p3,    abc 

Table s2

p1,    20
p2,    xyz
p3,    abc 
Contact Us
+91 9880187415
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Karnataka INDIA.