topic Re: Problem with fetching data from Marc in Application Development and Automation Discussions

Problem with fetching data from Marc

Former Member — Tue, 03 Apr 2012 12:46:05 GMT

I struct with a problem that i need to get the all materials which present in all 3 plants ( say 3000,3010,3100)

but we can't write like

select matnr werks from marc into table t_mara UP TO 100 ROWS WHERE werks = '3000'
and werks = '3010'
and werks = '3100' .

because at a time werks will be 1 value only.

can we achieve this with 1 or atleast 2 select queries?

or there is any other way.

Plaese give me the reference on this so that i achieve this.

Regards

Chaitanya

Re: Problem with fetching data from Marc

erik_mertens3 — Tue, 03 Apr 2012 12:51:13 GMT

Hi Krishna,

Instead of .. and ... and .. you should use .. or .... or ...

Or you could use "werks in ('3000','3010','3100')".

Re: Problem with fetching data from Marc

Former Member — Tue, 03 Apr 2012 12:53:57 GMT

Hi Erik,

wantdly i wrote it. because i want material which should present in all 3 plants but not in either of them.

Regards

Chaitanya

Re: Problem with fetching data from Marc

erik_mertens3 — Tue, 03 Apr 2012 13:03:19 GMT

ok, then you have to use two select statement (or two internal tables).

In the outer select you process the material, in the inner select you check if the material is present in all 3 plants. If so, append it to an internal table. Easiest way imho.

Re: Problem with fetching data from Marc

Former Member — Tue, 03 Apr 2012 13:05:39 GMT

You should be able to do this with a sub-query. Press F1 on SELECT.

Rob

Re: Problem with fetching data from Marc

Former Member — Tue, 03 Apr 2012 13:11:30 GMT

Hi,

You can JOIN 3 tables in one query:

SELECT m1~matnr

UP TO 100 ROWS

INTO TABLE t_mara[]

FROM marc AS m1

JOIN marc AS m2

ON m1~matnr EQ m2~matnr

JOIN marc AS m3

ON m1~matnr EQ m3~matnr

WHERE m1~werks EQ '3000'
AND m2~werks EQ '3010'
AND m3~werks EQ '3100' .

Regards,

Przemysław

Re: Problem with fetching data from Marc

Private_Member_49934 — Tue, 03 Apr 2012 13:13:51 GMT

I think there is no other way other than to handle it programatically. Something like the below code!

select matnr werks from marc into table  t_mara  UP TO 100 ROWS WHERE werks = '3000'
                                      and werks = '3010'
                                 and werks = '3100' .
t_mara_3000 = t_mara. delete t_mara_3000 where werks <> '3000'. 
t_mara_3010 = t_mara. delete t_mara_3010 where werks <> '3010'.
t_mara_3110 = t_mara. delete t_mara_3110 where werks <> '3100'.
clear : t_mara.
describe table : t_mara_3000 lines l_v_3000_lines,
                         t_mara_3010 lines l_v_3010_lines,
                         t_mara_3100 lines l_v_3010_lines,
if  l_v_3000_lines <> 0 and  l_v_3010_lines <> 0 and l_v_3010_lines <> 0.
loop at t_mara_3000 assigning <l_fs_mara_3000>.
read t_mara_3010 assigning <L_fs_mara_3010> where matnr = <l_fs_mara_3000>-matnr.
if sy-subrc <> 0.
continue.
endif.
read t_mara_3100 assigning <L_fs_mara_3100> where matnr = <l_fs_mara_3000>-matnr.
if sy-subrc <> 0.
continue.
endif.
append : <l_fs_mara_3000> to t_mara,
              <l_fs_mara_3010> to t_mara,
              <l_fs_mara_3100> to t_mara.
unassign : <l_fs_mara_3000>, <l_fs_mara_3010>, <l_fs_mara_3100>.
endif.

Re: Problem with fetching data from Marc

Former Member — Tue, 03 Apr 2012 13:27:59 GMT

didn't read Rob's excellent and correct reply?

Re: Problem with fetching data from Marc

RaymondGiuseppi — Tue, 03 Apr 2012 13:48:12 GMT

Perhaps there is no F1 key on his keyboard ? Then he could read WHERE - subquery at help.sap.com or sample at Open SQL - Usage Example

SELECT matnr FROM mara AS m INTO TABLE t_mara UP TO 100 ROWS
  WHERE EXISTS ( SELECT * FROM marc WHERE matnr = m~matnr AND werks EQ '3000')
    AND EXISTS ( SELECT * FROM marc WHERE matnr = m~matnr AND werks EQ '3010')
    AND EXISTS ( SELECT * FROM marc WHERE matnr = m~matnr AND werks EQ '3100').

Regards,
Raymond

Re: Problem with fetching data from Marc

Private_Member_49934 — Wed, 04 Apr 2012 07:57:30 GMT

Sorry for the reply.

But as for your question - Obviously not. SCN doesn't give updated status while you are typing the post and I don't have time to type the post copy and do a referesh again before adding reply.

Re: Problem with fetching data from Marc

kesavadas_thekkillath — Wed, 04 Apr 2012 08:15:39 GMT

Hi Raymond,

Do we need a subquery for this Can't the data be selected by appending the plant's to a range table and using IN operator.

SELECT DISTINCT matnr FROM marc INTO CORRESPONDING FIELDS OF TABLE it_marc WHERE werks IN s_werks.

Re: Problem with fetching data from Marc

RaymondGiuseppi — Wed, 04 Apr 2012 08:34:40 GMT

The problem of the IN range operator is the implicit OR between every record of the range. The only question to be now answered is performance : comparing

- subqueries with 3 exists where subqueries

- joining twice the file to itself

- extraction to (sorted) internal table and identifying triples.

Another SELECT dynamic for any number of plants

DATA: marc TYPE marc,
      nb_werks TYPE i,
      t_mara TYPE TABLE OF matnr.
SELECT-OPTIONS so_werks FOR marc-werks.
SELECT COUNT(*) FROM t001w INTO nb_werks
  WHERE werks IN so_werks. " number of plants
SELECT matnr FROM marc INTO TABLE t_mara
  WHERE werks IN so_werks
  GROUP BY matnr
  HAVING COUNT( * ) = nb_werks. " list of materials defined  in every plant

Regards,

Raymond

Re: Problem with fetching data from Marc

kesavadas_thekkillath — Wed, 04 Apr 2012 08:59:53 GMT

Sub query seems to be faster than all other. I just checked it.

Re: Problem with fetching data from Marc

Former Member — Wed, 04 Apr 2012 09:05:14 GMT

Hi All

Thank you all for your valuble suggestions. I has resolved my problem with the help of all you.

Thanks

Chaitanya