topic Re: Validating Duplicate Entries In itab in Application Development and Automation Discussions

Validating Duplicate Entries In itab

pradiptakumar_mishra — Tue, 23 May 2006 09:18:33 GMT

Hi All

How to approach for restricting duplicate entries in the Database?

The scene is that I've to enter some data in a table control . and while saving, I've to check whether the same entry already exists in the database or not.

How to approach for this? Awaiting your valuable response.

Thanks in advance

Pradipta

Re: Validating Duplicate Entries In itab

Former Member — Tue, 23 May 2006 09:21:47 GMT

Hi,

1. Before saving you can fire a select statement to see if there a entry with the same values

2. Get all the existing values from the DB into a internal table and check against the internal table the rows in the table control.

Regards,

Ravi

Note : Please mark all the helpful answers

Re: Validating Duplicate Entries In itab

Former Member — Tue, 23 May 2006 09:23:01 GMT

While updating, check in the database using a select single statement.

loop at itab.

select single *

from ztab

where keyfield1 = itab-field1

and key-field2 = itab-field2.

if sy-subrc <> 0.

insert ztab from itab.

endif.

endloop.

where itab is the internal table that has the records in the table control.

or simply use modify statement

modify ztab from wa. (Modifies the record if already exists or creates an entry if no entry exists).

Regards,

Ravi

Re: Validating Duplicate Entries In itab

Former Member — Tue, 23 May 2006 09:23:59 GMT

Use 'Modify' stmt instead of 'INSERT'.

Re: Validating Duplicate Entries In itab

Former Member — Tue, 23 May 2006 09:24:24 GMT

Hi pradipta,

1. We have to check with SELECT statement only.

2. like this :

Just copy paste in new program.

report abc.

*----

data : itab like t001.

select single * from t001

into itab

where bukrs = '1333'.

*----

if sy-subrc = 0.

write 😕 'record exits'.

else.

write 😕 'record does notexits'.

endif.

regards,

amit m.

Re: Validating Duplicate Entries In itab

Former Member — Tue, 23 May 2006 09:27:33 GMT

Hi Pradipta

for this u have to carry out individual checks for each field before updating the database

for eg

check not <db fname> = itab-fname and ....

modify database.

Thanks

Re: Validating Duplicate Entries In itab

Former Member — Tue, 23 May 2006 09:28:10 GMT

Hi,

I think you can check for the primary field in that particular record using the SELECT statement.if the record already exist, then delete the duplicate entries. or you can use

DELETE ADJACENT DUPLICATES FROM ITAB COMPARING <primary field>.

This will delete all the duplicate entries based on that field.

Regards,

Aswin

Re: Validating Duplicate Entries In itab

Former Member — Tue, 23 May 2006 09:32:23 GMT

Hai Mishra

DELETE ADJACENT DUPLICATES FROM itab.

Additions

1. ... COMPARING f1 f2 ...

2. ... COMPARING ALL FIELDS

Effect

Deletes neighboring, duplicate entries from the internal table itab . If there are n duplicate entries, the first entry is retained and the other n - 1 entries are deleted.

Two lines are considered to be duplicated if their default keys match.

The return code value is set as follows:

SY-SUBRC = 0 At least one duplicate exists, at least one entry deleted.

SY_SUBRC = 4 No duplicates exist, no entry deleted.

Addition 1

... COMPARING f1 f2 ...

Effect

Two lines of the internal table itab are considered to be duplicates if the specified fields f1 , f2 , .... match.

Addition 2

... COMPARING ALL FIELDS

Effect

Two lines are considered to be duplicates if all fields of the table entries match.

Notes

The DELETE ADJACENT DUPLICATES statement is especially useful if the internal table itab is sorted by fields (whether in ascending or descending order) which were compared during duplicate determination. In this case, the deletion of neighbouring duplicates is the same as the deletion of all duplicates.

If a comparison criterion is only known at runtime, it can be specified dynamically as the content of a field name by using COMPARING ... (name) ... . If name is blank at runtime, the comparison criterion is ignored. If name contains an invalid component name, a runtime error occurs.

Comparison criteria - statistically or dynamically specified - can be further restriced by specifying the offset and/or length.

Note

Performance

Deleting a line from an internal table incurs index maintenance costs which depend on the index of the line to be deleted. The runtime depends on the line width of the table.

For example, deleting a line in the middle of an internal table with 200 entries requires about 10 msn (standardized microseconds).

Deleting a range of entries with " DELETE itab FROM idx1 TO idx2. " deleting a set of entries with " DELETE itab WHERE ... " only incur index maintenance costs once. Compared with a LOOP , which deletes line-by-line, this is much faster.

To delete neighboring, duplicate entries from an internal table, use the variant " DELETE ADJACENT DUPLICATES FROM itab. " instead of LOOP constructions.

Thanks & regards

Sreeni

Re: Validating Duplicate Entries In itab

pradiptakumar_mishra — Tue, 23 May 2006 09:32:59 GMT

Dear Ravi,

Thanks. I've used that method. Am fetching all the records based on the key fields to another itab. Then am looping the table control itab and then inside that am reading the itab of the dbtab.

But am not getting the desired output.

Can you pls guess wht might be the error ?

Am sending a portion of my logic.

SELECT bukrs hbkid hktid instrno instrtp waers kunnr

FROM yfcoll

INTO CORRESPONDING FIELDS OF TABLE it_yfcoll

WHERE bukrs = sel_screen-bukrs

AND hbkid = sel_screen-hbkid

AND hktid = sel_screen-hktid.

LOOP AT it_control INTO wa_control.

READ TABLE it_yfcoll INTO wa_yfcoll

WITH KEY bukrs = sel_screen-bukrs

hbkid = sel_screen-hbkid

hktid = sel_screen-hktid

kunnr = table_control-kunnr

instrno = table_control-instrno

instrtp = table_control-instrtp

waers = table_control-wrbtr

BINARY SEARCH.

IF sy-subrc = 0 .

wa_control-sel = 'X'.

MODIFY it_control FROM wa_control TRANSPORTING sel.

'Entered Record Already Exists'

wa_control-kunnr

wa_control-instrno

wa_control-instrtp.

ELSE.

CLEAR l_check.

l_check = 'X'.

wa_control-sel = ' '.

MODIFY it_control FROM wa_control TRANSPORTING sel.

ENDIF.

ENDLOOP.

Thanks in advance

Pradipta

Re: Validating Duplicate Entries In itab

Former Member — Tue, 23 May 2006 09:33:35 GMT

Hi Pradipta,

Select within loop will not be a good option. It will be a performance issue.

Use the following code instead to check for the duplicate entries.

If not itab_table_data[] is initial.
	select field1 field2
        from ztab
        into table lit_itab
        for all entries in itab_table_data
        where field1 = itab_table_data-field1.
Endif.

sort lit_itab by field1 field2.

Loop at itab_table_data into wa_table_data.
	Read table lit_itab into wa_itab
        with key field1 = wa_table_data-field1
                 field2 = wa_table_data-field2
        Binary search.
	if sy-subrc eq 0.
*		Show error message or do whatever you want 
        Endif.
Endloop.

Reward points if it helps.

Regards,

Amit Mishra

Re: Validating Duplicate Entries In itab

pradiptakumar_mishra — Tue, 23 May 2006 09:36:17 GMT

Dear All,

Thanks.

As using a SELECT stmt within loop-endloop is a performance issue. So I can't go ahead with that. Again the scenario is that I've to give an error message so that the user will modify or delete the entry.

This is where am strucked up.

With Rgds

Pradipta

Re: Validating Duplicate Entries In itab

rahulkavuri — Tue, 23 May 2006 09:36:28 GMT

hi for performance issues instead of looping while entering the data, sort the table according to the primary keys and delete duplicate entries

delete adjacent duplicates comparing primarykey

Re: Validating Duplicate Entries In itab

Former Member — Tue, 23 May 2006 09:41:56 GMT

Hi,

Well that should have worked for you.

Where exactly is this code, PAI / PBO?

What is the error?

Regards,

Ravi

Re: Validating Duplicate Entries In itab

pradiptakumar_mishra — Tue, 23 May 2006 09:59:19 GMT

Its in PAI.

Re: Validating Duplicate Entries In itab

athavanraja — Tue, 23 May 2006 10:19:26 GMT

just declare an itab of type your database table.

data: itab type standard table of zmytable .

fill the values to itab.

now simply use

Modify as suggested by Vijay

MODIFY zmytable FROM TABLE itab .

this will

insert new entry - if there is no entry exists in DB table

modify existing entry - if there is a record with simlar key in DB table it will modify the record

this is like INSERT with accepting duplicate entries .

both modify and insert with accepting duplicate entries checks for duplicate primary keys.

Regards

Raja