topic Re: Modify a table with a secondry index in Application Development and Automation Discussions

Modify a table with a secondry index

Former Member — Thu, 25 Jul 2013 12:32:43 GMT

Hallo Dear folk,

I'm currently reading the book "Abap objects" of Hosrt Keller and Sascha Krüger. I have a question regarding secondary indices and the abap

statement "modify". First, let me please mention to you what is writte on page 807 (German version of the book):

....

MODIFY dbtab FROM TABLE itab.

.....

But even with MODIFY it could happen, that a line cannot be proccessed. For example can a line in the database table exist, with the same unique secondary index ."

I was woundering:

ist possible o to use secondary index along with the MODIFY statement? say like that (obviously this statement didn't compile):

modify znames from lw_name where NACGNAME = lw_name-NACGNAME

%_HINTS ORACLE 'INDEX("ZNAMES" "ZNAMES~001")'.

where the table 'znames' have the primary key field 'NAME'. The secondary index has the field 'NACGNAME'.

I'm simply trying to write a simple program which would explain what was mentioned in the book.

Hope for your help

Nido

Re: Modify a table with a secondry index

Former Member — Thu, 25 Jul 2013 12:57:10 GMT

Hi Nido,

You could use the below link which gives you more details of it.

It's simple to use secondary index but not recommended.

Revert if you find more questions.

http://scn.sap.com/message/4767495

Thanks,

Dinesh.

Re: Modify a table with a secondry index

Former Member — Thu, 25 Jul 2013 13:00:28 GMT

The explanation is present in ABAP keyword documentation of Modify statement.

http://help.sap.com/abapdocu_702/en/abapmodify_dbtab.htm

Sy-subrc 4: When a work area was specified in source, no rows were processed, or when an internal table was specified in source, not all specified rows were processed because there is already a row with the same unique secondary index in the database table.

Modify statement can change non-key fields. Modify statement may fail with sy-subrc value 4 if field being changed is part of unique secondary index.

Try removing the Oracle hints from Modify statement and try changing field NACGNAME after making it part of unique secondary index.

Re: Modify a table with a secondry index

Former Member — Thu, 25 Jul 2013 13:31:51 GMT

Hello,

There are a couple of things that are going on with the query that you have posted.

1) MODIFY dbtab FROM TABLE itab.

this is a generic staement which will update db table using values from itab.

2) modify znames from lw_name where NACGNAME = lw_name-NACGNAME

%_HINTS ORACLE 'INDEX("ZNAMES" "ZNAMES~001")'.

Here you are trying to update Db using lw_name. I guess the field lw_name is a work area here and along with that you are trying to use a secondary index. This is just a trial that are doing to test secondary index while updating a Db. Well my suggestion in real life situation would be that if this Db table is not that big and or the use of secondary index is not that much then do not use the decondary index.

Check syntax for table key use

http://help.sap.com/abapdocu_702/en/abapmodify_itab_single.htm

http://help.sap.com/abapdocu_702/en/abapmodify_itab_index.htm

Please see this note from the SAP help of modify

The following limitations apply when modifying key fields of theprimary and secondary table keys:

The key fields of the primary table key ofsorted tables andhashed tables are read-only and must not be modified. This would lead to the invalidation of the internal table administration and attempts to do so usually raise non-handleable exceptions.
The key fields of a secondary table key, however, are only read-only while the secondary table is being used. This is the case in LOOP loops and during the use of the MODIFY statement, in which the secondary key is specified after USING KEY. Otherwise the key fields are not read-only.

Please go through this link for use of secondary index.

http://help.sap.com/abapdocu_702/en/abenindices.htm

Modify statement

http://help.sap.com/abapdocu_702/en/abapmodify_itab.htm

best regards,

swanand

Re: Modify a table with a secondry index

Former Member — Thu, 25 Jul 2013 16:07:18 GMT

Hallo Manish,

I have experimented following your explanation.

I have created a table named "zadresses" with the primary key "Name"

NAME NACHNAME STADT

CARINA INGENDAE REMSCHEID

EMILIE SAL Duisburg

Emilie Salorte Duisburg

NIDAL ZIDAN HEIDELBERG

RUZICA DJENADIC DARMSTADT

salorte SALORTE Duisburg

I have created the uniques secondary index '001' with the fields "NACHNAME"(lastname) and "STADT" (city).

With the entries above already exist, I have written the following program:

data: lw_Address type zadresses.

start-of-selection.

lw_Address-name = 'CARINA'.

lw_Address-nachname = 'INGENDAE'.

lw_Address-stadt = 'Paris'.

Modify zadresses from lw_Address.

I would have expected that the program wouldn't be able to modify the first line of the above table (Value 'Paris' for column 'Stadt'), because based on the mentioned explanation, the field 'Stadt' is a part of the secondary index! sy-subrc was 0 and sy-dbcnt was 1 !!!!

do I understand something wrong here! Please give me your hint!

Re: Modify a table with a secondry index

Former Member — Thu, 25 Jul 2013 16:43:11 GMT

Please see the attached secondary index properties.

Re: Modify a table with a secondry index

Former Member — Thu, 25 Jul 2013 18:06:26 GMT

The Modify statement will fail if there is already a row with same unique secondary index.

When you changed (INGENDAE,REMSCHEID) to (INGENDAE,Paris) for first record, new secondary index (INGENDAE,Paris) is still unique, not existing for any other record before your statement.

If you try to change 2nd record (SAL,Duisburg) to (Salorte,Duisburg), Modify statement should fail because there is already a record (record 3) whose secondary key is (Salorte,Duisburg).

This is required to maintain the uniqueness of secondary index.

Re: Modify a table with a secondry index

Former Member — Thu, 25 Jul 2013 18:32:14 GMT

...

many thanks, you are very right and now I have sy-subrc = 4.