ABAP, NetWeaver, ERP Tutorial

In ABAP programming, we need to create some custom tables to store extended business data or some execution logs. The texts in these custom tables are always saved/displayed in upper and lower case. If the user enters some search criteria which is not case sensitive, he cannot get the expected results. The business always wants the report to provide the capability to match the inputs with database fields case-insensitively.

Generally, there are two solutions for this question.

Solution 1. Add additional fields in custom tables for each fields you want to have in the report.

This is just the SAP standard solution for this question. For example, in address table ADRC – Addresses (Business Address Services), we have fields NAME1, CITY1, STREET to save the business address detail information. The contents of above fields will be in upper and lower format to make it more readable. But user may want to search the address data case-insensitively. SAP creates another three fields: MC_NAME1, MC_CITY1, MC_STREET to store the business address detail information duplicately, but all the contents are in the upper case.

If user enters the name, the program will translate the input into uppercase. The translated input will be matched with fields MC_NAME1 to get expected results.

Note: the two fields must be updated simultaneously. Otherwise it may return the unexpected results.

Solution 2. Use Native SQL

If you cannot add additional fields in the standard table or you think it is very boring to add many duplidated fields, you can consider about using Native SQL. Open SQL cannot solve this issue because it doesn’t support this functionality in order to provide a common interface to all databases. There are some risks to use the Native SQL. Please check your database provider before you use it.

For this question, you can use upper function which is supported by many DBMS. Below is the code snippet to show how to use it.

  TRANSLATE ls TO UPPER CASE.
 
  EXEC SQL PERFORMING append_itab.
    SELECT * FROM  INTO :wa
     WHERE upper("select_field") EQ :ls
  ENDEXEC.
 
  form append_itab.
    append wa to itab.
  endform.