SQL Query to retrieve data from multiple tables in a database

[former_member640968]

I am trying my best to explain the issue. We do have 5 tables M1,M2,M3,M4,M5 and after all we have only one common field M_ID in all the tables. Also M_ID will have different values. So my requirement is to retrieve the records that related to individual M_ID at a time.

Example: If I have M_ID values as 1,2,3,4 etc then for each SQL select I need to retrieve the records for only one M_ID value

M1 Table: M_ID,F1,F2,.. M2 Table: M_ID,F3,F4,.. M3 Table: M_ID,F5,F6,..

F* are the fields that can be repetitive multiple times. So I need to take care of Data redundancy too.

M1 contains the Material Details,M2 contains the Plant Details,M3 Contains the Product description details, M4 Contains the SalesOrg details and M5 Contains Tax details. Basically we are retrieving the data fromthe database to map the MATMAS IDOC. So I would also be required any functional relation exists between these tables so as to perform an inner Join operation?

[former_member184720]

Hi Sudheer - Any problem to go with Stored Procedure approach..? this way i feel you have more control on the DB operations.

[former_member640968]

Could you please elaborate how can I use SP and how it will be be a better approach?

[former_member184720]

Hi Sudheer -

Instead of using joins(in sender channel) to get the records from multiple tables write a store procedure in the database which you can call from JDBC sender channel.

Inside the stored procedure you can write your queries/joins/filters.

Please refer to below document:

http://scn.sap.com/community/pi-and-soa-middleware/blog/2013/03/06/executing-stored-procedure-from-s...

[iaki_vila]

HI Sudheer,

May be i misunderstood your problem, but you are answering yourself, you can use M_ID to do the join. I suppose you are talking about JDBC - IDOC scenario, isnt it?

Regards.

[former_member640968]

Yes