Join fields from ekko on marc?

[Former Member]

Hi there,

I have the following select:

select a~matnr

t~maktx

c~werks

c~dispo

c~dismm

c~zinds

c~mmsta

c~minbe

c~bstmi

c~plifz

c~zsuper

d~labst

c~webaz

into corresponding fields of table part_data

from makt as t

inner join mara as a on t_{matnr = a}matnr

inner join marc as c on a_{matnr = c}matnr

inner join mard as d on c_{matnr = d}matnr

where a~matnr in s_matnr

and c~werks in s_werks

and c~dispo in s_dispo

and c~mmsta in s_mmsta

and c~zinds in s_zinds

and c~dismm in s_dismm.

if sy-subrc ne 0.

• appropriate error handling

endif.

How can I add something like this to it?

select ebeln

from ekko into corresponding fields of table part_data

where bsart = 'ZD11'.

ENDSELECT.

Both selects work but they overwrite each other!

[Former Member]

Hi there,

I have the following select:

select a~matnr

t~maktx

c~werks

c~dispo

c~dismm

c~zinds

c~mmsta

c~minbe

c~bstmi

c~plifz

c~zsuper

d~labst

c~webaz

into corresponding fields of table part_data

from makt as t

inner join mara as a on t_{matnr = a}matnr

inner join marc as c on a_{matnr = c}matnr

inner join mard as d on c_{matnr = d}matnr

where a~matnr in s_matnr

and c~werks in s_werks

and c~dispo in s_dispo

and c~mmsta in s_mmsta

and c~zinds in s_zinds

and c~dismm in s_dismm.

if sy-subrc ne 0.

• appropriate error handling

endif.

How can I add something like this to it?

select ebeln

from ekko into corresponding fields of table part_data

where bsart = 'ZD11'.

ENDSELECT.

Both selects work but they overwrite each other!

[Former Member]

Look at the BOLD part ...

select ebeln

from ekko

appending corresponding fields of table part_data

where bsart = 'ZD11'.

ENDSELECT.

[Former Member]

This still does not pull out the ebelns for each material. Is there a way of integrating this select with my original select?

[Former Member]

Hi,

u have to pass the matnr in the select to get all the ebelns.

so u can try this:

select all the ebelns from ekko in to itab and modify these entries after u fetch the data from ekpo passing the matnr.

select ebeln from ekpo

into table i_ekpo

for all entries in itab

where ebeln = itab-ebeln

and matnr = s_matnr.

regards,

madhumitha

[Former Member]

There is no field MATNR in table ekko ..

instead get the data from EKPO ...

select ebeln

from ekpo

into corresponding fields of table part_data1

for all entries in part_data

where matnr = part_data-matnr.

[Former Member]

Hi,

instead of using

select ebeln

from ekko into corresponding fields of table part_data

where bsart = 'ZD11'.

ENDSELECT.

use like below.

loop at part_data.
  select single ebeln from ekko into part_data-ebeln where bsart = 'ZD11'.
  modify part_data.
endloop.

rgds,

bharat.

[Former Member]

hi ,

check below.

select ebeln

from ekko

appending corresponding fields of table part_data

where bsart = 'ZD11'.

endselect.

Regards,

Kranthi.

[Former Member]

I still can't seem to get all the fields to output.

I tried

select ebeln

from ekpo

into corresponding fields of table part_data1

for all entries in part_data

where matnr = part_data-matnr.

move part_data1 to part_data.

This brings up all the ebeln data but makes matnr, mmsta and werks disappear.

[Former Member]

hi,

this is to get only the relevant ebelns.

move only corresponding data into the final internal table.

consolidate the data taken from ekpo n ekko into one internal table and then move the corresponding data into final internal table.

ie:

loop at i_ekko

read table i_ekpo with key ebeln = i_ekko-ebeln.

<populate ebeln, matnr...into it_final>

endloop.

then move corresponding fields to part_data.

regards,

madhumitha

[Former Member]

How do I define I_EKPO?

[christine_evans]

I think you should rethink what you are actually trying to do with these selects.

The initial select is getting a list of material numbers from MARC etc.

Your second select is getting a list of order numbers from EKKO for order type BSART.

There is no direct relationship between your MARC data and your EKKO data and yet you are trying to add them to the same internal table.

What are you trying to do?

If you are trying to get a list of order lines containing the material numbers picked up by your first select, then it is possible but you would need to include the EKPO order lines table (as already suggested) which is the one that actually holds the material number for the order. But a single material number could be used on multiple order lines, and so you will never get the one to one relationship that you appear to be wanting.

Very, very confusing.

[Former Member]

I am trying to output some details of materials and ebeln would allow me to see the agreement numbers for each material.

[christine_evans]

I've just seen that you've got it working using joins - and if I had a choice I'd always use joins rather than the clunky SAP LOOP-AND-READ method I've described below..... But this is the other way of doing it....

For each material number in your first internal table you want to get a list of order numbers where at least one order line contains that material number, and then you want to get a distinct list of all agreement numbers (EKKO-KONNR I guess) related to each of these orders.

You're not likely to get a one to one relationship. So, you'll have to reorganise your data a bit. The basic, though rather long winded, way of doing this would be the following.

a) Keep your MARC select as it is, filling itab1

b) Get all EKPO records for the selected MATNRs itab2 using FOR ALL ENTRIES on itab1 - EKPO-MATNR is indexed, at least in our system, so this should be quick.

c) Get all EKKO records the the selected orders into itab3 using FOR ALL ENTRIES on itab2.

d) Fill your output table itab4 by combining the data from these 3 tables.

- Loop at itab1 and fill the itab4 work area with the fields you want.

- For each itab1 MATNR, get the associated records from itab2 using a LOOP or the more efficient READ with a BINARY SEARCH followed by a LOOP. NB it is very likely that you will find more than one record here and you need to deal with all of them.

- For each itab2 EBELN, get the associated order header record from itab3 using a READ with BINARY SEARCH. Copy the fields you want to keep eg EBELN and KONNR to your itab4 work area.

- Append your itab4 workarea to your itab4 table.

Lots of ways you could improve this. I'd probably use a join between EKPO and EKKO to reduce the number of selects and the number of itabs and remove any duplicate MATNR / EBELN / KONNR records before doing the final loop.

Edited by: Christine Evans on Apr 29, 2008 2:32 PM

[Former Member]

Got it working with my original select

select a~matnr

t~maktx

c~werks

c~dispo

c~dismm

c~zinds

c~mmsta

c~minbe

c~bstmi

c~plifz

c~zsuper

d~labst

c~webaz

e~bsart

k~ebeln

*o~ebeln

*o~BUKRS

into corresponding fields of table part_data

FROM EKKO as e

inner join ekpo as k on e_{ebeln = k}ebeln

inner join mara as a on k_{matnr = a}matnr

inner join marc as c on a_{matnr = c}matnr

inner join mard as d on c_{matnr = d}matnr

inner join makt as t on d_{matnr = t}matnr

where a~matnr in s_matnr

and c~werks in s_werks

and c~dispo in s_dispo

and c~mmsta in s_mmsta

and c~zinds in s_zinds

and c~dismm in s_dismm

and e~bsart = 'ZD11'.

Thanks for everyone's help

[ThomasZloch]

not bad...how long does this run on a big database?

you should add SPRAS to the JOIN on MAKT, WERKS to the JOIN on MARC and WERKS and LGORT to the JOIN on MARD, or your select might return too many unwanted records.

Greetings

Thomas

[Former Member]

Thank you for that, that has reduced the number of records immensely which is great.

Will I ever be able to return just one unique record for each material or will I be limited by having to find an agreement as each material can have more than one agreement number?