Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos
212

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 ?

1 ACCEPTED SOLUTION

Former Member
0 Kudos
105

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%'.

13 REPLIES 13

Former Member
0 Kudos
106

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%'.

0 Kudos
105

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
0 Kudos
105

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

Rob

Former Member
0 Kudos
105

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
0 Kudos
105

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

0 Kudos
105

hi Vikrant,

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

0 Kudos
105

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

Rob

0 Kudos
105

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 ?

0 Kudos
105

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

Rob

0 Kudos
105

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

0 Kudos
105

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.

0 Kudos
105

>

> OPEN SQL

Or NATIIVE SQL?

Rob

Former Member
0 Kudos
105

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.