We recently did a Unicode conversion. Before the conversion, we had a number of old matchcodes. Matchcodes are obsolete and not allowed in a Unicode environment. I was asked to convert the matchcodes to search helps.
The matchcodes in many cases were for text fields that could be in a combination of upper and lower case. Now matchcode objects (at least the ones I had to convert) included extra tables to be used by the search procedure. The tables contained the text strings translated to upper case. The system automatically created or changed the matchcode entries when they were maintained by the custom transactions. So when a user used the matchcode, he or she did not have to worry about case.
The first step in converting the matchcode is to delete it. This also deletes the matchcode tables and we are left with the base tables with which to work.
So the next step is to create a search help on the base table text field. I did this in the normal way and it more or less “worked”. The only problem was that it was now case sensitive. If a user entered “smith*” in a last name field and pressed F4, no records were likely to be returned. However, if “Smith*” were entered, then all records starting with “Smith” would be returned, but no records starting with “SMITH”.
This was clearly unacceptable, but what to do?
There are three approaches that I could think of.
The first was to use the standard SAP method: add new fields to the base table that contained the upper case translation of the fields to be used in the search help. An example of this can be seen in the customer master table KNA1. The field MCOD1 contains the contents of field NAME1 translated to upper case (and truncated from thirty-five to twenty-five characters). Additionally, there is an index on this field. The transaction that maintains customers also maintains this field. The benefits of dong this is that you can easily create a case insensitive search help that will be able to use the secondary index created for it. The drawback is that the table must be changed and the update program must be changed to maintain this field as well. There is also some additional system overhead maintaining the index.
The second approach is one that I have seen used in some cases. A search help exit is created that reads all records from the table into an internal table, translates the text field to upper case, translates the search string to uppercase and then filters the records without worrying about case (everything is upper case). The benefits of doing this is that it does work, but to me the main drawback is that it would be very difficult to get this to use an index and there is more overhead because each record must be processed multiple times. Also, although the coding is not complex, there is more than I would like to see and it is not obvious at first glance what it is doing.
The approach that I decided upon was to use native SQL to translate the text to uppercase as it was retrieving from the database and filter it using the upper case translation of the search string. The code is fairly straightforward and when retrieving data, the database may be able to do an index scan. So it’s a bit quicker than the second option but not as fast as the first.
Some caveats:
1) This may not work for you. Native SQL depends on the database being used. I have tested this on out DB2 tables and have used similar logic in Oracle. Other databases may or may not have similar tools. If you do not use one of the two I mentioned, you will have to search them out yourself.
2) I have tested this using a wildcard search string like “smith*”. If you want to use other wildcards or no wildcards at all (test for equality), then you will have to build separate SELECTs.
The steps:
As an example, I created a search help for KNA1-NAME1 in a test system. I used a standard SAP table so that you can see exactly what I did, but you will probably want to try this on a custom table or a table you create specifically for the purpose.
Step 1
Create a search help. In SE11, create search help ZKREDA as an elementary search help. The search help parameters are NAME1 and KUNNR. Make sure KUNNR is exported. Also make sure to use the same data elements as are in KNA1. Give the selection method as ZKREDAV. Give the search help exit as ZKRED_F4IF_SHLP_EXIT. Save it but don’t try to activate.
Step 2
Create the selection method. Leave the search help screen, but still in SE11 enter ZKREDAV as the view name and press create. Create it as a database view. Enter KNA1 as the table on the Table/Join conditions tab. On the View Fields screen, enter MANDT, KUNNR and NAME1 as the view fields. Optionally, you can enter selection conditions to limit the number of records returned and the time taken. Save and activate the view. At this point you can test it. It will be case sensitive.
Step 3
Create the search help exit. First, make sure you have a function group to hold the search help exit. If you do not have one, leave SE11 and go to SE80 and create the function group. Now go to SE37 and create the function module ZKRED_F4IF_SHLP_EXIT in your function group. Look at function module F4IF_SHLP_EXIT_EXAMPLE to see what your importing, exporting and table parameters should look like (and check its documentation). The FM code should look like:
FUNCTION zkred_f4if_shlp_exit.
DATA: rc TYPE sy-subrc.*STEP SELECT
IF callcontrol-step NE 'SELECT'.
EXIT.
ENDIF.
IF callcontrol-step = 'SELECT'.
PERFORM step_select TABLES record_tab
shlp_tab
CHANGING shlp
callcontrol
rc.
IF rc = 0.
callcontrol-step = 'DISP'.
ELSE.
callcontrol-step = 'EXIT'.
ENDIF.
EXIT.
ENDIF.
Now for the nitty-gritty. We will use native SQL to translate the name to upper case before it is returned from the database. The code for FORM STEP_SELECT:
form step_select
tables record_tab structure seahlpres
shlp_tab type shlp_desct
changing shlp type shlp_descr
callcontrol type ddshf4ctrl
rc type sy-subrc.
data: ls_option type ddshselopt.
data: ls_zkredav like standard table of zkredav.
data: ls_zkredav_wa like zkredav.
data: name1 like zkredav-name1,
no_name(1),
count type i.
loop at shlp-selopt into ls_option.
if ls_option-shlpfield = 'NAME1'.
name1 = ls_option-low.
translate name1 to upper case.
replace all occurrences of '*' in name1 with '%'.
if sy-subrc <> 0. "Empty
no_name = 'X'.
endif.
endif.
endloop.
if name1 is initial.
name1 = '%'.
endif.
if no_name is not initial.
EXEC SQL.
OPEN C1 FOR
SELECT *
FROM ZKREDAV
WHERE mandt = :sy-mandt
and UPPER(name1) = :name1
ENDEXEC.
else.
EXEC SQL.
OPEN C1 FOR
SELECT *
FROM ZKREDAV
WHERE mandt = :sy-mandt
and UPPER(name1) like :name1
ENDEXEC.
endif.
do.
count = count + 1.
if count > callcontrol-maxrecords.
callcontrol-maxexceed = 'X'.
exit.
endif.
EXEC SQL.
FETCH NEXT C1 INTO :ls_ZKREDAV_wa
ENDEXEC.
if sy-subrc <> 0.
exit.
endif.
call function 'ISP_CONVERT_FIRSTCHARS_TOUPPER'
exporting
input_string = ls_zkredav_wa-name1
importing
output_string = ls_zkredav_wa-name1.
append ls_zkredav_wa to ls_zkredav.
enddo.
EXEC SQL.
CLOSE C1
ENDEXEC.
call function 'F4UT_RESULTS_MAP'
tables
shlp_tab = shlp_tab
record_tab = record_tab
source_tab = ls_zkredav
changing
shlp = shlp
callcontrol = callcontrol
exceptions
illegal_structure = 1
others = 2.
if sy-subrc <> 0.
rc = 4.
endif.
endform. "step_select
Save and activate everything.
Some things to be aware of in the above code:
1)
If the user leaves the name field blank, we have to add a ‘%’ for the LIKE
2)
If the user enters a value with no wildcards, the SELECT must be changed to test for equality rather than using LIKE. This will have major implications if you want to create a search help with more than one field because of the different combinations of fields that can have wildcards or not. For example, if you have two fields, you will need four SELECTs; three fields require eight SELECTs; five fields would need thirty two SELECTs. So you either need to keep the number of fields to a minimum or assume you will always be using wildcards.
3) Performance may be an issue – even with an index on the field you are using. I tested this without an index on NAME1 and then again after creating an index. The “explain” function showed that the index was used, but the execution time was greater than before the index was created.
Step 4
Test – sample code might look like:
REPORT ztest_shelp LINE-SIZE 80 MESSAGE-ID 00.
TABLES: kna1.
PARAMETERS: p_kunnr LIKE kna1-kunnr MATCHCODE OBJECT zkreda
OBLIGATORY.
SELECT SINGLE * FROM kna1
WHERE kunnr = p_kunnr.
