05-23-2006 10:18 AM
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
05-23-2006 10:21 AM
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
05-23-2006 10:23 AM
05-23-2006 10:32 AM
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
05-23-2006 10:36 AM
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
05-23-2006 10:41 AM
Hi,
Well that should have worked for you.
Where exactly is this code, PAI / PBO?
What is the error?
Regards,
Ravi
05-23-2006 10:59 AM
05-23-2006 11:19 AM
just declare an itab of type your database table.
data: itab type standard table of zmytable .
fill the values to itab.
now simply use
<b>Modify</b> as suggested by Vijay
<b>MODIFY zmytable FROM TABLE itab .</b>
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 <b>INSERT</b> with <b>accepting duplicate entries .</b>
both <b>modify</b> and <b>insert with accepting duplicate entries</b> checks for duplicate primary keys.
Regards
Raja
05-23-2006 10:23 AM
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
05-23-2006 10:24 AM
Hi pradipta,
1. We have to check with SELECT statement only.
2. like this :
Just copy paste in new program.
3.
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.
05-23-2006 10:27 AM
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
05-23-2006 10:28 AM
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
05-23-2006 10:32 AM
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
05-23-2006 10:33 AM
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.
<b>Reward points if it helps.</b>
Regards,
Amit Mishra
05-23-2006 10:36 AM
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