case sensitivity problem in select

[Former Member]

Hello Experts,

In my scenario KNA1-NAME1 data stored as "Washington DC" mixture of capital and small letters, because domain lowercase property checked by default. I need to pick all KNA1 data via RFC FM by passing NAME1 field.

Input parameter could be anything like "WASHINGTON", "Washington", "Washington" or "WASHINGTON DC".

I am using like with connected % sign both side in my select statement, in case if input come as "Washington". system shows data but in other case system does not show any data.

There is no conversion routine with domain.

Tried upper/ucase in select statement, but system is giving syntax error.

I tried native sql also but i am getting short dump.

Database server is MSSQL.

If anyone has done this before, Please help me.

Vikas

[Former Member]

The easiest solution to this is to SELECT MCOD1 rather than NAME1. It is a slightly shorter upper case translation of that field used in search helps.

Rob

[Former Member]

Thanks Rob I used your method..

[naimesh_patel]

You can select all the records into the internal table, convert the internal table records to UPPER CASE and convert the inputs to UPPER CASE and delete the recoreds which are not in the Inputs from internal table.

You can move the original content to other column for future display.

Like:

SELECT NAME1 INTO ITAB FROM KNA1.

LOOP AT ITAB.
  ITAB-NAME_TMP = ITAB-NAME1.
  TRANSLATE ITAB-NAME_TMP TO UPPER CASE.
  MODITY ITAB.
ENDLOOP.

* fill range with the UPPER CASE input 
R_NAME1-LOW = INPUT.
TRANSLATE R_NAME1-LOW  TO UPPER CASE.
APPEND R_NAME1.

DELETE ITAB WHERE NAME_TMP NOT IN R_NAME1.

This costs the performance.

Regards,

Naimesh Patel

[Former Member]

I would probably use an intermediate variable to pass on the value that is working to the select.

case input_value:

when "WASH...'

intermdediate_val = "Washington"

when "washington"

intermdediate_val = "Washington"

....

... end case

select from kna1 where name1 = intermeidate_val.

[Former Member]

ls_name-name = 'WASHINGTON'.

APPEND ls_name to lt_name.

ls_name-name = 'Washington'.

APPEND ls_name to lt_name.

select * from <dbtab> INTO table <int_tab> FOR ALL ENTRIES IN lt_name where <dbtab-field> = lt_name-name.

[Former Member]

Hello Vikas Yadav

Can you show the statement?

That could be easy to do analyse.

Thanks.

Wiparat