Recently, I was looking at a requirements document to build an interface to an external system that wants to query customer master data by the customer first name and last name.  As I read this, there were a cacophony of thoughts, all demanding equal attention, racing through my head:

• How will I ever match the inbound interface parameter “Tom” with “TOM”, or “tom”?
• How will I ever match the inbound interface parameter “Smith” with “SMITH” or “smith”?
• The ABAP WHERE clause is not case-INsensitive.
• There could be hundreds of customers named Tom Smith.
• KNA1-NAME1 and KNA1-NAME2 are not indexed fields.
• And no, we are not storing any portion of either first or last name in an existing indexed field like SORTL.
• There are well over one million customers in the database.
• We have already decided to use PI for all interfaces.
• I will have to buy the BASIS team a case of beer to get them to agree to create indices on the fields KNA1-NAME1 and KNA1-NAME2 in a table with over one million records.

I arrived at the conclusion that I need a case-insensitive database query, along with database indices created for the fields KNA1-NAME1 and KNA1-NAME2.

But, what is a case-insensitive WHERE clause?  A little research and help from colleagues revealed that many had gone before me, and this was nothing new.  To implement a case-insensitive WHERE clause in ABAP, you simply needed to use the native SQL UPPER() construct. The database system that is being used is Microsoft SQL Server, but the UPPER() function and its syntax is similar across different database platforms. This seemed like an easy nut to crack. But, as I soon found out, I actually had a lot to learn.

Take 1

The examples I found were very explicit, showing actual code snippets.  Using my research examples as a template, I built what was to be my first attempt at using native SQL in a ZTEST ABAP program:

exec sql.
  select kunnr name1 name2
    from kna1
    into table i_cust
    where upper(name1) like :l_name1
      and upper(name2) like :l_name2
endexec.

Well, as a native SQL rookie, I was stopped dead in my tracks.  Native SQL does not allow a select into a table – you can only select into a work area.

Take 2

In my ZTEST ABAP program, I changed the code to SELECT into a work area and append the work area to an internal table in a subroutine:

exec sql performing append_cust.
  select kunnr, name1, name2
    from kna1
    into :wa_cust
    where upper(name1) like :l_name1
      and upper(name2) like :l_name2
endexec.

form append_cust.
  append wa_cust to i_cust.
endform.

I really thought that this would work.  But, I was getting a syntax error from native SQL – the table name was not being recognized.  A little more research revealed that, in some cases, only UPPER CASE is valid for table names and field names.  OK.  I can handle that.

Take 3

I changed the native SQL statement to all UPPER CASE in my ZTEST ABAP program:

EXEC SQL PERFORMING APPEND_CUST.
  SELECT KUNNR, NAME1, NAME2
    FROM KNA1
    INTO :WA_CUST
    WHERE UPPER(NAME1) LIKE :L_NAME1
      AND UPPER(NAME2) LIKE :L_NAME2
ENDEXEC.

form append_cust.
  append wa_cust to i_cust.
endform.

This appearedto work just fine…  Except, I noticed that too many records were being returned.  A little more analysis showed that I was getting records from ALL clients, not just my logon client.

Take 4

Again, in my ZTEST ABAP program, specify the client:

EXEC SQL PERFORMING APPEND_CUST.
  SELECT KUNNR, NAME1, NAME2
    FROM KNA1
    INTO :WA_CUST
    WHERE UPPER(NAME1) LIKE :L_NAME1
      AND UPPER(NAME2) LIKE :L_NAME2
      AND MANDT = :L_MANDT
ENDEXEC.

form append_cust.
  append wa_cust to i_cust.
endform.

Now I was getting the results that I wanted!!!!  So, I pasted this ZTEST ABAP code into the ABAP proxy.

A syntax check of the code immediately rejected the use of subroutines – they are not allowed in OO ABAP. I suppose I should have known that.  The error message indicated that I needed to use a cursor instead of the subroutine.  So, I started cursing – a lot!!!

Take 5 (The Final Version)

Here is the final version of the code which works for OO ABAP, and, hence, the ABAP proxy:

EXEC SQL.
  OPEN CUSTCURSOR FOR
    SELECT KUNNR, NAME1, NAME2
      FROM KNA1
      WHERE UPPER(NAME1) LIKE :L_NAME1
        AND UPPER(NAME2) LIKE :L_NAME2
        AND MANDT = :L_MANDT
ENDEXEC.

DO.
  EXEC SQL.
    FETCH NEXT CUSTCURSOR INTO :WA_CUST
  ENDEXEC.

  IF SY-SUBRC NE 0.
    EXIT.
  ELSE.
    APPEND WA_CUST TO I_CUST.
  ENDIF.
ENDDO.

EXEC SQL.
  CLOSE CUSTCURSOR
ENDEXEC.

Well, I finally got it right after five tries.  As mentioned before, I found many explicit code examples, but none were for OO ABAP with a database  requiring UPPER CASE be used, so that the table and field names would be recognized in the native SQL SELECT statement.

I learned something new about SAP.  I hope that I never stop learning.  I also hope that this blog might save someone a bit of distress in trying to work through a similar situation.  Now, I just need to figure out what kind of beer to buy the BASIS team to get those indexes created…