Data Warehousing Community Forum
February 10, 2012, 01:09:11 am

Pages: [1]   Go Down
« previous next »
  Print  
Author Topic: How to handle multiple rows in stored procedure transformation?  (Read 2617 times)
shankar.singu
DW Fresher
*

Reputation: +6/-0
Offline Offline

Posts: 16


View Profile
« 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
Logged
Sipra
Founder
*****

Reputation: +59/-0
Offline Offline

Posts: 323


Am the King...


View Profile WWW
« Reply #1 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..!
Logged
Sipra
Founder

www.dwforum.net
shankar.singu
DW Fresher
*

Reputation: +6/-0
Offline Offline

Posts: 16


View Profile
« Reply #2 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
Logged
Allegra
DW Apprentice
**

Reputation: +17/-0
Offline Offline

Posts: 75



View Profile
« Reply #3 on: June 01, 2010, 12:17:40 pm »
Expecting the solution too...  Cool
Logged
@llegr@
what do you call an Ant with an AK47 ?
Whoever
Administrator
*****

Reputation: +34/-0
Offline Offline

Posts: 124



View Profile WWW
« Reply #4 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.
Logged
If most people said what’s on their minds, they’d be speechless.
Sipra
Founder
*****

Reputation: +59/-0
Offline Offline

Posts: 323


Am the King...


View Profile WWW
« Reply #5 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...! Kiss
Logged
Sipra
Founder

www.dwforum.net
shankar.singu
DW Fresher
*

Reputation: +6/-0
Offline Offline

Posts: 16


View Profile
« Reply #6 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
Logged
Pages: [1]   Go Up
  Print  
« previous next »
 
Jump to: