topic Re: Join problems in Application Development and Automation Discussions

Join problems

Former Member — Thu, 14 Jun 2007 09:51:18 GMT

Hi all,

I am trying to extract some PO item data using a join statement and I only get one line in w_extract, the data from the first line in EKET and the corresponding data from MAW1 and EKPO. Can someone see what I am doing wrong?

I have checked EKET and I have nine items corresponding to the EINDT-selection. I do not fill s_wladg in the selection.

I have also checked EKPO and found the same nine items as in EKET and checked MAW1 for the materials and all have a WLADG.

select a~eindt
       a~menge
       a~ebeln
       b~matnr
       b~meins
       c~wladg
  into (w_extract-eindt,
       w_extract-menge,
       w_extract-ebeln,
       w_extract-matnr,
       w_extract-meins,
       w_extract-wladg)
  from eket as a join ekpo as b
  on a~ebeln eq b~ebeln
  and a~ebelp eq b~ebelp
  join maw1 as c
  on b~matnr eq c~matnr
  where c~wladg in s_wladg
    and a~eindt in s_eindt.

best regards,

Glenn Karlsson

Re: Join problems

Former Member — Thu, 14 Jun 2007 09:57:56 GMT

Select statement look s fine

But the sequence of data flow is first into EKPO then EKET

so change little bit like

select b~ebeln

a~eindt

a~menge

b~matnr

b~meins

c~wladg

into (w_extract-ebeln,

w_extract-eindt,

w_extract-menge,

w_extract-matnr,

w_extract-meins,

w_extract-wladg)

from ekpo as b join eket as a

on a_{ebeln eq b}ebeln

and a_{ebelp eq b}ebelp

join maw1 as c

on b_{matnr eq c}matnr

where a~eindt in s_eindt and

c~wladg in s_wladg.

see now

Reward points for useful Answers

Regards

Anji

Re: Join problems

Former Member — Thu, 14 Jun 2007 09:58:10 GMT

i am not sure whether you have pasted full select or not because endselect is not there ... and if you are not using select single as per your query then you will get an error.

data : itab like w_extract occurs 0 with header line.

select a~eindt

a~menge

a~ebeln

b~matnr

b~meins

c~wladg

into (w_extract-eindt,

w_extract-menge,

w_extract-ebeln,

w_extract-matnr,

w_extract-meins,

w_extract-wladg)

from eket as a join ekpo as b

on a_{ebeln eq b}ebeln

and a_{ebelp eq b}ebelp

join maw1 as c

on b_{matnr eq c}matnr

where c~wladg in s_wladg

and a~eindt in s_eindt.

append w_extract to itab.

endselect.

loop at itab. " put break point and check

endloop.

regards

shiba dutta

Re: Join problems

Former Member — Thu, 14 Jun 2007 10:04:49 GMT

Hi,

The problem probably lies in the work area declaration.

I think that you are selecting the values in a work area which can at most have just one record.

If you want a number of records then try to select the values into an internal table directly using the INTO TABLE command.

It is best to select all the values at once rather than selecting into a work arae or using SELECT ...ENDSELECT commands for performance issues.

Thanks,

Sandeep.

Re: Join problems

Former Member — Thu, 14 Jun 2007 10:04:51 GMT

Hi Glen,

How did u define w_extract-eindt, w_extract-menge, w_extract-ebeln,

w_extract-matnr, w_extract-meins, w_extract-wladg?

If u define them as variables, then u will get only the first line .

If u need all the records , then u can define an internal table with the fields .

DATA : BEGIN OF it_eket OCCURS 0,

eindt like eket-eindt,

menge like eket-menge,

ebeln like eket-ebeln,

matnr like ekpo-matnr,

meins like ekpo-meins,

wladg like maw1-wladg,

END OF it_eket.

then u can write a select statement as follows :

select a~eindt

a~menge

a~ebeln

b~matnr

b~meins

c~wladg

into table it_eket

from eket as a join ekpo as b

on a_{ebeln eq b}ebeln

and a_{ebelp eq b}ebelp

join maw1 as c

on b_{matnr eq c}matnr

where c~wladg in s_wladg

and a~eindt in s_eindt.

This will get u all the records into internal table it_eket .

regards,

Sree

PS: Rewards points if Useful.

Re: Join problems

Former Member — Thu, 14 Jun 2007 10:28:44 GMT

Thanks all. Combining a couple of the answers I fixed the problem.