topic Re: select statment - where clause in Application Development and Automation Discussions

select statment - where clause

Former Member — Wed, 08 Apr 2009 15:26:10 GMT

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 ???

Re: select statment - where clause

Former Member — Wed, 08 Apr 2009 15:43:47 GMT

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.

Re: select statment - where clause

former_member156446 — Wed, 08 Apr 2009 15:51:05 GMT

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

Re: select statment - where clause

Former Member — Wed, 08 Apr 2009 20:39:57 GMT

Very easy if you use native SQL.

Rob

Re: select statment - where clause

Former Member — Thu, 09 Apr 2009 05:42:54 GMT

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.

Re: select statment - where clause

Former Member — Thu, 09 Apr 2009 06:24:59 GMT

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

Re: select statment - where clause

Former Member — Thu, 16 Apr 2009 05:21:39 GMT

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.

Re: select statment - where clause

Former Member — Thu, 16 Apr 2009 05:45:22 GMT

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

Re: select statment - where clause

Former Member — Thu, 16 Apr 2009 07:19:21 GMT

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

Re: select statment - where clause

Former Member — Mon, 20 Apr 2009 05:56:25 GMT

This message was moderated.

Re: select statment - where clause

Former Member — Mon, 20 Apr 2009 10:24:46 GMT

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

Re: select statment - where clause

Former Member — Wed, 22 Apr 2009 10:58:41 GMT

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

Re: select statment - where clause

Former Member — Thu, 30 Apr 2009 07:35:57 GMT

studying.....

bob:

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