topic Re: Modify dbtab Statement WIth Check Table in Application Development and Automation Discussions

Modify dbtab Statement WIth Check Table

Former Member — Tue, 05 Mar 2013 14:45:01 GMT

Hi All,

I have 2 tables say

EMP_ID Table with fields ID and Name and i have EMP_PAY Table with 2 fields ID and Pay (In both the Tables my ID is Primary key)

In order to have entry with ID and Pay in EMP_PAY , i need to have an entry EMP_ID Table with ID and Name so i add check table EMP_ID to ID field in EMP_PAY table and it works fine when i try to create entries directly using SE 11

But i have written a small program using Modify Statement to insert a record in EMP_PAY with an ID that doesn't exist in EMP_ID ( From SE11 it gives me an error saying record doesn't exist in EMP_ID Table but with modify statement in abap program it creates and entry and returns SY-subrc eq 0 and SY-DBCNT = 1

I have 2 files that user uploads ,if he uploads the EMP_PAY file first it should update EMP_PAY table only if the the records exist in EMP_PAY and EMP_ID and do an insert if it ID exists in EMP_ID table and doesn't exist in EMP_PAY. To cover these 2 bases i have used Modify Statement but its not working as i intented.

I've an internal table that needs to Update EMP_PAY , is there any simple way to tell me what are rejected records because of no correponding entry in EMP_ID Table

Thanks

Re: Modify dbtab Statement WIth Check Table

Former Member — Tue, 05 Mar 2013 15:24:47 GMT

Venkat,

In order to have the code optimized for performance, the normal tendency is to use the internal table to update/insert records. This way we hit the DB just once instead of writing insert/modify inside a loop. This is the best approach. Unfortunately this will not tell you what records were successfully updated and what not.

the only to check this is to perform a select on the DB table against the records in your internal table after the insert is done. Then loop thru the records and compare.

Thanks,

Vikram.M

Re: Modify dbtab Statement WIth Check Table

Former Member — Tue, 05 Mar 2013 16:19:17 GMT

Hi Vikram,

As you said it works if i'm updating the Table if the entry with primary exists .

Let says if the records doesn't exist then i have to insert the record if the id exists in EMP_ID table (WHich is the check table for ID field of EMP_PAY Table) . Again this is much simple example , i have 10 check tables for 10 different columns in a table , so i have to take each field and get the check table and see that records exists in the check table .. its a tedious job

I thought when SAP does this check for you when you're creating entries from SE11 why not from Program

Thanks

Venkat

Re: Modify dbtab Statement WIth Check Table

Former Member — Tue, 05 Mar 2013 16:45:13 GMT

Hi Venkat,

Are declaring EMP_ID as a foreign key?

Please see https://help.sap.com/saphelp_nw04/helpdata/en/cf/21ea77446011d189700000e8322d00/content.htm for documentation.

Regards,

Steve

Re: Modify dbtab Statement WIth Check Table

Former Member — Tue, 05 Mar 2013 17:58:26 GMT

Hi Steve ,

I understand the concept of Foregin and Check Table

I have maintained it the check table and foregin key in SE11 and it works fine when i try to create an entry in EMP_PAY, ID in EMP_PAY will need t have an entry in EMP_ID Table - This works when i create entry in table using SE11

When i insert a new record using SE38 Program iusing Modify Statement it won;t check the check table

Say My EMP_ID has following records

ID Name

1 abc

2 def

Assume EMP_PAY is Blank

and if i try to create a record using SE11 Like

ID 3

Pay 10 , it won't let me save that record because ID = 3 doesn't exist in EMP_ID Table and i get an error message

Now say i do the same using an ABAP Program and my internal table itab has record

id pay

3 10

Nowi say MODIFY EMP_PAY FROM ITAB. (It returns SY-SUBRC EQ 0 and SY-DBCNT = 1) AND When i check the EMP_PAY Table it has 1 new record 3 and 10 and still ID =3 Doesn't exist in EMP_ID

Is there anything in SAP which check the corresponding check Table if a field has one

If you see the Foreign Check Table Link in SE11 -It says Check Table Box checked on screen

Venkat

Re: Modify dbtab Statement WIth Check Table

RaymondGiuseppi — Tue, 05 Mar 2013 18:09:31 GMT

The MODIFY statement will only take care of INSERT or UPDATE if record exists already in target table. But it will not take any care of foreign keys constraints. You have to code this contraint yourself as SAP do in generated SE11 report or SM30 dialog maintenance.

So, read from file data to be updated or inserted. Then check for constraints (eg. SELECT from foreign_key_table(s) FOR ALL ENTRIES IN table_to_update, then remove records without match in the new extracted table, and use INSERT/UPDATE or MODIFY statement on the resulting internal table.

Regards,

Raymond

Re: Modify dbtab Statement WIth Check Table

Former Member — Tue, 05 Mar 2013 22:46:27 GMT

Venkat, This is cuz the statement MODIFY does not check for foreign key constraints but INSERT does. If you are always expecting records to be inserted, just go for insert than MODIFY. Otherwise you first need to check if the key exists and then based on that decide whether to use modify or insert.

Thanks,

Vikram.M

Re: Modify dbtab Statement WIth Check Table

kakshat — Wed, 06 Mar 2013 03:29:48 GMT

Hi Vikram,

I don't think even INSERT checks for foreign key constraints. Whether you are inserting or updating (or modifying) records programmatically, the foreign key constraints won't be enforced.

Re: Modify dbtab Statement WIth Check Table

vigneshyeram — Wed, 06 Mar 2013 10:27:06 GMT

Dear Venkat,

I agree to and .

Foreign key is not checked by INSERT MODIFY and UPDATE.

You will have write in the program Select from check table to check if the entry present then INSERT or MODIFY.

Thanks & Regards,

Vignesh Yeram

Re: Modify dbtab Statement WIth Check Table

Former Member — Wed, 06 Mar 2013 14:11:11 GMT

Thanks All. I just need a confirmation before i strted writing the code

Re: Modify dbtab Statement WIth Check Table

Former Member — Thu, 07 Mar 2013 10:12:31 GMT

Thanks Kumar.

Re: Modify dbtab Statement WIth Check Table

kakshat — Thu, 07 Mar 2013 10:21:21 GMT

No problem!