Convert both parameter and table value to Upper case in Select statement

[Former Member]

I have a select statement with where clause. I want to make it not case sensitive. I want to able to convert the value coming form table to upper case and compare it against the parameter that is converted to upper case.

Can some one please give me syntax for that. I know in ABAP there is TRANSLATE statement but I am not sure how to use it in Where clause.

Ex:

WHERE UPPERCASE(fieldName) like UPPERCASE(parameter)

Thanks

[Former Member]

I have a select statement with where clause. I want to make it not case sensitive. I want to able to convert the value coming form table to upper case and compare it against the parameter that is converted to upper case.

Can some one please give me syntax for that. I know in ABAP there is TRANSLATE statement but I am not sure how to use it in Where clause.

Ex:

WHERE UPPERCASE(fieldName) like UPPERCASE(parameter)

Thanks

[Former Member]

HI,

You can use translate statement .

Thanks,

Sriram Ponna.

[Former Member]

You either have to use native SQL to do this or find another field in the table that is not case sensitive.

What table and field are you looking at?

Rob

[Former Member]

The select in inside a Function Module that is getting called from Portal Web Dynpro screen. I am searching Master Data tables in BI.

I can control the parameter to be converted to upper case, but as i mentioned to compare it I want to be able compare it against the table values to return the results.

Is there a setting to make a funciton module results case insensitive.

The last option I have is to load all master data in BI in upper case. I want to use that as a last option.

Thanks

[Former Member]

Native SQL is slower, but in this case, probably what you need.

Rob

[Former Member]

Thanks..I will try that.

[Former Member]

Something like:

report ztest no standard page heading.

tables lfa1.

data: name like lfa1-name1 value 'NISSAN'.

EXEC SQL.
  SELECT *
  INTO :LFA1
  FROM  LFA1
  WHERE UPPER(NAME1)  =  :NAME
ENDEXEC.

Rob

[Former Member]

Ravi,

In domain, see if the flag LOWER CASE is flagged.

If this indicator is set, upper case and lower case are distinguished when you enter values with screen masks.

Otherwise all the letters entered will be converted to upper case when you enter values with a screen mask.

This mechanism is in effect for all the fields referring to this domain.

Hope this helps

Vinodh Balakrishnan

[Former Member]

Hi

U can only translate the value before selecting the data:

TRANSLATE <FIELD> TO UPPER CASE.

But you can't do it while selecting the record, so

TRANSLATE <FIELD> TO UPPERCASE.

SELECT * FROM <TABLE> WHERE FIELD = <FIELD>

But you're sure the table field has only uppercase string.

Max