JDBC Sender Structure

[Former Member]

Hi,

I have a requirement to select data from 2 different DB tables with different fields. Is there a possibility in PI to handle multiple resultset in the Source PI JDBC structure?

Regards,

Jishi

[Ryan-Crosby]

Hi Jishi,

You could also go this route shown below which would not require a stored procedure on the database side.  The first picture is a basic data type with two SQL statements and the corresponding response structure follows:

You can use this type of message structure in your interface to run multiple JDBC statements using one database connection.

Regards,

Ryan Crosby

[Former Member]

Hello,

Check this:

http://scn.sap.com/community/pi-and-soa-middleware/blog/2013/07/19/sap-pi--udf-to-capture-multiple-r...

Thanks

Amit Srivastava

[ambrish_mishra]

Hi Jishi,

IMO, multiple resultset cannot be achieved at the sender side.

You can do a stored procedure to meet your requirement through a sender JDBC adapter. This stored procedure will execute in the database and retrieve data from the 2 tables based on foreign key relationship and return you the fields required.

I am not sure if hierarchy can be achieved like Row1->Field1 but if you are looking to segregate data based on tables, then you can do so by field names (T1_Fileld1)

Hope it helps!

Ambrish

[praveen_sutra]

Hi,

probably you can get one set through standard adapter and second set of values through java mapping.

thanks and regards,

Praveen T

[Senthilprakash1]

something like this "

SELECT <Table 1 Fields, Table 2 Fields>

FROM <Table1> D,<Table2> C where D.<Field>= C.<Field> and C.<Field> in (select distinct top 100 C.<Field> from Table2 C, Table1 D where C.<Field> = D.<Field> ORDER BY C.<Field>)"

[Senthilprakash1]

If there is a relation between the two tables. and if you can come up with a meaning full select query for your required output. then yes you can fetch data from multiple tables.

else best way to have SP created and pull the data from it.

[former_member802575]

Hi,

   It is possible in Pi  using view : you can ask DB team to create view  and updated view

other wise see this link http://scn.sap.com/thread/130261

srikanth

[dipaks_patil]

Hi

it can be possible using store procedure.

http://scn.sap.com/thread/3349296

http://scn.sap.com/thread/823214

https://scn.sap.com/message/13163250

Thanks,

Dipak Patil

[Former Member]

Hi,

My scenario(Sender JDBC Channel) is to pick sales data generated by POS system and I have to pick a record's header from the 1st table and the corresponding line items from the 2nd table and update the flag in the table to mark the record as read.

Regards,

Jishi

[ambrish_mishra]

Hi Jishi,

Although I have responded earlier to your post but this requirement is easily achieved through the design below:

You can request the DB team to create a staging table. let DB team populate this table with  data from the 2 tables based on foreign key relationship.

You can ask them to create a stored procedure and share with you. This stored procedure will be called in Sender JDBC adapter; will execute in the database and return you a flat structure with all the rows of this staging table. So the header fields will get repeated for every related item and can be identified by the key field.

You can simply mark the record as read in the adapter itself (Update SQL statement).

Hope it helps!

Ambrish

PS: You can also do a select on the staging table in the adapter instead of using a stored procedure

Message was edited by: Ambrish Mishra

[Former Member]

Hi Ambrish,

Once I pick the records in the format that mentioned(So the header fields will get repeated for every related item and can be identified by the key field). then please can you tell how I can identify the related records of each sales order and  map the records to the target BAPI structure which will accept only one sales order per BAPI call.

Thanks,

Jishi

[ambrish_mishra]

Hi Jishi,

Here is an example of mapping, if you pick the records with flat structure:

This is a case where target is an IDoc and multiple records related to same company code and reference number will generate a unique IDoc. The database call will pick multiple records for multiple orders.

This concept will be the basis for your mapping.

However, are you planning to pick one sales order per database call ?

I don't think so... you can pick all the records and split it based on the logic above and call the BAPI.

Hope it helps!

Ambrish

[Former Member]

Hi Ambrish,

The approach you have suggested seems to reach a solution for this requirement. You have mentioned regarding the mapping to Idoc, however can you please tell how the data can be sorted at field level?

Thanks,

Jishi

[ambrish_mishra]

Hi Jishi,

For Item segments, if you are using similar concept which I suggested, just remove collapseContext function and the item segments will be created properly.

At field level, it will be direct one to one mapping.

Do revert back with screenshots, if you are stuck.

Ambrish