topic Re: Convert both parameter and table value to Upper case in Select statement in Application Development and Automation Discussions

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

Former Member — Mon, 21 Jan 2008 15:55:33 GMT

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

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

Former Member — Mon, 21 Jan 2008 16:00:35 GMT

HI,

You can use translate statement .

Thanks,

Sriram Ponna.

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

Former Member — Mon, 21 Jan 2008 16:00:50 GMT

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

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

Former Member — Mon, 21 Jan 2008 16:00:59 GMT

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

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

Former Member — Mon, 21 Jan 2008 16:07:20 GMT

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

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

Former Member — Mon, 21 Jan 2008 16:22:50 GMT

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

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

Former Member — Mon, 21 Jan 2008 16:44:01 GMT

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

Rob

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

Former Member — Mon, 21 Jan 2008 17:27:53 GMT

Thanks..I will try that.

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

Former Member — Mon, 21 Jan 2008 17:32:59 GMT

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