Sender JDBC Adapter with Mutiple SQL Database Tables.

[Former Member]

Hi All,

My requirement is SQL->PI7.0->BI.

I have a plan to go with the senario like this : JDBC sender->SAPPI->ABAP Proxy.

And also I need fetch the data from more than 10 data tables with different database tables with key fields.

Could you please suggest me, How to extract all tables data to SAP PI System. Either need to go with Stored procedures or Any Join conditions or each table like as one Interface.

Please provide me your suggestions.

Regards,

Chandra

[Former Member]

Hi ,

Chandra ,

Best way is Database Views

Involve a Database guy in your scenario : Tell DBA the fields required , tell DBA the PrimaryKey and ForeignKey Relation Between All your 10 Tables.

DBA will create a View for you on 10 Tables.

So in Ur SELECT Query . you can write simply

Select * from <ViewName>;

And One more thing to Tell DBA to create a UPDATABLE VIEW not only READ-ONLY View.

By this way you can way you can Update VIEW also in UPDATE QUERY of sender Adapter...

Regards

PS

[Former Member]

Hi All,

Thanks for your Inputs.

In SAP PI System How the Source and Target Data Structure look like.

Could you please give me simple example.

Regards,

Chandra

[Former Member]

If You Select Query is :

Select EMP_ID , EMP_NAME, EMP_ADD1, EMP_ADD2, EMP_PINCODE

FROM EMP_VIEW

WHERE EMP_FLAG = 'N';

Then on your Source Structure would be :

Source

- recordset

- - EMP_ID

- - EMP _NAME

- - EMP_ADD1

- - EMP_ADD2

- - EMP_PINCODE

It is simple structure like anyother ...

and create target accordingly ...

[Former Member]

Hi Chandra,

I feel you write a join condition as a SELECT query and you mentioned this query statement in JDBC sender adapter,

Otherway,you write a stored procedure for selecting the records from 10 tables, Then you write query statement to call the stored procedure.

Regards,

Sateesh

[Former Member]

Hi Sateesh,

Thanks for your Inputs.

I think for tables for writing the join statements in SAP PI it is difficult. But stored procedure, How we need to write all 10 tables.

Could you please give some tip. And also, In SAP PI How the sender Data type will look like.

Thanks in Advance.

Regards,

Chandra

[Former Member]

Hi Chandra,

As Prabhat said, you can take help from a DBA and use a DB view to handle this requirement.

FYI:

[baskar_gopalakrishnan2]

Creating VIew to read 10 tables is better idea. I do View in our interfaces.

In additon to other suggestions, Please go through this link. The below link taught me a lot about knowing View.

http://www.w3schools.com/SQL/sql_view.asp

Note: In your sender adapter you can write SQL Query as Select fields from YourView where condition