I had an issue today with a custom BAPI (using SAP's proprietary language
ABAP) that would just NOT return values that I knew were in the PRPS.POST1 field. It turned out that it was a case sensitivity problem - as the LIKE clause in SAP is case sensitive. Typically users don't care about case at all and expect that a search for "Test" will return all upper case and lower case variations (e.g. Test, TEst, TEST, etc).
Unfortunately there is no equivalent of the UPPER() statement in standard SQL Syntax. There are 2 workarounds that I'm aware of:
- If you are lucky, there is a field with the same name defined for text fields which has all upper case characters and suffixed with "U". For example, the upper case equivalent of POST1 is the POSTU field. You can then do case insensitive searches on the field by forcing your input parameter to upper case.
- Use the CS (Contains String) Function instead of the LIKE clause within your select.....endselect
See below for details of a real-world example of how to use the CS clause for case-insensitive searches:
The below custom BAPI function accepts a spend type (e.g. Capital Expenditure (CAPEX), Operational Expenditure (OPEX)) company code parameter and a wildcard search field and then populates different objects based on the spend type. For example, a Spend type of OPEX will return a list of Cost Centres into the T_TYPE_OKH_COST_CENTRE Table as an output, a spend type of Asset will return a list of assets into the the T_TYPE_A_ASSET structure in the output.
Sample Search BAPI Code (ABAP)
FUNCTION Z_BAPI_COST_OBJECT_FIND.
*"----------------------------------------------------------------------
*"*"Local Interface:
*" IMPORTING
*" VALUE(I_SPEND_TYPE) TYPE EKPO-KNTTP
*" VALUE(I_COMPANY_CODE) TYPE PRPS-PBUKR
*" VALUE(I_COST_OBJECT) TYPE CHAR_50
*" TABLES
*" T_TYPE_P_WBS STRUCTURE ZFPRPS
*" T_TYPE_OKH_COST_CENTRE STRUCTURE ZFCSKS
*" T_TYPE_A_ASSET STRUCTURE ZFANLA
*" EXCEPTIONS
*" EXCEPTION
*"----------------------------------------------------------------------
tables: prps, csks, anla.
data: projdesc(50) TYPE C.
*The below statements use a CS statement rather than the like clause of SQL
*because LIKE is case sensitive in SAP - unfortunately there is no equivalent
* of UPPER() same as in Native SQL Server.
* Get Cost Object of type WBS
* and POSKI LIKE I_COST_OBJECT."
* We need to do a cast
if I_SPEND_TYPE = 'P'.
select * from PRPS
where BELKZ = 'X'
and PBUKR = I_COMPANY_CODE.
* and ( POSID LIKE I_COST_OBJECT or POST1 LIKE I_COST_OBJECT ).
move PRPS-POST1 to projdesc.
if PRPS-TADAT IS INITIAL and
( PRPS-POSID CS I_COST_OBJECT
or projdesc CS I_COST_OBJECT ).
move PRPS-PSPNR to T_TYPE_P_WBS-PSPNR.
move PRPS-POSID to T_TYPE_P_WBS-POSID.
move PRPS-POST1 to T_TYPE_P_WBS-POST1.
append T_TYPE_P_WBS.
endif.
endselect.
* Get Cost Object of type Cost Centre for Retail, Office and Industrial
elseif I_SPEND_TYPE = 'O'.
select * from CSKS
where BUKRS = I_COMPANY_CODE
and ( ABTEI = 'RETAIL'
or ABTEI = 'OFFICE'
or ABTEI = 'INDUSTRIAL'
or ABTEI = '12' ).
if CSKS-NAME1 CS I_COST_OBJECT
or CSKS-KOSTL CS I_COST_OBJECT.
move CSKS-KOKRS to T_TYPE_OKH_COST_CENTRE-KOKRS.
move CSKS-KOSTL to T_TYPE_OKH_COST_CENTRE-KOSTL.
move CSKS-DATBI to T_TYPE_OKH_COST_CENTRE-DATBI.
append T_TYPE_OKH_COST_CENTRE.
endif.
endselect.
* Get Cost Object of type Cost Centre for Marketing
elseif I_SPEND_TYPE = 'K'.
select * from CSKS
where BUKRS = I_COMPANY_CODE
* and ABTEI = 'MARKETING'.
and ABTEI = '12'.
*Conditionally
if CSKS-NAME1 CS I_COST_OBJECT
or CSKS-KOSTL CS I_COST_OBJECT.
move CSKS-KOKRS to T_TYPE_OKH_COST_CENTRE-KOKRS.
move CSKS-KOSTL to T_TYPE_OKH_COST_CENTRE-KOSTL.
move CSKS-DATBI to T_TYPE_OKH_COST_CENTRE-DATBI.
append T_TYPE_OKH_COST_CENTRE.
endif.
endselect.
* Get Cost Object of type Cost Centre for Head Office
elseif I_SPEND_TYPE = 'H'.
select * from CSKS
where BUKRS = I_COMPANY_CODE
and ( ABTEI = 'CORPORATE'
or ABTEI = '12' ).
move CSKS-KOKRS to T_TYPE_OKH_COST_CENTRE-KOKRS.
move CSKS-KOSTL to T_TYPE_OKH_COST_CENTRE-KOSTL.
move CSKS-DATBI to T_TYPE_OKH_COST_CENTRE-DATBI.
append T_TYPE_OKH_COST_CENTRE.
endselect.
* Get Cost Object of type Asset
elseif I_SPEND_TYPE = 'A'.
select * from ANLA
where BUKRS = I_COMPANY_CODE
and ( ANLKL = '13000000'
or ANLKL = '14000000' ).
if ANLA-ANLN1 CS I_COST_OBJECT or
ANLA-TXT50 CS I_COST_OBJECT.
move ANLA-BUKRS to T_TYPE_A_ASSET-BUKRS.
move ANLA-ANLN1 to T_TYPE_A_ASSET-ANLN1.
move ANLA-ANLN2 to T_TYPE_A_ASSET-ANLN2.
append T_TYPE_A_ASSET.
endif.
endselect.
endif.
ENDFUNCTION.
Additional Notes1) Spaces DO matter around parentheses - the following will NOT work:
INCORRECT:
select * from PRPS
where BELKZ = 'X'
and PBUKR = I_COMPANY_CODE
and (POSKI LIKE I_COST_OBJECT or POST1 = I_COST_OBJECT).
endselect.
CORRECT:
The following is correct syntax
select * from PRPS
where BELKZ = 'X'
and PBUKR = I_COMPANY_CODE
and ( POSKI LIKE I_COST_OBJECT or POST1 = I_COST_OBJECT ).
endselect.
2) All statements should end with a period. (ie similar to the use of the semicolon as a line delimiter in C#)
