select statment - where clause

[Former Member]

Hello experts,

I want to write a select statment,

e.g.

select * from pa0001 where ename like '%Rahul%'.

so this stamemt will give me all DB rows in which ename contains Rahul but i also want rows those are having rahul or rAhul or raHul or RAhul or RAHul or RaHul like wise....

can u plz help me on this ???

[Former Member]

Hello experts,

I want to write a select statment,

e.g.

select * from pa0001 where ename like '%Rahul%'.

so this stamemt will give me all DB rows in which ename contains Rahul but i also want rows those are having rahul or rAhul or raHul or RAhul or RAHul or RaHul like wise....

can u plz help me on this ???

[Former Member]

Hi,

It is not possible to search the pernr's using the ename as data in the PA0001-ENAME is case sensitive so you need to search the name in the same case as it is stored.

You have two options..

1. first fetch the data from the PA0001 and covert ename in all the records to uppercase and then search in the uppercase..now you get all the list of pernr's for the given part name.

2. use the PA0002 to find the pernr using NCHMC VNAMC fields as these two fields stores the last name and first name in upper case(generally sap uses these fields as seach field to find the pernr for the given name).

If you compare NACHN & VORNA with NCHMC & VNAMC ..NACHN & VORNA stores the data in the same case as user entered where as NCHMC & VNAMC stores in Upper case...so use these field for search.

You don't find such match code field for ename in PA0001.

[former_member156446]

Hi Rahul, check this code which I wrote for you:

REPORT  zj_test LINE-SIZE 162.

PARAMETER: pa_name TYPE text40.

DATA: ra_name TYPE STANDARD TABLE OF selopt,
      wa_name TYPE selopt,
      lv_name TYPE text40,
      lv_i TYPE i, lv_count TYPE i.

lv_name = pa_name.

lv_i = STRLEN( lv_name ).

DO lv_i TIMES.
  TRANSLATE lv_name+lv_count(1) TO UPPER CASE.
  wa_name-sign = 'I'.
  wa_name-option = 'EQ'.
  wa_name-low = lv_name.
  APPEND wa_name TO ra_name.   " ra_name in select (where ename in ra_name).
  WRITE: wa_name-low.
  lv_name = pa_name.
  lv_count = lv_count + 1.
ENDDO.

Input:<<< rahul.

output:>>>
Rahul
rAhul
raHul
rahUl
rahuL

[Former Member]

Very easy if you use native SQL.

Rob

[Former Member]

Hi Rob,

Can u plz explain me how to use native sql.

Hi Avinash,

Dont focus on table....

my need is all rows with the selection criteria is not case sensitive.

[Former Member]

write your select query inside the block:

EXEC(Query)ENDEXEC.

[Refer Native Sql|http://help.sap.com/abapdocu/en/ABAPEXEC_IMPLICIT.htm]

[Refer Native Sql|http://help.sap.com/abapdocu/en/ABAPEXEC.htm]

Regards,

Gurpreet

[Former Member]

studying.....

bob:

i want to know how to write the sentence by native sql.

[Former Member]

Hi,

try this:-

tables EVER.

DATA : IT_EVER LIKE STANDARD TABLE OF EVER WITH HEADER LINE.

ranges : R_ERNAM FOR EVER-ERNAM.

R_ERNAM-SIGN = 'I'.

R_ERNAM-OPTION = 'CP'.

R_ERNAM-LOW = 'RAHUL'. " here rahul is with any case of character

APPEND R_ERNAM.

SELECT * FROM EVER INTO TABLE IT_EVER WHERE ERNAM IN R_ERNAM.

BREAK-POINT.

[Former Member]

Hi,

Normally we don't use the character field for fetching data in select statement. Instead of name use Emp no

(Pernr) in your select.

Select * from pa0001 into corresponding field of itab where pernr = S_pernr.

• Use S_pernr in selection screen.

Regards,

Himanshu

[Former Member]

hi,

select all names form pa0001 into internal table and convert the names to upper case .

search according to u r requirement it will work..

[Former Member]

This message was moderated.

[Former Member]

Hi Rahul,

1)write select statement considering all names form pa0001 into internal table and then convert the ename fields data ie names to upper case.

2)Then loop at above internal table with where condition as '%rahul%' and only data which have ename as rahul fetched . move the related fields data to a new internal table and append the table.

3)New appended table has the required data.

Check the above and hope this will solve the problem.

Thanks,

Vengal rao

[Former Member]

Hi

Find the below code which may be useful for your requirement

ranges:r_sel for lfa1-name1.

data:v_name(5) type c value 'laxmi'.

data:len type i,

v_num type i,

v_num1 type i,

v_num2 type i,

v_name1(5) type c,

v_name2(5) type c,

v_name_final(7) type c,

v_char type c.

len = strlen( v_name ).

clear:v_name1,v_name2,v_name_final,v_num,v_num1,v_num2.

v_num1 = len.

do len times.

v_num1 = v_num1 - 1.

v_char = v_name+v_num(1).

v_num2 = v_num.

v_num = v_num + 1.

if v_num1 is initial.

clear:v_name1.

else.

v_name1 = v_name+v_num(v_num1).

endif.

if v_num2 is not initial.

v_name2 = v_name+0(v_num2).

endif.

translate v_char to upper case.

concatenate '' v_name2 v_char v_name1 '' into v_name_final.

r_sel-option = 'CP'.

r_sel-sign = 'I'.

r_sel-low = v_name_final.

clear:r_sel-high.

append r_sel.

translate v_char to lower case.

concatenate '' v_name2 v_char v_name1 '' into v_name_final.

r_sel-option = 'CP'.

r_sel-sign = 'I'.

r_sel-low = v_name_final.

clear:r_sel-high.

append r_sel.

enddo.

Use range parameter in your select statement.

Regards

Sripal