Update 3tables in DB in SAP PI 7.3.1

[Former Member]

Hi,

I have secanrio JDBC -->PROXY.I have 3tables at sender sice and based on DATE fld i need to update all 3tables.

Update date as per system date.Im using oracle11g.

I have tried using view but its not working in PI7.3.1

Kindly give an best solution for this.

[Former Member]

Hello!

In my opinion, the correct way of doing it is creating ON CASCADE (on update/on delete/on insert) triggers when changing the value of one table. This will create dependency that will propagate the value changed on the field of one table to the others, automatically using triggers.

Please ask your DBA more information about this.

BR,

Lucas Santos

[Former Member]

Hi,

if i understand you correctly after fetching the data from tables you need to update the 3 tables based on date.. if so use joins and update the same...

else please elaborate your requirement...

HTH

Rajesh

[Former Member]

After fetching the data need to update DATE fld.

I have used joins in select query in where clause.

But in update how to do this?

i have mention as UPDATE T1,T2,T3

SET DATE = sysdate

WHERE (condition using joins as in select query)

Note:-SELECT query is fine.

[Former Member]

Hi,

Try executing the query directly at database level and check the result...

As PI tries to do the same ... Also check user credentials for appropriate access..

HTH

Rajesh

[Former Member]

Hi,

You may write stored procedure to select and update data and then put <TEST> in update sql statement.

Please check this link which says:

If you want the data determined from the Query SQL Statement to remain in the database unchanged after being sent successfully, enter <TEST>.   

This is recommended if the data has not only been read, but also changed by a stored procedure entered under Query SQL Statement.

http://help.sap.com/saphelp_nw73/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/content.htm

Regards,

Beena.

[Former Member]

Hi Anitha,

you can insert into mutliple tables using insert all and insert first ,and you can do multiple operations to the same table using merge ; but you can't do multiple updates to different tables in one shot.

For your understanding try once directly executing the query in d/b ;you will get the below error.

"cannot modify more than one base table through a join view"

Try for other alternatives ;say using stored procedure .

Regards

Venkat

[Former Member]

Thanks for your inputs.

Multiple Update is not working using views.

Im planning to do custom module function for this.

Because client is not fine with stored procedure,

Please guide me on the same.

Regards,

Anitha

[Former Member]

Hi,

I dont understand what you mean by views here for updation...

As views in terms of database world is used only for retrieving the data and update cannot be done...if you are in the right direction then ignore else read what i mean..

HTH

Rajesh

[baskar_gopalakrishnan2]

Views are meant for just querying. You cannot do update. Talk to database team and use regular tables without view. If you do join over there you can able to update three tables based on the date.

[Former Member]

Hi,

One solution could be to update one table after select query in sender jdbc adapter and then add another jdbc receiver along with proxy receiver to update remaining 2 tables.

You may update the date in other tables using query by setting the values from first table where date was updated earlier. (action=SQL_DML)

http://help.sap.com/erp2005_ehp_04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

Also you may modify query in jdbc sender so that it checks date of all tables. (say date is blank in all 3 tables). This will ensure that newly added records are not selected again.

You may consider run operating system command option also. But this will not provide transactional behaviour. If date field in not part of where clause while selecting records, you may update it using script. However this option can not be used if you want transactional behaviour that the records newly added should not be updated by current transaction.

Regards,

Beena.