Update RESB with SQL ?

[oliver_ried]

Hi,

I don't want to write and test a program in ABAP to update the field LGORT in the table RESB, so I have decided to update this field directly with an SQL-update:

update sapc12.resb set lgort='6602'

where mandt='100' and rsnum='0000006321'

and substr(matnr,1,1)='F' and werks='0066'

It works, and the production order (CO03) looks perfectly valid after the update.

I know that it is not recommended to update SAP-tables directly with SQL, but I think it makes no problem in this special case.

I want to be sure not to crash our SAP-system, thus I want to ask this forum whether this approach is valid.

Could something goes wrong when I update the field LGORT in the table RESB with SQL ?

Kind regards

Oliver

[Former Member]

Why do you think using SQL to do a direct update would be any better than ABAP?

Rob

[oliver_ried]

It saves me a lot of development time.

To write and test an ABAP I need one or two days.

To write a SQL-script I need 30 minutes (or less).

Oliver

[Former Member]

So... no testing with the SQL??

Rob

[oliver_ried]

With ABAP I would use a batch-input-routine within my ABAP-code, and to work with batch-inputs is time-consuming, because I want to update only selected entries in the RESB, so I have to check every part list position whether it includes the correct material number or not.

With SQL I would combine the update-statement with a select-statement to update the RESB.

In our old ERP-system I have changed a lot of data with SQL, but in SAP we should update data only with batch-inputs.

Testing with SQL? No, not really. Either the field is updated or not. I must test the production orders in SAP after the update.

Oliver

[Former Member]

>

> With ABAP I would use a batch-input-routine within my ABAP-code, and to work with batch-inputs is time-consuming, 

You can do direct table updates using ABAP. There would be no difference.

However, with ABAP, there is at least an audit trail of what happened.

You don't necessarily need to use BDC. There may be a BAPI to help you out.

In the end, you cannot really be sure when updating tables directly - particularly ones used in complex transactions.

It's a classic case of being penny wise but pound foolish.

Rob

[Former Member]

I was initially thinking you were doing SQVI/SQ01, then realized u want to update this directly in database...You are probably setting yourself up for SOX nightmare...

Aside from the debate of which is the right place to make this change, you need to really make sure that there are no process behind the LGORT field in the RESB table?

You may need to check this by changing just this field in the existing production order via std. transaction (and run a SQL trace to validate if SAP is not updating any other tables)... If SAP is updating some other tables or fields via std. transaction, you have to ensure all those fields are also updated in your sql query. even then there is no guarantee that you have covered everything (and SAP may not be ready to support if there are problems later)

[oliver_ried]

I have already started an SQL-trace with ST05, changed the LGORT with CO02 and then searched through the SQL-trace-statements.

There are some updates, but the value of the LGORT was only within the RESB-update.

I have not noticed any dependencies between the RESB-LGORT and other tables.

Oliver

[Former Member]

Hi

The approach is not valid of course........

but that doesn't mean it can't do it, but you make sure any incosistency in the database will be created.

Sometimes I've changed the values by sql, but before doing it I've studied the transaction in order to understand wich tables have to be updated if that value is changed.

So if you are sure it's enough to change the storage location in RESB table you can do it.....but under your responsibilty

Max

[oliver_ried]

> In the end, you cannot really be sure when updating tables directly

Rob, that's absolutely correct. It seems, it is my decision at my own risk.

In ABAP I can only use the SQL-dialect from SAP but not the complete syntax of Oracle-SQL, isn't it?

With pure Oracle-SQL I have much more possibilities than with ABAP-SQL.

Oliver

[Former Member]

In ABAP I can only use the SQL-dialect from SAP but not the complete syntax of Oracle-SQL, isn't it?
With pure Oracle-SQL I have much more possibilities than with ABAP-SQL.

Yes your're right.

There isn't any difference to write an SQL statament via ABAP or via Oracle, but just as you said a oracle-sql is powerfull than an abap one.

Just only remember you can also write a NATIVE SQL statament in an abap program.

Max

[Former Member]

You also have to bear in mind that this is an ABAP forum. Would you go into your local BMW dealer and ask if you should buy a Porsche?

Rob

[ThomasZloch]

>

> > In the end, you cannot really be sure when updating tables directly
> Rob, that's absolutely correct. It seems, it is my decision at my own risk.
> 
> In ABAP I can only use the SQL-dialect from SAP but not the complete syntax of Oracle-SQL, isn't it?
> 
> With pure Oracle-SQL I have much more possibilities than with ABAP-SQL.
> 
> Oliver

Are you more Oracle DBA or more ABAP developer? I would assume the former.

ABAP SQL has many additional features like IN operators or FOR ALL ENTRIES that are automatically translated to native SQL statements via the DB-Interface. It is tailormade for programming robust and DB-platform independent business applications.

Just what the doctor ordered, from my developer and non-DBA point of view.

Thomas

[Clemenss]

Hi Oliver,

>

> I want to be sure not to crash our SAP-system, thus I want to ask this forum whether this approach is valid.

You will never get SAP to to agree upon updating a standard SAP table directly - regardless of how you do it.

Because RESB is updated by SAP standard transactions, it will be difficult if possible to know all secondary effects a RESB change would usually have.

Also, the LGORT change you want to do, is a business requirement. I'd assume this is may be the consequence of a transport request. Please be aware that your planed change will cause database inconsistency.

The absolute minimum to do here (also regarding quality inspection) is to record the changes as old and new value per changed record.

You may ask your financial guys if there is anything to expect - i.e. regarding material valuation. If you have WM, what about that?

If you use SAP standard transaction, you have 99.9 % safety that everything will work as desired - otherwise it works as designed.

Regards,

Clemens

[oliver_ried]

> You will never get SAP to agree upon updating a standard SAP table directly - regardless of how you do it.

I thought as much!

> If you use SAP standard transaction, you have 99.9 % safety that everything will work as desired - otherwise it works as designed.

Yes, I know, but the work to write ABAPs with batch-inputs is awkward. To use SQL is so much cooler and faster. I love it to code SQL-statements!

> You also have to bear in mind that this is an ABAP forum. Would you go into your local BMW dealer and ask if you should buy a Porsche?

I would give it a try.

> Are you more Oracle DBA or more ABAP developer?

Neither Oracle DBA nor ABAP developer. In the first instance I am a enthusiastic Java developer with a little ABAP background.

My experience is: Java developer don't want to write code in ABAP, and ABAP developer hate Java. The two languages are too different.

Oliver

[Clemenss]

Hi Oliver,

>

> Yes, I know, but the work to write ABAPs with batch-inputs is awkward. To use SQL is so much cooler and faster. I love it to code SQL-statements!
> Oliver

I can understand you. You will not win any merits for creating a batch-input, but you could by creating a report using BAPIS and and application log.

And, honestly, is this task a real challenge for your SQL skills?

Regards,

Clemens

[oliver_ried]

> Is this task a real challenge for your SQL skills?

No, it's not. Creating a report using BAPIS and an application log would be nice, but I need this update-routine only once, and with SQL I can solve the problem very quick.

[Former Member]

As I said earlier, this doesn't leave much of an audit trail. You should run your plan by the auditors or at least your managers.

Rob