Case Insensitive SELECT statement

[Former Member]

hi expert.

this is my scenario.

i have one parameter in input for short description with name ZLTXA1

.

I HAVE THIS QUERY :

SELECT * FROM PLPO WHERE PLPO~LTXA1 LIKE  ZLTXA1.

.

FOR EXAMPLE I SEARCH FOR TEST .ONE TIME IN PLPO I HAVE TEST.IN ONE RECORD I HAVE Test and so on.

but i got just test in output.

how can i fix it?

[venkateswaran_k]

Hi

The field PLPO~LTXA1 ( domain type is TEXT40 - which is by default stores data in lower case)

You can verify that in SE11.

So you need to define your ZLTXA1 of same type as LTXA1.

You can convert them into lowercase (using translate statement) before used in query.

TRANSLATE zltxa1 INTO   LOWERCASE.

Also, please let me knwo how user enters data in parameter.  using any wild chars ?

Example:

data: ZLTXA1 TYPE LTXA1.

condense zltxa1.

TRANSLATE zltxa1 TO LOWERCASE.

concatenate '%' ZLTXA1 '%' into zltxa1.

SELECT * FROM PLPO WHERE PLPO~LTXA1 LIKE  ZLTXA1.

Regards,

Venkat

[Former Member]

HI THANKS YOU FOR YOUR REPLY.USER USE CHARACHTER AND *

[venkateswaran_k]

Hi Maryam

Ask user to use % instead of *

Or

concatenate '%' ZLTXA1 '%' into zltxa1.

SELECT * FROM PLPO WHERE PLPO~LTXA1 LIKE  ZLTXA1.

You can change your code as above.  (User need not to provide any wild card).

Regards,

Venkat

[sivaprasad_paruchuri]

u can replace * with % in program to achieve

[former_member43585]

Hi Maryam,

I would recommend before using ZLTXA1 in your select query use below logic,

CONCATENATE '%'ZLTXA1'%' INTO ZLTXA1.

And then use it in your select query.

Hope this might help.

Regards,

Ramiz.

[former_member182354]

Hello Maryam,

                      This is because your domain for field LTXA1 might have LOWER CASE checkbox checked.

                      This implies that same field value will not be shown if it contains value in higher case with the select query you used.

                       Try our 'CA' statement and check if that works.

SELECT * FROM PLPO WHERE PLPO~LTXA1 CA  P_ZLTXA1.

Regards,

Raghav

[Former Member]

i have lowercase and uppercase in plpo .

[Former Member]

Hi Maryam,

I Would re-commend you to first declare parameter using below statement.

PARAMETERS <p> lowercase .

Now use the same  value in your select query as below :-

SELECT * FROM PLPO WHERE PLPO~LTXA1 LIKE  <p>.

I Hope this will work !

Regards,

Praveen Srivastava

[Former Member]

Hi Maryam,

The domain for ltxa1 has lowercase checkbox enabled.

This means that it distinguishes lower case and upper case characters, ie Ab and AB is different.

To execute a select query for such fields, you will need to input the string with the same case as is stored in the database table.

I dont think it is possible to have one generic select statement for this, since there can be so many combinations of the string characters in upper and lower cases.

I would suggest that you first retrieve the data into an internal table and then do the filtering in the internal table.

*First convert the parameter into one case.

translate ZLTXA1 to upper case.

* Get the data into internal table .

select  .......into table it_plpo ..... .where " if any other conditions are there..

* Compare the field ltxa1.

loop at it_plpo into wa_plpo.

  translate wa_plpo-ltxa1 to upper case.

  if wa_plpo-ltxa1 ne zltxa1.

      delete it_plpo index sy-tabix.

endif.

endloop.

[SuhaSaha]

To execute a select query for such fields, you will need to input the string with the same case as is stored in the database table.

I dont think it is possible to have one generic select statement for this, since there can be so many combinations of the string characters in upper and lower cases.

It is not 100% correct. You can use Native SQL to do a case insensitive search using ABAP, provided the underlying DB can handle it.

This problem has been discussed many times in the forums & the OP could have referred to them before posting the question.

- Suhas

[Former Member]

i see in the sdn use upper(plpo~ltxa1) in where statement .

is it possible?

[Former Member]

Yes its possible if you are using native sql. Its the syntax in native sql.

TRANSLATE ZLTXA1 TO UPPER CASE.

  EXEC SQL PERFORMING append_itab.

    SELECT * FROM  PLPO INTO :wa

     WHERE upper(LTXA1) EQ : ZLTXA1

  ENDEXEC.

  form append_itab.

     append wa to itab.

  endform.

[Former Member]

thank you for your reply .but i want to use in abap and with like statement

[Sougata]

You might want to do it like this:

Scenario:

Input field IV_NAME1_SEARCHSTRING in this Method is a search string which searches for case insensitive NAME1 field in vendor master LFA1.

Parameters of the Method

Source code of Method

TYPES:
     TY_T_LFA1 TYPE TABLE OF LFA1 .     "Declared in the Public Section

METHOD select_vendor_native .

  DATA:
     o_cond              TYPE REF TO cl_lib_seltab,
     l_con_ref           TYPE REF TO cl_sql_connection,
     l_stmt              TYPE string,
     l_stmt_ref          TYPE REF TO cl_sql_statement,
     l_dref              TYPE REF TO data,
     l_res_ref           TYPE REF TO cl_sql_result_set,
     ls_lfa1             TYPE lfa1,
     lt_lfa1             TYPE TABLE OF lfa1,
     lv_found            TYPE boolean,
     lv_new_searchstring TYPE string,
     lv_size_limit       TYPE i.

  lv_new_searchstring = to_upper( replace( val = iv_name1_searchstring
                                           sub  = |*|
                                           with = |%|
                                           occ  = 0 ) ).

  lv_size_limit = iv_size_limit.
  IF lv_size_limit < 0 OR lv_size_limit > gc_size_limit.    "100
    lv_size_limit = gc_size_limit.
  ENDIF.

* create the connecction to database object.
  CREATE OBJECT l_con_ref.

  l_stmt =     'SELECT * '    &&
                   ' FROM  lfa1 '     &&
                   ' WHERE upper(name1) LIKE  ''' && lv_new_searchstring && '''' &&
                   ' AND mandt = ' && sy-mandt &&
                   | FETCH FIRST | && lv_size_limit && ' ROW ONLY'.

* create the sql statement object
  l_stmt_ref = l_con_ref->create_statement( ).
  l_res_ref  = l_stmt_ref->execute_query( l_stmt ).

* set output structure
  GET REFERENCE OF ls_lfa1 INTO l_dref.
  l_res_ref->set_param_struct( l_dref ).

* show result
  CLEAR et_lfa1.
  WHILE l_res_ref->next( ) > 0.
    APPEND ls_lfa1 TO et_lfa1.
  ENDWHILE.

  ev_found = boolc( et_lfa1 IS NOT INITIAL ).

* close the result set object
  l_res_ref->close( ).

ENDMETHOD.

You need to follow the model above to meet your requirement for the PLPO table that you are using.

Hope this helps.

Sougata.

[SuhaSaha]

Hello Sougata,

You are using the ADBC API classes to wrap the Native SQL statement. Of course the guideline is to use ADBC, but is it worth the effort?

I have used ADBC APIs extensively in the last few weeks & i love them. But i still feel that it's too much for this simple task, i prefer to KISS

Will be happy to hear your thoughts.

BR,

Suhas

[Sougata]

Hi Suhas,

I believe its definitely worth the effort, especially for the particular scenario in my example, to be able to meet the business requirement.

The requirement that we had was to provide (to a WebService) with all matches (i.e. all possible combinations of a case insensitive wildcard search) of a vendor name found from the SAP database (field LFA1-NAME1) on user input in a "vendor name" field from a legacy system via a WebService.

We did not think it was possible to achieve the results by using Open SQL - Probably the only way to get the results was to use Native SQL and the only way we could build it elegantly (and in the SAP recommended way) was to use the SAP provided ADBC APIs.

Cheers,

Sougata.

[custodio_deoliveira]

Nice stuff, Sougata!

Haven't tried ADBC much yet, but will keep it in mind in case I need something similar (as I did in the past, but no ADBC available )

Cheers,

Custodio

[SuhaSaha]

the only way we could build it elegantly (and in the SAP recommended way) was to use the SAP provided ADBC API

That's what the question is all about - elegance v/s simplicity

Just to make everyone sure I'm not against using ADBC; i love them, i really do

Cheers,

Suhas

[Sougata]

In this particular business scenario, as far as I know, Native SQL is the only way to achieve the required results i.e. it is not possible to construct a generic Open SQL statement to provide a solution for this requirement as there could possibly be innumerable search combinations.

Other way it could have been done was to use direct Native SQL operations i.e. coding direct EXEC-ENDEXEC statements - but there are lots of advantages of using these ADBC APIs. The intention is not to touch the core object (Native SQL operations) but to work with the wrapper objects (the APIs) which drive the DB operations with a lot of exception handling at each layer available "free" to the calling applications.

I think the whole point is to model the data access in an object oriented approach.To put this argument into perspective: Would I write procedural code if asked to write a new program from scratch? No. Is there any harm in writing good procedural code? No! Then why would I want to write it completely in OO? Because I want to implement the solution using a 'Model' which then becomes much easier to enhance and/or extend business functionality in the long run etc. etc.

So I think, more often than not, there are more than enough reasons for being elegant than just for elegance sake!

Sougata.

[Former Member]

Hi,

as pointed above, your best shot will be using the native SQL to perform the task. If the underlying database(s), or whatever other reason is not to use the native SQL, then your only opportunity is to inject the logic that fills / changes the table PLPO and include (e.g. append structure) a Z-field, which would in every change of the database row contain upper (or lower) case of the text needed. In your applications then this would be a very simple thing to accomplish, as you would enable users to use "*" wildcards, where your code would check for the asterisk in the look-up text and if found, you would use a CP relation operator insted of EQ (or LIKE in other cases). Or you would use a range / select option to leave all work to the ABAP itself. Your select statements will in this case query the Z-field, not the one that exists by standard.