03-05-2013 2:45 PM
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
03-05-2013 3:24 PM
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
03-05-2013 3:24 PM
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
03-05-2013 4:19 PM
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
03-05-2013 10:46 PM
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
03-06-2013 3:29 AM
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.
03-07-2013 10:12 AM
03-07-2013 10:21 AM
03-05-2013 4:45 PM
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
03-05-2013 5:58 PM
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
03-05-2013 6:09 PM
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
03-06-2013 10:27 AM
03-06-2013 2:11 PM
Thanks All. I just need a confirmation before i strted writing the code