topic case sensitivity problem in select in Application Development and Automation Discussions

case sensitivity problem in select

Former Member — Thu, 17 Jul 2008 14:54:47 GMT

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

Re: case sensitivity problem in select

Former Member — Thu, 17 Jul 2008 15:05:16 GMT

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

Re: case sensitivity problem in select

naimesh_patel — Thu, 17 Jul 2008 15:09:54 GMT

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

Re: case sensitivity problem in select

Former Member — Thu, 17 Jul 2008 15:12:04 GMT

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.

Re: case sensitivity problem in select

Former Member — Thu, 17 Jul 2008 15:58:41 GMT

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.

Re: case sensitivity problem in select

Former Member — Thu, 17 Jul 2008 16:05:17 GMT

Hello Vikas Yadav

Can you show the statement?

That could be easy to do analyse.

Thanks.

Wiparat

Re: case sensitivity problem in select

Former Member — Thu, 24 Jul 2008 15:41:48 GMT

Thanks Rob I used your method..