JOINS in JDBC receiver adapter

[sahithi_moparthi]

Hello Gurus,

I have a requirement to design an interface which involves JDBC adapter as a receiver.

Scenario:

SAP would send a request message with EmployeeID field. 

PI would perform the select query on three tables (each table has more than a million records) based on EmployeeID field and send the output of select query back to SAP as response.

Can you please let me know how it can be implemented in PI?

Can a join query be formed in PI message mapping step?

If So could you please provide the step by step procedure ?

[Former Member]

Hi Sahithi,

Simplest solution would be to implement a stored procedure and call the same from PI mapping which does the all query logic.

Thanks

Chirag

[sahithi_moparthi]

Hi Chirag,

   Client wants to implement that in PI.Database team is not ready to implement anything from their side.

So now we are looking for an options in PI,how could we implement JOINS in PI.

[Former Member]

Hi,

You can create three individual select on tables and get the desire values from all three tables and do necessary transformation in PI.

check below select statement -

Defining a SELECT Statement - Advanced Adapter Engine - SAP Library

thanks

chirag

[sahithi_moparthi]

Hi Chirag,

Thanks for your reply!

I am able to retriev the data from multipletables using "SELECT" statement at receiver side. My scenario is synchronus.Could you please expain how can we send the response data to SAP.

Also is it possible to do it dynamically.Incase if i have 10 to 15 table then how could we acheive this ?