Ignore case in Select Statement with Select-Options..!!!

[Former Member]

Hi Experts,

Currently iam using the following SELECT query,

SELECT bktxt FROM BKPF into g_bktxt

WHERE BKTXT IN s_bktxt.

Here, the issue is - in SE11 for BKPF-BKTXT they maintain different combinations of BKTXT values like : WA, wa, Wa, wA.

Now, when an user gives 'wa' in select options (s_bktxt) then i need to pull all (WA, wa, Wa, wA) combinations from database - irrespective of Case ( Case Insensitve or Ignore Case ) at the input select-option - s_bktxt.

How to achieve this...?

Any suggesstions / Ideas will be of great help.

[Former Member]

hi

chk this

if helpful, reward

Sathish. R

[Former Member]

hi,

u can restrict like this..

either in select-options : <b>give LOWER CASE</b>

or in where conditon use this

WHERE <b>upper(carrid) = :c1</b>

or

use this <b>TRANSLATE wa TO UPPER CASE.</b>

With Rgds,

S.Barani

[Former Member]

Hi Kar Sar,

You can do one thing. first pull all the records in an internal table that is of type of structure that is containing only one field BKTXT, depending upon requirement you can add field to structure.

    SELECT bktxt FROM bkpf INTO TABLE it_bkpf1.

Now loop this table and check whether BKTXT contains 'wa' .

        LOOP AT it_bkpf1 INTO wa_bkpf1.
      IF wa_bkpf1-bktxt CP 'wa'.     "Here you can check for any string
        APPEND wa_bkpf1 TO it_bkpf. "Store that in another internal table
        CLEAR wa_bkpf1.
      ENDIF.
    ENDLOOP.

CP operator checks for string irrespctive of the case. Using This you can perform wild search also.

Here in the SELECT statement there is no WHERE clause, this is the performance isuue but i could not find any other better approach.

With Regards,

Ajeet Pratap Singh

[Former Member]

I had a similar requirement and used this approach:

- First I took the selection parameter and put only the first character in a variable.

- Next, I used the CONCATENATE command to add a % character to it.

- Then I converted this to upper case using the TRANSLATE command.

- The next lines of code copied the same value to a second variable and converted it to lower case, so I had both an upper case and lower case version of the first character, like "A%" and "a%".

- Now I can use these values in the WHERE clause of a SELECT statement to build an internal table of all the database rows that match the first character (WHERE field LIKE var1 or field LIKE var2).

- Once this first selection has been done using the SELECT statement, I can go through the data using a LOOP command.  Within that LOOP, the CP (contains pattern) operator can be used to do a compare that is NOT case sensitive.

If your database table is large, you could create more variables to use in the first SELECT statement.  You could use 4 variables instead of 2 and reduce the number of rows in the intial select quite significantly by using every combination of upper/lower case for the first 2 letters of the seletion parameter (AB%, Ab%, aB%, ab%).  Again, after the first SELECT statement has been used to quickly building an internal table of potential matches, you would have to LOOP through the data and use the CP operator to do an case-insenstive comparison.