Select Query issue. a% and A % both should work.

[Former Member]

hi,

I have to selet records from database table based on local variable values.

Loal variable - Lv.

If lv = 'a*' then i have to select all records which starts with a.

I have Replaced all occurences of * with % and then i have used a select query :

select F1 from <table>

into table <internal table>

where C1 like lv.

Now problem is this select query picks values which are like a% not like A%.

I want to select all values whether it starts with a or A.

How to proceed ?

[Former Member]

Looks like your WHERE CLAUSE field C1 is a case sensitive field which means a* and A* are treated differently. Build your query as WHERE C1 LIKE 'a%' OR C1 LIKE 'A%'.

[Former Member]

hi,

Thanks for your reply.

But one problem.

You said :

Looks like your WHERE CLAUSE field C1 is a case sensitive field which means a and A* are treated differently. Build your query as WHERE C1 LIKE 'a%' OR C1 LIKE 'A%'.*

where class field C1 is Case senstive - What does it mean ? Can i change the field to make it case insensitve ?

Secondly, I may write conditon like you mentioned : like a% or like A% but the problem is value is in variable lv.

And lv can contain Ab% or aaB%. then your provided solution wont work.

Any other inputs ?

[Former Member]

This has been asked and answered many times before. What is the table and field you are SELECTing?

Rob

[Former Member]

Hi Saurav Mago,

Creates a range (r_lv) with the values: a* and A*. Change the Where condition:

select F1 from

where C1 in r_lv.

Hope this information is help to you.

Regards,

José

[Former Member]

Hi,

A select query on explicit where condition a% wont work for A% always. In some standard SAP tables new fields are added where all the data is converted to UPPERCASE to facilitate the working of both a% and A%. For example Material Description is stored like this as a solution by SAP. But in most cases its not maintained,

If you are retrieving records from a Z TABLE, then add a new field with the data of the required field converted to UPPERCASE. Else as suggested use OR condition with both lower and upper case in the LIKE condition of where.

Regards,

Vikranth

[Former Member]

hi Vikrant,

Making a new field with uppercase seems gud option but i would like to have some other option.

[Former Member]

The answer depends on the table and field you are selecting. Can you provide the details??

Rob

[Former Member]

Hi Rob,

The table is Z table from which data needs to be selected.

But making new field in table with Uppercase is not feasible and also

maintaining table fields in Uppercase is not .

Any inputs ?

[Former Member]

Depending on your database, you should be able to translate the database value to upper case using native SQL in the WHERE clause.

Rob

[Former Member]

Hi,

Check this example for searching using Native SQL if ur DB is Oracle

EXEC SQL.
  SELECT *
  INTO :ITAB
  FROM  ZTABLE
  WHERE UPPER(FIELD1)  =  :LV_VARIABLE
ENDEXEC.

Regards,

Vikranth

[Former Member]

If it is custom table, why is it difficult to change the domain? If that is not feasible, you will have to either use the OPEN SQL commands as suggested above or get the records based on other columns and then loop at them, change both(database and your variable) to uppercase and compare.

[Former Member]

>

>  OPEN SQL 

Or NATIIVE SQL?

Rob

[Former Member]

Hi,

use this code.

data: lv(2).
data: lv1(2).

lv = 'a%'.
lv1 = 'A%'.

select F1 
  from <table>
    into table <internal table>
where C1 like lv
       or C1 like lv1.