Deleting Duplicate entries existing in dynamic table
Hello,
Every table has key field(s).After patches up or upgrade happened som tables have duplicate entries with key fields. generally this should not happen in general case. In some cases this type of situation will occur.
So I created one program for this requirement. Selection screen : Table name.Tbale entries will be delete /download .
*&———————————————————————*
*& Report Z_TAB_DUPL_ENT_DELETION
*&
*&———————————————————————*
*&
*&
*&———————————————————————*
REPORT z_tab_dupl_ent_deletion.
TABLES : rsrd1.
TYPE-POOLS : abap, slis.
FIELD-SYMBOLS: <fs_table> TYPE STANDARD TABLE,”occurs 0 with header line,
<fs_table_temp> TYPE STANDARD TABLE,
<fs_table_final> TYPE STANDARD TABLE,
<w_dref>,
<w_dref1>,
<w_dref_final>,
<f1>,
<f2>.
DATA: wa_fieldcat TYPE lvc_s_fcat,
it_fieldcat TYPE lvc_t_fcat.
DATA : w_dref TYPE REF TO data,
w_dref1 TYPE REF TO data,
w_dref_final TYPE REF TO data.
TYPES :BEGIN OF ty_head,
text(60) TYPE c,
END OF ty_head.
DATA : it_head TYPE TABLE OF ty_head,
wa_head LIKE LINE OF it_head.
*ALV data declarations
DATA: fieldcatalog TYPE slis_t_fieldcat_alv WITH HEADER LINE,
gd_layout TYPE slis_layout_alv,
gd_repid LIKE sy-repid.
DATA : i_ddfields TYPE STANDARD TABLE OF dntab .
DATA : count TYPE i,
count_n TYPE i,
l_lines TYPE i,
l_lines_temp TYPE i,
gl_file TYPE string,
l_check.
DATA : p_table TYPE char30.
DATA : t_table TYPE STANDARD TABLE OF string,
wa TYPE string.
SELECTION-SCREEN BEGIN OF BLOCK block1 WITH FRAME.
SELECT-OPTIONS : s_table FOR rsrd1-tbma_val NO INTERVALS .” default ‘SFLIGHT’.
*PARAMETERS : P_PSFILELIKE rlgrap-filename DEFAULT ‘c:\TEMP\’.
PARAMETERS : p_test AS CHECKBOX.
SELECTION-SCREEN END OF BLOCK block1.
SELECTION-SCREEN BEGIN OF BLOCK block WITH FRAME TITLE text-011.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT 1(26) text-s01 MODIF ID m3.
PARAMETERS: p_ps RADIOBUTTON GROUP serv USER-COMMAND m1
MODIF ID m3.
SELECTION-SCREEN POSITION 33.
PARAMETERS: p_psfile TYPE file MODIF ID m1
DEFAULT ‘C:\temp\’. “#EC NOTEXT
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT 1(26) text-s02 MODIF ID m1.
PARAMETERS: p_as RADIOBUTTON GROUP serv MODIF ID m1
DEFAULT ‘X’.
SELECTION-SCREEN POSITION 33.
PARAMETERS: p_asfile type file MODIF ID m2
DEFAULT ‘/tmp’..
PARAMETERS: p_post TYPE xfeld NO-DISPLAY.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN END OF BLOCK block.
AT SELECTION-SCREEN OUTPUT.
LOOP AT SCREEN.
IF screen-name = ‘P_FILE’.
screen-input = 0.
MODIFY SCREEN.
ENDIF.
ENDLOOP.
**********************************************************8
************************************************************************
* A T S E L E C T I O N – S C R E E N O U T P U T *
************************************************************************
*AT SELECTION-SCREEN OUTPUT.
*based on user selection of presenation server/application server
*enable input for respective file path
* PERFORM enable_file_input.
*AT SELECTION SCREEN EVENTS for file validation.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_psfile.
*subroutine to read user entered file path details
PERFORM read_dynpro_content USING ‘P_PSFILE’
CHANGING p_psfile.
*subroutine to display file dailog for user,
*to download data to local file
PERFORM get_file_name_ps USING p_ps
CHANGING p_psfile.
AT SELECTION-SCREEN ON p_psfile.
*subroutine to check, user entered file path exists or not
IF NOT p_ps IS INITIAL.
PERFORM check_file_exist USING p_psfile
‘P_PSFILE’.
ENDIF.
***********************************************************************
*start-of-selection.
START-OF-SELECTION.
LOOP AT s_table.
p_table = s_table-low.
PERFORM get_table_structure.
CLEAR : w_dref.
PERFORM create_itab_dynamically.
PERFORM get_data.
ENDLOOP.
*&———————————————————————*
*& Form get_table_structure
*&———————————————————————*
* Get structure of an SAP table
*———————————————————————-*
FORM get_table_structure.
DATA:l_iname TYPE tabname,
wa_ddifields TYPE dntab.
REFRESH : t_table,
i_ddfields,
it_fieldcat,
it_head.
l_iname = p_table.
CALL FUNCTION ‘NAMETAB_GET’
EXPORTING
langu = sy-langu
tabname = l_iname
TABLES
nametab = i_ddfields.
LOOP AT i_ddfields INTO wa_ddifields.
CLEAR wa_fieldcat.
wa_fieldcat-fieldname = wa_ddifields-fieldname .
wa_fieldcat-datatype = wa_ddifields-datatype.
wa_fieldcat-inttype = wa_ddifields-inttype.
wa_fieldcat-intlen = wa_ddifields-intlen.
wa_fieldcat-decimals = wa_ddifields-decimals.
IF wa_ddifields-keyflag = ‘X’.
APPEND wa_fieldcat TO it_fieldcat.
wa = wa_fieldcat-fieldname.
APPEND wa TO t_table.
CLEAR : wa.
ENDIF.
APPEND wa_fieldcat TO it_fieldcat.
wa_head-text = wa_ddifields-fieldtext.
APPEND wa_head TO it_head.
CLEAR : wa_head,wa_ddifields.
ENDLOOP.
ENDFORM. “get_table_structure
*&———————————————————————*
*& Form create_itab_dynamically
*&———————————————————————*
* Create internal table dynamically
*———————————————————————-*
FORM create_itab_dynamically.
CREATE DATA w_dref TYPE TABLE OF (p_table).
CREATE DATA w_dref1 TYPE TABLE OF (p_table).
CREATE DATA w_dref_final TYPE TABLE OF (p_table).
ASSIGN w_dref->* TO <fs_table>.
ASSIGN w_dref1->* TO <fs_table_temp>.
ASSIGN w_dref_final->* TO <fs_table_final>.
ENDFORM. “create_itab_dynamically
*&———————————————————————*
*& Form get_data
*&———————————————————————*
* Populate dynamic itab
*———————————————————————-*
FORM get_data.
DATA : lv_flag,
l_file TYPE file.
*** Select Data from table using field symbol which points to dynamic itab
SELECT * FROM (p_table) INTO TABLE <fs_table>.
if sy-subrc eq 0.
<fs_table_temp> = <fs_table>.
DESCRIBE TABLE <fs_table> LINES l_lines.
SORT <fs_table>.
SORT <fs_table_temp>.
DELETE ADJACENT DUPLICATES FROM <fs_table_temp> COMPARING ALL FIELDS.
DESCRIBE TABLE <fs_table_temp> LINES l_lines_temp.
* IF l_lines_temp EQ l_lines.
LOOP AT <fs_table_temp> ASSIGNING <w_dref1>.
CLEAR : count, count_n.
LOOP AT <fs_table> ASSIGNING <w_dref>.
CLEAR : wa,l_check.
LOOP AT t_table INTO wa.
ASSIGN COMPONENT wa OF STRUCTURE <w_dref1> TO <f1>.
ASSIGN COMPONENT wa OF STRUCTURE <w_dref> TO <f2>.
IF <f1> = <f2>.
l_check = ‘X’.
ELSE.
CLEAR : l_check.
EXIT.
ENDIF.
ENDLOOP.
* IF <w_dref1> = <w_dref>.
IF l_check EQ ‘X’.
count = count + 1.
ELSE.
IF count > 1.
EXIT.
ELSE.
count_n = count_n + 1.
EXIT.
ENDIF.
ENDIF.
* IF count > 1.
* EXIT.
* ENDIF.
ENDLOOP.
IF count_n = 1.
DELETE TABLE <fs_table> FROM <w_dref1>.
ENDIF.
IF count > 1.
APPEND <w_dref1> TO <fs_table_final> .
DO count TIMES.
DELETE TABLE <fs_table> FROM <w_dref1>.”Hemendra
ENDDO.
ENDIF.
ENDLOOP.
* ENDIF.
IF <fs_table_final> IS NOT INITIAL.
IF p_ps = ‘X’.
CONCATENATE p_psfile p_table ‘.XLS’ INTO gl_file.
*** download to excel
CALL FUNCTION ‘GUI_DOWNLOAD’
EXPORTING
* BIN_FILESIZE =
filename = gl_file
filetype = ‘DAT’
* APPEND = ‘X’
* WRITE_FIELD_SEPARATOR = ‘ ‘
header = ’00’
replacement = ‘#’
TABLES
data_tab = <fs_table_final>
fieldnames = it_head.
ELSEIF p_as = ‘X’.
CLEAR : l_file.
l_file = p_asfile.
CONCATENATE l_file ‘/’ p_table ‘.txt’ INTO l_file.
OPEN DATASET l_file FOR OUTPUT
IN TEXT MODE ENCODING DEFAULT.
IF sy-subrc EQ 0.
LOOP AT <fs_table_final> ASSIGNING <w_dref_final>.
TRANSFER <w_dref_final> TO l_file.
ENDLOOP.
CLOSE DATASET l_file.
ELSE.
MESSAGE e002(fb) WITH l_file.
ENDIF.
ENDIF.
IF p_test IS INITIAL.
CALL FUNCTION ‘ENQUEUE_E_TABLE’
EXPORTING
mode_rstable = ‘E’
tabname = p_table
EXCEPTIONS
foreign_lock = 1
system_failure = 2
OTHERS = 3.
IF sy-subrc <> 0.
MESSAGE e001(00) WITH text-001.
ELSE.
LOOP AT <fs_table_final> ASSIGNING <w_dref_final>.
DELETE (p_table) FROM <w_dref_final>.
IF sy-subrc NE 0.
lv_flag = ‘X’.
EXIT.
ENDIF.
ENDLOOP.
IF lv_flag = ‘X’.
ROLLBACK WORK.
ELSE.
COMMIT WORK AND WAIT.
IF sy-subrc EQ 0.
LOOP AT <fs_table_final> ASSIGNING <w_dref_final>.
INSERT (p_table) FROM <w_dref_final>.
ENDLOOP.
ENDIF.
ENDIF.
* MODIFY (p_table) FROM TABLE <fs_table_final>.
” Tabelle entsperren
CALL FUNCTION ‘DEQUEUE_E_TABLE’
EXPORTING
mode_rstable = ‘E’
tabname = p_table.
endif.
ENDIF.
ENDIF.
ENDIF.
refresh : <fs_table_final>,
<fs_table>,
<fs_table_temp>.
ENDFORM. “get_data
*&———————————————————————*
*& Form display_data
*&———————————————————————*
* display data using ALV
*———————————————————————-*
FORM display_data. “#EC NEEDED
PERFORM build_fieldcatalog.
PERFORM build_layout.
PERFORM display_alv_report.
ENDFORM. ” display_data “#EC NEEDED
*&———————————————————————*
*& Form BUILD_FIELDCATALOG
*&———————————————————————*
* Build Fieldcatalog for ALV Report, using SAP table structure
*———————————————————————-*
FORM build_fieldcatalog.
*** ALV Function module to build field catalog from SAP table structure
DATA: it_fcat TYPE slis_t_fieldcat_alv.
CALL FUNCTION ‘REUSE_ALV_FIELDCATALOG_MERGE’
EXPORTING
i_structure_name = p_table
CHANGING
ct_fieldcat = it_fcat
EXCEPTIONS
inconsistent_interface = 1
program_error = 2
OTHERS = 3.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
fieldcatalog[] = it_fcat[].
DELETE fieldcatalog WHERE key EQ space.
ENDFORM. ” BUILD_FIELDCATALOG
*&———————————————————————*
*& Form BUILD_LAYOUT
*&———————————————————————*
* Build layout for ALV grid report
*———————————————————————-*
FORM build_layout.
gd_layout-colwidth_optimize = ‘X’.
ENDFORM. ” BUILD_LAYOUT
*&———————————————————————*
*& Form DISPLAY_ALV_REPORT
*&———————————————————————*
* Display report using ALV grid
*———————————————————————-*
FORM display_alv_report.
gd_repid = sy-repid.
CALL FUNCTION ‘REUSE_ALV_GRID_DISPLAY’
EXPORTING
i_callback_program = gd_repid
is_layout = gd_layout
it_fieldcat = fieldcatalog[]
i_save = ‘X’
TABLES
t_outtab = <fs_table>
EXCEPTIONS
program_error = 1
OTHERS = 2.
ENDFORM. ” DISPLAY_ALV_REPORT
*&———————————————————————*
*& Form READ_DYNPRO_CONTENT
*&———————————————————————*
* text
*———————————————————————-*
* –>P_0293 text
* <–P_P_PSFILE text
*———————————————————————-*
FORM read_dynpro_content USING pv_fname TYPE any
CHANGING pv_value TYPE any.
*Local variable declaration
DATA: lt_dynpfields TYPE STANDARD TABLE OF dynpread,
ls_dynpfields TYPE dynpread.
MOVE pv_fname TO ls_dynpfields-fieldname.
APPEND ls_dynpfields TO lt_dynpfields.
CLEAR:ls_dynpfields.
CALL FUNCTION ‘DYNP_VALUES_READ’
EXPORTING
dyname = sy-repid
dynumb = sy-dynnr
TABLES
dynpfields = lt_dynpfields
EXCEPTIONS
invalid_abapworkarea = 1
invalid_dynprofield = 2
invalid_dynproname = 3
invalid_dynpronummer = 4
invalid_request = 5
no_fielddescription = 6
invalid_parameter = 7
undefind_error = 8
double_conversion = 9
stepl_not_found = 10
OTHERS = 11.
IF sy-subrc EQ 0.
LOOP AT lt_dynpfields INTO ls_dynpfields
WHERE fieldname EQ pv_fname.
MOVE ls_dynpfields-fieldvalue TO pv_value.
EXIT.
ENDLOOP.
ENDIF.
ENDFORM. ” READ_DYNPRO_CONTENT
*&———————————————————————*
*& Form GET_FILE_NAME_PS
*&———————————————————————*
* text
*———————————————————————-*
* –>P_P_PS text
* <–P_P_PSFILE text
*———————————————————————-*
FORM get_file_name_ps USING p_ps_flag TYPE flag
CHANGING p_fname TYPE file.
* local declaration
DATA: lt_file_table TYPE filetable,
l_action TYPE i,
l_rc TYPE sy-subrc,
l_upath TYPE string,
l_upath_help TYPE string,
l_fdpos LIKE sy-fdpos.
FIELD-SYMBOLS: <lfs_file> TYPE file_table.
* Only allow selection if file type is presentation server!
IF p_ps_flag IS INITIAL.
EXIT.
ENDIF.
* Determine initial path
MOVE p_fname TO l_upath_help.
DO 10 TIMES.
IF l_upath_help CA ‘\’.
l_fdpos = sy-fdpos + 1.
CONCATENATE l_upath l_upath_help(l_fdpos) INTO l_upath.
SHIFT l_upath_help BY l_fdpos PLACES.
ELSE.
EXIT.
ENDIF.
ENDDO.
* File open dialog
CLEAR: lt_file_table, l_action, l_rc.
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
default_extension = ‘*.*’
file_filter = cl_gui_frontend_services=>filetype_all
initial_directory = l_upath
CHANGING
file_table = lt_file_table
rc = l_rc
user_action = l_action
EXCEPTIONS
file_open_dialog_failed = 1
cntl_error = 2
error_no_gui = 3
not_supported_by_gui = 4
OTHERS = 5.
* Check if file has been selected
IF sy-subrc EQ 0.
IF l_action NE 9.
READ TABLE lt_file_table INDEX 1 ASSIGNING <lfs_file>.
p_fname = <lfs_file>.
ELSE.
* File selecton canceled.
MESSAGE ‘File selection terminated.'(002) TYPE ‘S’.
ENDIF.
ELSE.
MESSAGE ID sy-msgid TYPE ‘S’ NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
ENDFORM. ” GET_FILE_NAME_PS
*&———————————————————————*
*& Form CHECK_FILE_EXIST
*&———————————————————————*
* text
*———————————————————————-*
* –>P_P_PSFILE text
* –>P_0312 text
*———————————————————————-*
FORM check_file_exist USING pv_psfile TYPE any
pv_field TYPE any.
* local declaration
DATA: l_result(1) TYPE c,
l_upath TYPE string,
l_upath_help TYPE string,
l_fdpos LIKE sy-fdpos.
DATA: l_message(100).
* Path and file name given?
IF pv_psfile IS INITIAL.
SET CURSOR FIELD pv_field.
* Please specify file names.
MESSAGE text-003 TYPE ‘E’.”‘Please specify filename’.
ENDIF.
* Determine initial path
MOVE pv_psfile TO l_upath_help.
DO 10 TIMES.
IF l_upath_help CA ‘\’.
l_fdpos = sy-fdpos + 1.
CONCATENATE l_upath l_upath_help(l_fdpos) INTO l_upath.
SHIFT l_upath_help BY l_fdpos PLACES.
ELSE.
EXIT.
ENDIF.
ENDDO.
* Check path exists
CALL METHOD cl_gui_frontend_services=>directory_exist
EXPORTING
directory = l_upath
RECEIVING
result = l_result
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
wrong_parameter = 3
not_supported_by_gui = 4
OTHERS = 5.
IF sy-subrc EQ 0.
IF l_result NE ‘X’.
SET CURSOR FIELD pv_field.
PERFORM enable_file_input.
*Directory does not exist.
CONCATENATE l_upath text-005 INTO l_message SEPARATED BY space.
MESSAGE l_message TYPE ‘E’.
ENDIF.
ELSE.
SET CURSOR FIELD pv_field.
MESSAGE ID sy-msgid TYPE ‘E’ NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
ENDFORM. ” CHECK_FILE_EXIST
*&———————————————————————*
*& Form ENABLE_FILE_INPUT
*&———————————————————————*
* text
*———————————————————————-*
* –> p1 text
* <– p2 text
*———————————————————————-*
FORM enable_file_input .
ENDFORM. ” ENABLE_FILE_INPUT
Regards
Srini