topic Re: Select querry in Application Development and Automation Discussions

Select querry

Former Member — Fri, 22 Dec 2006 09:14:43 GMT

I would like to get all stock information

My selections screen contains

three materials like:

s_matnr1

....

s_matnr2

....

s_matnr3

.....

So , I have written querry like this:

but getting values only for one matnr at a time.

SELECT a_{matnr b}werks b_{labst b}umlme
b_{insme b}einme b_{speme b}klabs
b_{kinsm b}keinm b_{kspem b}retme
INTO CORRESPONDING FIELDS OF TABLE it_result
FROM mara AS a INNER JOIN mard AS b
ON a_{matnr = b}matnr
WHERE b~matnr IN s_matnr1 AND
b~matnr IN s_matnr2 AND
b~matnr IN s_matnr3 AND
b~werks IN s_werks.

I want to get the stock values for all matnrs: matnr1 ,matnr2 &matnr3

please help me ...

Re: Select querry

Former Member — Fri, 22 Dec 2006 09:17:29 GMT

select-option s_matnr for mara-matnr.

SELECT a~matnr b~werks b~labst b~umlme
b~insme b~einme b~speme b~klabs
b~kinsm b~keinm b~kspem b~retme
FROM mara AS a INNER JOIN mard AS b
 ON a~matnr = b~matnr
WHERE b~matnr IN s_matnr
 b~werks IN s_werks.

put all the three material in select option it takes it.

kishan negi

Re: Select querry

Former Member — Fri, 22 Dec 2006 09:18:23 GMT

i think it shud work -

SELECT a_{matnr b}werks b_{labst b}umlme

b_{insme b}einme b_{speme b}klabs

b_{kinsm b}keinm b_{kspem b}retme

INTO CORRESPONDING FIELDS OF TABLE it_result

FROM mara AS a INNER JOIN mard AS b

ON a_{matnr = b}matnr

WHERE b~matnr IN s_matnr1 AND

b~werks IN s_werks. "hope u r supplying correct value in WERKS

Message was edited by:

Amit Tyagi

Message was edited by:

Amit Tyagi

Re: Select querry

graghavendra_sharma — Fri, 22 Dec 2006 09:18:39 GMT

instead of this

WHERE b~matnr IN s_matnr1 AND

b~matnr IN s_matnr2 AND
b~matnr IN s_matnr3 AND
b~werks IN s_werks

USE THIS CODE:

WHERE ( b~matnr IN s_matnr1 OR

b~matnr IN s_matnr2 OR
b~matnr IN s_matnr3 ) AND
b~werks IN s_werks

Re: Select querry

Former Member — Fri, 22 Dec 2006 09:19:04 GMT

Append all your Materials from Selection screen into a single variable.

RANGES : R_MATNR FOR MARA-MATNR.

APPEND S_MATNR1[] TP R_MATNR.
APPEND S_MATNR2[] TP R_MATNR.
APPEND S_MATNR3[] TP R_MATNR.

 SELECT a~matnr b~werks b~labst b~umlme
 b~insme b~einme b~speme b~klabs
 b~kinsm b~keinm b~kspem b~retme
 INTO CORRESPONDING FIELDS OF TABLE it_result
 FROM mara AS a INNER JOIN mard AS b
 ON a~matnr = b~matnr
 WHERE b~matnr IN R_matnr1 AND
 b~werks IN s_werks.

Regards

Ravi

Note - Please mark all the helpful answers

Re: Select querry

jayanthi_jayaraman — Fri, 22 Dec 2006 09:19:50 GMT

Hi,

select ...

WHERE ( b~matnr IN s_matnr1 <b>or</b>

b~matnr IN s_matnr2 <b>or</b>

b~matnr IN s_matnr3 ) and

b~werks IN s_werks.

Re: Select querry

Former Member — Fri, 22 Dec 2006 09:31:24 GMT

I tried with this code:

( b~matnr IN s_matnr1 or

b~matnr IN s_matnr2 or

b~matnr IN s_matnr3 ) and

b~werks IN s_werks.

but querry failing in getting the data. but there are values is table.

Here Plant is only mandatory field.

any inputs?

Re: Select querry

Former Member — Fri, 22 Dec 2006 09:32:35 GMT

try this code and give only one selct option for material number -

SELECT a_{matnr b}werks b_{labst b}umlme

b_{insme b}einme b_{speme b}klabs

b_{kinsm b}keinm b_{kspem b}retme

INTO CORRESPONDING FIELDS OF TABLE it_result

FROM mara AS a INNER JOIN mard AS b

ON a_{matnr = b}matnr

WHERE b~matnr IN s_matnr1 AND

b~werks IN s_werks. "hope u r supplying correct value in WERKS