topic Re: how to write select statement ? in Application Development and Automation Discussions

how to write select statement ?

Former Member — Wed, 19 Sep 2007 02:29:22 GMT

Hi,

i have requirement like below :

In selection screen we have material number as selection options and plant as single entry.

Based on the input.

We have to extract the material number and plant based on MTART = AA or UT in MARA table and STLAN = 1 in MAST table.

how to write a select statment to pick values from selection option and extract those material and plant which fullfill above conditions.

Please let me know..

its urgent

please help..

i have written code like below..

types: begin of x_it_mat,

matnr type mkal-matnr,

werks type mkal-werks,

stlan type mast-stlan,

end of x_it_mat.

types: begin of x_it_mat1,

matnr type mkal-matnr,

werks type mkal-werks,

mtart type mara-mtart,

end of x_it_mat1.

data : it_mat type table of x_it_mat,

wa_mat like line of it_mat.

data : it_mat1 type table of x_it_mat1,

wa_mat1 like line of it_mat1.

TABLES: MKAL , MAST , MARA.

SELECTION-SCREEN BEGIN OF BLOCK matnr

WITH FRAME TITLE TEXT-001.

select-options :

so_matnr for mkal-matnr.

SELECTION-SCREEN END OF BLOCK matnr.

SELECTION-SCREEN BEGIN OF BLOCK plant

WITH FRAME TITLE TEXT-002.

parameter p_werks type mkal-werks.

SELECTION-SCREEN END OF BLOCK plant.

select matnr

werks

stlan

from mkal INTO table it_mat

where matnr in so_matnr

and werks = p_werks.

after this how to write another select statement to extract material by satisfiying above 2 conditions..

Re: how to write select statement ?

Former Member — Wed, 19 Sep 2007 02:40:11 GMT

Check the below code :

tables : mara,

mast.

data : begin of i_data occurs 0,

matnr like mara-matnr,

end of i_data.

select-options : s_matnr for mara-matnr.

parameters p_werks like mast-werks.

start-of-selection.

select a~matnr into table i_data

from mara as a inner join mast as b on a_{matnr = b}matnr

where b~werks = p_werks

and a~matnr in s_matnr

and a~mtart = 'AA'

or a~mtart = 'UT'

and b~stlan = '1'.

loop at i_data.

write:/ i_data-matnr.

endloop.

Thanks

Seshu

Re: how to write select statement ?

Former Member — Wed, 19 Sep 2007 02:56:18 GMT

hi seshu,

thanks you very much for your sample. i have small change in question. If plant is also having selection opiton.

at final i want internal table with material number and plant which satisfying all those conditions..

please help....

Re: how to write select statement ?

Former Member — Wed, 19 Sep 2007 02:57:40 GMT

Hi SRM

That 's your requirements , you need to add more code like ( ( a_{mtart = 'AA' ) OR ( a}mtart = 'UT') ) , otherwise system will select material that have condition a_{mtart = 'AA' or a}mtart = 'UT' by igonre another condition.

SELECT b_{matnr b}werks

INTO CORRESPONDING FIELDS OF TABLE it_mat1

FROM mara AS a

INNER JOIN mast AS b

ON b_{matnr = a}matnr

WHERE ( ( a_{mtart = 'AA' ) OR ( a}mtart = 'UT') ) AND

b~stlan = 1 AND

b~werks in p_werks.

Regards

Wiboon

Message was edited by:

Wiboon Chaiyabutsakul

Message was edited by:

Wiboon Chaiyabutsakul

Re: how to write select statement ?

Former Member — Wed, 19 Sep 2007 03:01:55 GMT

Hi ,

Please check the below code and modified plant as select-option

Check the below code :

tables : mara,

mast.

data : begin of i_data occurs 0,

matnr like mara-matnr,

end of i_data.

select-options : s_matnr for mara-matnr,

<b>S_werks for mast-werks.</b>

start-of-selection.

select a~matnr into table i_data

from mara as a inner join mast as b on a_{matnr = b}matnr

where <b>b~werks in s_werks</b>

and a~matnr in s_matnr

and a~mtart = 'AA'

or a~mtart = 'UT'

and b~stlan = '1'.

loop at i_data.

write:/ i_data-matnr.

endloop.

Hope you got it.

Thanks

Seshu