topic Re: Case issue in database table in Application Development and Automation Discussions

Case issue in database table

Former Member — Thu, 07 Aug 2008 06:47:27 GMT

hi all,

we have a customized table which is updated by utility using a flat file..

the flat files used previously had lower/upper case letters (mix of them), hence, there are different and unwanted entries in the table with wrong names...

now, we are going to change the utility to update the database table only with upper case letters but we are having issues with already existing unwanted entries..

since this is a one time activity sending a report to production is not advisable...so, please tell me how to delete these unwanted entries (mixed cases) in the database table?

or is there any way by which we can accept lowercase letters at input screen itself?

Re: Case issue in database table

andreas_mann3 — Thu, 07 Aug 2008 06:52:10 GMT

1) clean your entries with a program in dev-system

2) transport cleaned table to production with se09 (obj=tabu)

Re: Case issue in database table

Former Member — Thu, 07 Aug 2008 06:52:24 GMT

hi,

set the table maintainance generator for that table with authorization group then go to SM30 give the table name and press maintain and select the entries ehat u want to delete

Re: Case issue in database table

Former Member — Thu, 07 Aug 2008 07:00:59 GMT

Hi,

To clean up your table, select all entries in internal table.

loop at internal table

TRANSLATE <tabname-fieldname> to UPPER CASE.

modify internal table

endloop.

SORT BY fields you have translated.

DELETE ADJACENT DUPLICATES FROM table comparing fields. (sequence should be same as sort).

Delete all entries in DB TABLE

if sy-subrc <> 0.

rollback work.

endif.

update DB table with internal table with clean data.

if sy-subrc = 0.

commit work.

else.

rollback work.

endif.

For forcing to accept lowercase on screen. you have to validate it in AT SELECTION SCREEN OUTPUT (or PBO in case of screen).

you can use

l_var = tabname-fieldname

TRANSLATE l_var to LOWER CASE.

if l_var = tabname-fieldname

*NO ERROR message

else.

*error/warning message

endif.

In case you are referring to store value as entered by user on screen, use LOWERCASE while defining parameter

example : PARAMETERS : p_var type CHAR10 LOWERCASE.

If you enter LoWerCasE in p_var on screen it preserve Case as it is.

Regards,

Mohaiyuddin

Edited by: Mohaiyuddin Soniwala on Aug 7, 2008 12:40 PM

Re: Case issue in database table

Former Member — Thu, 07 Aug 2008 07:04:39 GMT

Hi Write an ABAP Program from below idea..

select * from ZTABLE into table it_data.

Loop at it_data into wa_data.

CALL FUNCTION 'SWA_STRING_TO_UPPERCASE'

Move-corresponding wa_data to it_final.

EXPORTING
INPUT_EXPRESSION =

INPUT_STRING = wa_data-text

PRESERVE_EXISTING_CAPITALS = 'X'
CAPITALIZE_AFTER_SPACE = 'X'
LANGUAGE = SY-LANGU
IMPORTING

OUTPUT_STRING = it_final-text

OUTPUT_EXPRESSION =
EXCEPTIONS
EXPRESSION_TRUNCATED = 1
OTHERS = 2

IF sy-subrc <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

Delete ZTABLE from wa_data.

Endloop.

clear wa_final.

Sort it_final.

DELETE ADJACENT DUPLICATES FROM it_final COMPARING ALL FIELDS.

Loop at it_final into wa_final.

insert into ZTABLE from wa_final.

commit work.

endloop.

hope this will help you.

rgds

rajesh

Re: Case issue in database table

Former Member — Thu, 07 Aug 2008 07:31:18 GMT

Hi Friend,

Welcome to SDN.

Acceptance of lower/upper case value is defined at domain level. If upper/lower case was set at domain for the data element of the field in that table, it willl different case values.

Even you can use that field on selection screen and it will accept lower values.

In production you have to delete unwanted entries manually.

Hope it will help you.

Regards

Krishnendu

Re: Case issue in database table

Former Member — Thu, 07 Aug 2008 18:21:46 GMT

Thank you all for the help.....:)

Hi...

I cannot transport the cleaned table from development box to production box....since the amount of data differs in both system....for me it is possible to either

1. Delete the entries directly in production box.

2. Change the utility to delete the unwanted entries in database table.

Second option looks good because it will take care of the problem if it occurs in future.

Edited by: Yogesh Sharma on Aug 7, 2008 11:52 PM