cancel
Showing results for 
Search instead for 
Did you mean: 

PI-Multiple SELECT query in sender JDBC Adapter

former_member225737
Participant
0 Kudos

Hi,

I had a scenario JDBC->PI->Proxy.

I want to fetch data from multiple tables i.e. to use multiple SELECT queries.

I cannot write JOIN in query also database is not accessible to create stored procedure.

Thanks & Regards,

Parin Chahwala.

Accepted Solutions (0)

Answers (3)

Answers (3)

former_member187587
Contributor
0 Kudos

And there is a reason why you can't. Architectural reason.

SAP PI is a message based platform, and as such the data that is send to the PI Integration server should already be in business granularity and have all the required data to complete the transaction a business transaction.

You will always get a flat structure from a relational DB selects on the JDBC sender side so there is no proper\stable\correct way to do it (dont even think of ccBPM...) . on the Receiver side however you have much more flexibility.

I would recommend you to try and use a select from an intermediate table where the xml will be stored in a CLOB type field. this will allow a "queu like " work with the database receiving all the info you need. the logic however should be written in SQL language on the Application side.

Hope this is clear enought.

iaki_vila
Active Contributor
0 Kudos

Hi Parin,

You have set hard premises. If you don't want a join operation and SP is not possible you can't achieve your goal with only on scenario. You can do two scenarios:

1. JDBC - Proxy (async): you do the select in the main table with the status flag.

2. Proxy - JDBC (Sync): in your proxy abap code when a data is received you call this second scenario with the necessary data to get the data for the set of tables needed. You can do this scenario enough generic to deal will the different tables.

Regards

manoj_khavatkopp
Active Contributor
0 Kudos

Y don't you write JOIN operation ?

Select <f1,f2,.. m_id, n_id...fn> from M1 M2 M3 where M1.m_id = M2.m_id and M2.n_id = M3.n_id.

Here f1,....m_id,n_id,.....fn are fields.

you don't have any other option other then having JOIN or Stored procedure.

Br,

Manoj