JOINS in JDBC receiver adapter

[former_member216353]

Hi,

We have a synchronous Proxy to JDBC interface where SAP would send a request message with Ordernumber field.

PI would perform the select query on three tables (each table has more than a million records) based on Ordernumber 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?

[baskar_gopalakrishnan2 · Accepted solution]

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

You can use the following message structure in the receiver structure.

Refer SAP [link |http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm]and the statment6 structure.

Sample:

<StatementName>
<anyName action=u201D SQL_QUERYu201D >
<access>Write your join query here</access>
<key>
  <placeholder1>value1</placeholder1>
  <placeholder2>value2<placeholder2>     
</key>
 </anyName >  
 </StatementName>

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

You can do it at the receiver side itself. Why do you want to do it message mapping? Since Pi 7.1 supports jdbc lookup, you can do the query in the mapping too. BUt in your case, you do all the query at the receiver side and get the response from the jdbc system. Hope that helps.

[former_member216353]

Hi all,

thanks..My query has been answered.

I am able to successfully execute the JOIN query using STATEMENT 6 in the link provided by Bhaskar.

[Former Member]

u can create a SP on the receiver side...talk to ur DB admin....let SP handle the selection logic

[rajasekhar_reddy14]

I would recommend to use stored procedure ,you will have more flexibility to perform joins.