topic Re: Join in Application Development and Automation Discussions

Join

Former Member — Mon, 29 Oct 2007 16:11:05 GMT

How to join EKKO, EKPO, EKKN, EKBE based on EBELN(PO number) & EBELP (PO line item number).

e.g I want PO number from ekko and po line item from ekpo and some other fileds from these tables...

thanks,

Re: Join

Former Member — Mon, 29 Oct 2007 16:36:47 GMT

Hi,

1. Select * from EKKO into table it_ekko.

2. Select * from ekpo into table it_ekpo for all entries in it_ekko where ebeln = it_ekko.

3. Select * from ekkn into table it_ekkn for all entries in it_ekpo where ebeln = it_ekpo-ebeln and ebelp = it_ekpo-ebelp.

4. Select * from ekbe into table it_ekbe for all entries in it_ekpo where ebeln = it_ekpo-ebeln and ebelp = it_ekpo-ebelp.

<b>Reward points for helpful answers</b>

Satish

Re: Join

sreekanthgo — Mon, 29 Oct 2007 16:45:18 GMT

Hi,

You can go for Different selects on all these tables using FOR ALL ENTRIES..

Below is an example code.. you can write select stmnts in that way..



DATA: BEGIN OF it_ekko OCCURS 0,
        ebeln TYPE ekko-ebeln,
      END OF it_ekko.

DATA: BEGIN OF it_ekpo OCCURS 0,
        ebeln TYPE ekpo-ebeln,
        ebelp TYPE ekpo-ebelp,
      END OF it_ekpo.

DATA: BEGIN OF it_ekkn OCCURS 0,
        ebeln TYPE ekkn-ebeln,
        ebelp TYPE ekkn-ebelp,
        zekkn TYPE ekkn-zekkn,
      END OF it_ekkn.

DATA: BEGIN OF it_ekbe OCCURS 0,
        ebeln TYPE ekbe-ebeln,
        ebelp TYPE ekbe-ebelp,
        zekkn TYPE ekbe-zekkn,
        vgabe TYPE ekbe-vgabe,
        gjahr TYPE ekbe-gjahr,
        belnr TYPE ekbe-belnr,
        buzei TYPE ekbe-buzei,
      END OF it_ekbe.


PARAMETERS: p_ebeln TYPE ekko-ebeln.

SELECT ebeln
  FROM ekko
  INTO TABLE it_ekko
 WHERE ebeln = p_ebeln. " This parameter can be on selection screen

IF NOT it_ekko[] IS INITIAL.

  SELECT ebeln
         ebelp
    FROM ekpo
    INTO TABLE it_ekpo
     FOR ALL ENTRIES IN it_ekko
   WHERE ebeln = it_ekko-ebeln.

  IF NOT it_ekpo IS INITIAL.

    SELECT ebeln
           ebelp
           zekkn
      FROM ekkn
      INTO TABLE it_ekkn
       FOR ALL ENTRIES IN it_ekpo
     WHERE ebeln = it_ekpo-ebeln
       AND ebelp = it_ekpo-ebelp.
  ENDIF.
ENDIF.

<i>Do not forget to reward if it helps you. ;)</i>

Thanks,

Sreekanth

Re: Join

Former Member — Mon, 29 Oct 2007 16:46:03 GMT


SELECT ekko~ebeln ekpo~ebelp INTO CORRESPONDING FIELDS OF 
TABLE itab FROM EKKO  INNER JOIN EKPO ON 
ekko~ebeln = ekpo~ebeln   "Continue same with other tables as well
WHERE ekko~ebeln IN s_ebeln.

Re: Join

Former Member — Mon, 29 Oct 2007 17:19:21 GMT

select m_{ebeln m}procstat m_{rlwrt m}frgke m~bukrs

c_{ebelp c}txz01 c_{knttp c}netwr

c1_{nplnr c1}kostl c1_{anln1 c1}sakto c1_{prctr c1}aufnr c1_{projn c1}imkey c1_{PS_PSP_PNR c1}ZEKKN

c2_{belnr c2}budat c2_{wrbtr c2}vgabe c2~gjahr

from ekko as m inner join ekpo as c on

c_{ebeln = m}ebeln

inner join ekkn as c1 on

c1_{ebeln = m}ebeln and

c1_{ebelp = c}ebelp

inner join ekbe as c2 on

c2_{ebeln = m}ebeln and

c2_{ebelp = c}ebelp

into corresponding fields of table it_outtab

where ebeln in s_ebeln.

This querey is displaying only first line item, whats wrong in it????

Re: Join

Former Member — Mon, 29 Oct 2007 17:27:02 GMT

Hi Khan following code is working fine in my system....


select m~ebeln m~procstat m~rlwrt m~frgke m~bukrs
c~ebelp c~txz01 c~knttp c~netwr
c1~nplnr c1~kostl c1~anln1 c1~sakto c1~prctr c1~aufnr c1~projn c1~imkey c1~PS_PSP_PNR c1~ZEKKN
c2~belnr c2~budat c2~wrbtr c2~vgabe c2~gjahr INTO CORRESPONDING FIELDS OF
TABLE it_outtab  from ekko as m inner join ekpo as c on
c~ebeln = m~ebeln

inner join ekkn as c1 on
c1~ebeln = m~ebeln and
c1~ebelp = c~ebelp

inner join ekbe as c2 on
c2~ebeln = m~ebeln and
c2~ebelp = c~ebelp
where ebeln in s_ebeln.

Message was edited by:

Perez C

Message was edited by:

Perez C

Re: Join

Former Member — Mon, 29 Oct 2007 17:55:47 GMT

Hi,

If we have multiplie line items in EKPO table but EKBE only has one line item, then based on this query, should it display only one line item with this join.

Thanks

Re: Join

Former Member — Mon, 29 Oct 2007 19:34:42 GMT

e.g i have 10 line items for ekpo table, but in ekbe table, we have only one line item. then based on above mentioned JOIN condition, how many columns will be displayed. In my current situation, it'sonly displaying one column. I need to dispaly all 10 line items.

The order of my join tables are as follows.

EKKO, EKPO, EKKN, EKBE and the join cond is:

select m_{ebeln m}procstat m_{rlwrt m}frgke m~bukrs

c_{ebelp c}txz01 c_{knttp c}netwr

c1_{nplnr c1}kostl c1_{anln1 c1}sakto c1_{prctr c1}aufnr c1_{projn c1}imkey c1_{PS_PSP_PNR c1}ZEKKN

c2_{belnr c2}budat c2_{wrbtr c2}vgabe c2~gjahr

from ekko as m inner join ekpo as c on

c_{ebeln = m}ebeln

inner join ekkn as c1 on

c1_{ebeln = m}ebeln and

c1_{ebelp = c}ebelp

inner join ekbe as c2 on

c2_{ebeln = m}ebeln and

c2_{ebelp = c}ebelp

into corresponding fields of table it_outtab

where ebeln in s_ebeln.

Pls help....

Re: Join

Former Member — Mon, 29 Oct 2007 19:45:24 GMT

Check the below link :

http://www.erpgenie.com/abap/code/abap47.htm

The above link will close to your requirement.

Do not use join more than 3 tables ( if you use more than 3 tables then you get bad performance ).

Thanks

Seshu