topic Re: select statement in Application Development and Automation Discussions

select statement

Former Member — Thu, 16 Aug 2007 05:25:22 GMT

Hi Fnds,

I am having a problem in selection statement,

In selection screen s_zvs_name field is 3 characters length,

and the field zzptg in marc table is 9 characters length,

Now i want to select the records from marc where the 1st 3 characters will be the s_zvs_name.

I m trying like this, but it is not accepting.

select matnr werks ekgrp zzptg from marc into table it_marc

where matnr in s_matnr

and werks in s_werks

and ekgrp in s_ekgrp

and zzptg+0(3) in s_vs_name.

Plz give me the correct solution for this.

Re: select statement

Former Member — Thu, 16 Aug 2007 05:31:01 GMT

Do one thing

select zzptg from marc into table itab.

now loop at itab.

itab1-fld1 = itab-zzptg+0(3).

append itab1.

claer itab ,itab1.

endloop.

and proceed with these data

reward points to all helpful answers

kiran.M

Re: select statement

Former Member — Thu, 16 Aug 2007 05:31:44 GMT

Use this code.


data lv_zzptg type marc-zzptg.

concatenate s_vs_name '%' into lv_zzptg.

select matnr werks ekgrp zzptg from marc into table it_marc
where matnr in s_matnr
and werks in s_werks
and ekgrp in s_ekgrp
and zzptg like lv_zzptg.

Please mark points if the solution was useful.

Regards,

Manoj

Message was edited by:

Manoj V Kumar

Re: select statement

Former Member — Thu, 16 Aug 2007 06:10:16 GMT

Hi Manoj,

No, it's not working.....

Re: select statement

Former Member — Thu, 16 Aug 2007 06:15:49 GMT

Hi kiran,

How it will work,

In selection statement itself i want to restrict the no.of entries by using the field z_vs_name.

Re: select statement

Former Member — Thu, 16 Aug 2007 06:23:37 GMT

Hi,

You can't have offset operations on SELECTS in the WHERE clause for the fields in the table. So it is causing you problem here. You need to have this condition separately rather than in the SELECT. First get the data into internal table without this condition and later have this condition on internal table.

Check this code.

select matnr werks ekgrp zzptg from marc into table it_marc

where matnr in s_matnr

and werks in s_werks

and ekgrp in s_ekgrp.

IF IT_MARC[] IS NOT INITIAL.

LOOP AT IT_MARC.

IF IT_MARC-ZZPTG+0(3) IN S_VS_NAME.

APPEND IT_MARC TO IT_FINAL.

CLEAR IT_FINAL.

ENDIF.

ENDLOOP.

ENDIF.

<b>Friendly Note:</b> You have many open threads and Plz close the threads if they are answered/solved and reward points to the people who are helping you by taking their time.

Thanks,

Vinay

Re: select statement

Former Member — Thu, 16 Aug 2007 06:59:07 GMT

Hi,

it will work, but only for special input values. As far as I understood you wright, I assume that you have defined s_zvs_name as a range. Therefore you have to create a where clause out of your range. You have to loop over your range and append to the where clause. For a equality row you could use the concatenate with '%', for between you have to extend with extremes (e.g. for ABC - MNO you have to write BETWEEN 'ABCAAAAAA ' and 'MNOZZZZZZ' - if the field contains only upper case characters). For a pattern you could use it directly in the LIKE.

Hope that helps.

Regards

Ralph

Re: select statement

Former Member — Thu, 16 Aug 2007 10:56:36 GMT

If you redefine the selection screen SELECT-OPTIONS field S_ZVS_NAME to be the same type as the database field MARC-ZZPTG, and then have the user input the value as 3 characters followed by * you will get the records you want.

If you must have only 3 characters on the selection screen, you will need to convert the input 3 character SELECT-OPTIONS table into a program RANGES table of 9 characters, putting an * in position 4. This is not simple, as if the user inputs a range say AAA to ABC you cannot covert this to AAA* to ABC* as wildcards are not allowed in ranges. Simplest to have the user input values matching the table field definition.

Andrew

Re: select statement

Former Member — Fri, 17 Aug 2007 07:15:03 GMT

you can either write

select matnr werks ekgrp zzptg

from marc

into table it_marc

where matnr in s_matnr

and werks in s_werks

and ekgrp in s_ekgrp

and zzptg like s_vs_name1

or zzptg like s_vs_name2.

Then you must change the s_vs_name into 3 character + anything, i.e. 'ABC%'

Or you do

select matnr werks ekgrp zzptg

from marc

into wa

where matnr in s_matnr

and werks in s_werks

and ekgrp in s_ekgrp.

check ( wa-zzptg+0(3) in s_vs_name ).

append wa to it_marc.

endselect

The check is usually not recommended, however, if check also conditions, whcih are not possible in the where condition, then it is o.k. And I don't think that zzptg is in an index, if it is then you should try option 1.

Siegfried

Re: select statement

Former Member — Fri, 17 Aug 2007 16:26:09 GMT

write the code in initialization event then it works because it got executed before selection screen