Requirement - How to remove the case-sensitivity in SELECT?

[Former Member]

Hi Experts,

i have a zreport, in it's selection screen it has a field 'YOU REF NO'. this field is case sensitive. if we enter same text as stored in the then only it is displaying corresponding records. so how to remove case sensitivity of the field?

Moderator Message: Please use meaningful subject lines for your posts to get better responses.

Message was edited by: Suhas Saha

[Former Member]

Hi Experts,

i have a zreport, in it's selection screen it has a field 'YOU REF NO'. this field is case sensitive. if we enter same text as stored in the then only it is displaying corresponding records. so how to remove case sensitivity of the field?

Moderator Message: Please use meaningful subject lines for your posts to get better responses.

Message was edited by: Suhas Saha

[Former Member]

Hi Experts,

i have a zreport, in it's selection screen it has a field 'YOU REF NO'. this field is case sensitive. if we enter same text as stored in the table then only it is displaying corresponding records. so how to remove case sensitivity of the field?

[Former Member]

You can check the Lower case flag in the domain of table field.

Thanks,

Shambu

[Former Member]

Hi

In the declaration of the selection screen, please add "LOWER CASE".

This addition prevents the content of character-type fields from being converted to uppercase letters.

Cheers

~Niranjan

[Former Member]

Hi Niranjan,

can u give the syntax?

i declared like this :

parameters: s_ihrez type vbkd-ihrez.

[Former Member]

Hi Vinod,

PARAMETERS: s_ihrez type vbkd-ihrez LOWER CASE..

Cheers

~Niranjan

[Aiolos]

do let know what's your requirement?

you want the field without case sensitive, right?

does this mean if you input 'A' or 'a', you always want to deal it with 'A'?

if so, please change the domain in se11. uncheck field lower case. you will achieve it.

else please explain quetioin more detail. hope will help you.

[Former Member]

Hi Yang,

Thank u for ur reply.

Actually VBKD-IHREZ got different entries like " test1,  test2, Test1 Ref and so on.

In the selection-screen if i enter "Test1 Ref", it is displaying corresponding records, but,

If i enter input as "test1 ref" or "TEST1 REF" or "TEST1 ref" and so on , it is not displaying the records..

I hope u understand my problem.

Exact problem is i need to enter the input as it is stored in the table(case sensitive)

[Aiolos]

when you input "test1 ref" or "TEST1 REF" or "TEST1 ref" and so on , you also want to get the records? is it?

if yes, it is impossible to achieve this.

[Former Member]

Hi Yang,

Yes exactly, i just want to avoid the case sensitivity.

[Aiolos]

hi Vinod,

the domain of VBKD-IHREZ is TEXT12, which is Lowercase letters allowed.

this means in table VBKD, both Lowercase and Uppercase for field VBKD-IHREZ are possible.

so how can you avoid this?

when you input test1, how does system know that you want to find entries with 'TEST1' or 'Test1' and so on.

[Former Member]

Hi Yang,

For 'test1' if they enter 'Test1 or TEST1'  i have to get the records. is that possible?

[Aiolos]

Hi Vinod,

it is impossible. if lowercase is check, it means uppercase and lowercase are strictly distinct.

so the select will only get the entry which is exactly equal to your input.

[SuhaSaha]

Hello,

Aiolos Yang wrote:
the domain of VBKD-IHREZ is TEXT12, which is Lowercase letters allowed.
this means in table VBKD, both Lowercase and Uppercase for field VBKD-IHREZ are possible.

This is not correct w.r.t the way data is stored in the DB. As i have already mentioned, "Lower Case" checkbox in the domain is applicable only for screen elements.

If the "Lower case" checkbox is not checked, when the screen conversion occurs the contents are converted to Upper case. But at the DB layer the field is case-sensitive.

Please see the below code to understand the concept. The field CARRID doesn't have "Lower Case" checked, but when the below code is executed all the 3 values are stored w/o any exceptions:

   DATA: gs_carr     TYPE scarr,
      gx_sql_err  TYPE REF TO cx_sy_open_sql_db,
      gv_errtxt   TYPE string.

*X capital
gs_carr-carrid    = `Xy`.
gs_carr-carrname  = `X Airlines`.

TRY .
    INSERT scarr FROM gs_carr.
    CLEAR gs_carr.
  CATCH cx_sy_open_sql_db INTO gx_sql_err.
    gv_errtxt = gx_sql_err->get_text( ).
    WRITE: / 'Carrier ID:', 15 gs_carr-carrid,
           / 'SQL Error:' , 15 gv_errtxt.
ENDTRY.

*Y capital
gs_carr-carrid    = `xY`.
gs_carr-carrname  = `Y Airlines`.

TRY .
    INSERT scarr FROM gs_carr.
    CLEAR gs_carr.
  CATCH cx_sy_open_sql_db INTO gx_sql_err.
    gv_errtxt = gx_sql_err->get_text( ).
    WRITE: / 'Carrier ID:', 15 gs_carr-carrid,
           / 'SQL Error:' , 15 gv_errtxt.
ENDTRY.

*Z capital
gs_carr-carrid    = `XY`.
gs_carr-carrname  = `XY Airlines`.

TRY .
    INSERT scarr FROM gs_carr.
    CLEAR gs_carr.
  CATCH cx_sy_open_sql_db INTO gx_sql_err.
    gv_errtxt = gx_sql_err->get_text( ).
    WRITE: / 'Carrier ID:', 15 gs_carr-carrid,
           / 'SQL Error:' , 15 gv_errtxt.
ENDTRY.

Hope i'm able to clarify my point.

BR,

Suhas

PS: My database is ORACLE release 10.2.0.5.0.

[Aiolos]

hi Suhas, yes you are right. Thanks.

[SuhaSaha]

Hello Vinod,

This is a FAQ in the forums - did you search before posting your question?

Removing the "Lower Case" in the domain is not a solution to remove the case-sensitivity of a field. This applies only to the screen fields referencing this domain.

If you have an Oracle DB, you use Native SQL to perform a case insensitive search - .

BR,

Suhas

[Former Member]

Hello suhas,

Ya made search, but i didn't get the answer

Thanks,

vinod.

[SuhaSaha]

Hello Vinod,

Did you check the thread i had referenced in my previous response?

In our system table VBKD has the following entries for IHREZ field

If you're on Oracle database you can use the "Upper" function via the Native SQL interface to fetch case-sensitive data.

PARAMETERS: p_ihrez TYPE ihrez OBLIGATORY DEFAULT 'Test Ref1'. "Your reference

TYPES:
BEGIN OF gty_sord,
  vbeln TYPE vbeln,
  posnr TYPE posnr,
  ihrez TYPE ihrez,
END OF gty_sord.

DATA: gs_sord           TYPE gty_sord,
      gt_sord           TYPE STANDARD TABLE OF gty_sord,
      gx_native_sql_err TYPE REF TO cx_sy_native_sql_error,
      gv_errtxt         TYPE string.

* Use the 'Upper' function in Oracle SQL to convert the contents to Uppercase
TRY .
    EXEC SQL PERFORMING f_append.
      SELECT vbeln, posnr, ihrez
        INTO :gs_sord
        FROM vbkd
        WHERE upper(ihrez) LIKE upper(:p_ihrez)
    ENDEXEC.
  CATCH cx_sy_native_sql_error INTO gx_native_sql_err.
    gv_errtxt = gx_native_sql_err->get_text( ).
    MESSAGE gv_errtxt TYPE 'I'.
ENDTRY.

LOOP AT gt_sord INTO gs_sord.
  WRITE: / gs_sord-vbeln,
        13 gs_sord-posnr,
        21 gs_sord-ihrez.
ENDLOOP.

*&---------------------------------------------------------------------*
*&      Form  f_write
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM f_append.
  APPEND gs_sord TO gt_sord.
ENDFORM.                    "f_write

The native SQL will fetch the case-sensitive IHREZ from VBKD & the output is as below:

BR,

Suhas