|
Title: How to handle multiple rows in stored procedure transformation? Post by: shankar.singu on May 31, 2010, 04:50:08 pm Hi,
How to handle multiple rows in stored procedure transformation? Example.. I am using connected procedure lets say sp_pr_getTransction(customerID number) I will pass customerID as input and the procedure should return list of the transactions done by that customer id, further we will filter and put some more transformation for other reasons. Can anyone please tell that how to handle this situation? Regards, Sankar. S Title: Re: How to handle multiple rows in stored procedure transformation? Post by: Sipra on May 31, 2010, 09:20:04 pm Quote should return list of the transactions done by that customer id Sankar, Informatica stored proc trasnformations handles multiple columns as its output and it do a row by row commit. Can you please expand your problem, so that we can keep it in right way..! Title: Re: How to handle multiple rows in stored procedure transformation? Post by: shankar.singu on June 01, 2010, 10:45:49 am Dear Sipra,
Pls have a look on the below table and scenario, I have explained. ----------------------------------------------------------------- CustomerID AccNo CCY TxnAmount Date ----------------------------------------------------------------- 1234 222222 USD 100.00 May 1, 2010 1234 222222 EUR 100.00 May 15, 2010 1235 333333 INR 200.00 May 10, 2010 1234 222222 USD 100.00 June 1, 2010 1235 333333 USD 100.00 June 1, 2010 1234 222222 USD 50.00 June 1, 2010 I will pass Customer id, the return value should be more than one row. Example if I pass customer id 1235, then the list of txn I should get which has done by the customer. ----------------------------------------------------------------- CustomerID AccNo CCY TxnAmount Date ----------------------------------------------------------------- 1235 333333 INR 200.00 May 10, 2010 1235 333333 USD 100.00 June 1, 2010 The procedure is maintained in DB as sp_pr_getTransction(customerID number) How to handle the above situation? Hope you will give solution to handle this since you are the king. Regards, Sankar. S Title: Re: How to handle multiple rows in stored procedure transformation? Post by: Allegra on June 01, 2010, 12:17:40 pm Expecting the solution too... 8)
Title: Re: How to handle multiple rows in stored procedure transformation? Post by: Whoever on June 03, 2010, 10:41:33 am Perhaps I'm just not aware of how to do it, but this cannot be done in Informatica (at least not in a simple manner).
The workaround would be to break up your mapping such that - 1. The first part terminates at the Stored Procedure (sp_pr_getTransction), which inserts the resultant data into a separate table (instead of returning it as output). 2. The second part starts with this separate table as input and then proceeds with the rest of the logic. Title: Re: How to handle multiple rows in stored procedure transformation? Post by: Sipra on June 10, 2010, 09:50:38 pm Hey here's the solution -
1) Using External Stored Proceudre Transformation will resolve this. Have some home work. Let me know in case of nay isssues. 2) The best solution is achieving this thru calling a Stored Procedure beyond Informatica premises will resolve. Incur your logic in Stored Proc and call it thru Post session by creating a Dummy task at Informatica level. We resloved our issues in past thru the Second approach. Same approach given by Whoever. Hope you got the right solution...! :-* Title: Re: How to handle multiple rows in stored procedure transformation? Post by: shankar.singu on June 11, 2010, 10:51:10 am Hi Sipra,
Thanks for reply. The answer you have given is very high level. Could you pls elaborate? if possible provide with an example or steps which have to follow and it will be really helpful for us.. Regards, Sankar. S |