Problem with fetching data from Marc

[Former Member]

Hi

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

[erik_mertens3]

Hi Krishna,

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

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

[Former Member]

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

[erik_mertens3]

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.

[Former Member]

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

Rob

[Former Member]

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

[Private_Member_49934]

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.

[Former Member]

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

[RaymondGiuseppi]

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

[Private_Member_49934]

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.

[kesavadas_thekkillath]

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.

[RaymondGiuseppi]

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

[kesavadas_thekkillath]

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

[Former Member]

Hi All

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

Thanks

Chaitanya