topic Re: Case Insensitive SELECT statement in Application Development and Automation Discussions

Case Insensitive SELECT statement

Former Member — Sun, 15 Dec 2013 09:04:04 GMT

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?

Re: Case Insensitive SELECT statement

venkateswaran_k — Sun, 15 Dec 2013 09:18:07 GMT

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

Re: Case Insensitive SELECT statement

former_member43585 — Sun, 15 Dec 2013 09:31:07 GMT

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.

Re: Case Insensitive SELECT statement

former_member182354 — Sun, 15 Dec 2013 09:51:22 GMT

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

Re: Case Insensitive SELECT statement

Former Member — Sun, 15 Dec 2013 10:46:55 GMT

i have lowercase and uppercase in plpo .

Re: Case Insensitive SELECT statement

Former Member — Sun, 15 Dec 2013 11:00:38 GMT

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

Re: Case Insensitive SELECT statement

venkateswaran_k — Sun, 15 Dec 2013 11:16:25 GMT

Hi Maryam

Ask user to use % instead of *

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

Re: Case Insensitive SELECT statement

Former Member — Sun, 15 Dec 2013 11:42:26 GMT

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

Re: Case Insensitive SELECT statement

Former Member — Sun, 15 Dec 2013 13:17:58 GMT

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.

Re: Case Insensitive SELECT statement

SuhaSaha — Sun, 15 Dec 2013 15:24:32 GMT


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

Re: Case Insensitive SELECT statement

Former Member — Mon, 16 Dec 2013 09:13:25 GMT

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

is it possible?

Re: Case Insensitive SELECT statement

Former Member — Mon, 16 Dec 2013 11:04:47 GMT

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.

Re: Case Insensitive SELECT statement

Former Member — Tue, 17 Dec 2013 01:46:30 GMT

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

Re: Case Insensitive SELECT statement

Sougata — Tue, 17 Dec 2013 04:36:01 GMT

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.

Re: Case Insensitive SELECT statement

sivaprasad_paruchuri — Tue, 17 Dec 2013 07:14:05 GMT

u can replace * with % in program to achieve

Re: Case Insensitive SELECT statement

SuhaSaha — Tue, 17 Dec 2013 12:17:22 GMT

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

Re: Case Insensitive SELECT statement

Sougata — Tue, 17 Dec 2013 23:04:59 GMT

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.

Re: Case Insensitive SELECT statement

custodio_deoliveira — Wed, 18 Dec 2013 00:49:27 GMT

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

Re: Case Insensitive SELECT statement

SuhaSaha — Wed, 18 Dec 2013 10:16:44 GMT


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

Re: Case Insensitive SELECT statement

Sougata — Thu, 19 Dec 2013 00:51:45 GMT

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.