topic Re: inner join in Application Development and Automation Discussions

inner join

Former Member — Tue, 01 Apr 2008 06:59:07 GMT

hello all,

how can i join three tables through inner join.

give an example

thanks.

Re: inner join

Former Member — Tue, 01 Apr 2008 07:03:05 GMT

Hi,



* Types
TYPES: BEGIN OF ty_target,
       ekko_ebeln  TYPE ekko-ebeln,
       ekko_bukrs  TYPE ekko-bukrs,
       ekko_bstyp  TYPE ekko-bstyp,
       bsart       TYPE ekko-bsart,
       ekpo_ebeln  TYPE ekpo-ebeln,
       ekpo_ebelp  TYPE ekpo-ebelp,
       ekpo_aedat  TYPE ekpo-aedat,
       txz01       TYPE ekpo-txz01,
       etenr       TYPE eket-etenr,
       eindt       TYPE eket-eindt,
       eket_menge  TYPE eket-menge,
       ameng       TYPE eket-ameng,
       END OF ty_target.

* Internal tables and work areas
DATA:
      it_data        TYPE TABLE OF ty_target,
      wa_data        LIKE LINE OF it_data.

SELECTION-SCREEN: BEGIN OF BLOCK b01 WITH FRAME TITLE title01.
PARAMETERS:     p_bukrs                               TYPE bukrs.
SELECT-OPTIONS: so_ebeln                              FOR ekko-ebeln.
SELECTION-SCREEN: END OF BLOCK b01.


  SELECT a~ebeln a~bukrs a~bstyp a~bsart b~ebeln b~ebelp b~aedat b~txz01 c~etenr
   c~eindt c~menge c~ameng
    FROM ( ( ekko AS a
             INNER JOIN ekpo AS b ON a~ebeln = b~ebeln AND
                                     a~lponr = b~ebelp )
             INNER JOIN eket AS c ON b~ebeln = c~ebeln AND
                                     b~ebelp = c~ebelp )
    INTO TABLE it_data   " <--  Attention ! Field sequence !
    WHERE a~ebeln IN so_ebeln AND
          a~bukrs EQ p_bukrs .

Regards

Sandipan

Edited by: Sandipan Ghosh on Apr 1, 2008 12:33 PM

Re: inner join

Former Member — Tue, 01 Apr 2008 07:09:00 GMT

If the tables are like header and item as well as inter related like VBAK VBAP VBPA etc

then it would be more faster than for all entries method

check this code

SELECT stpo_{stlnr stpo}idnrk mast_{matnr mara}mtart stpo~menge

INTO CORRESPONDING FIELDS OF TABLE zmat1 FROM mast

JOIN stpo ON stpo_{stlnr = mast}stlnr

JOIN mara ON mara_{matnr = mast}matnr

WHERE stpo_{stlty = 'M' "AND stpo}idnrk IN s_matnr

AND mast~werks = 1000.

Here s_matnr is a select-options on the selection-screen.

Or this.

Code:

Select single Vbrk_{Bukrs Vbrk}Kunrg Vbrk~Vbeln

Vbrk_{Fkdat Vbrk}Bstnk_Vf Vbrk~Zterm

Tvzbt~Vtext

Vbak_{Vbeln Vbak}Bstdk

Likp_{Vbeln Likp}lfdat Likp~Lfuhr

into w_vbrk

from vbrk

inner join Tvzbt on Tvzbt_{Zterm = Vbrk}Zterm and

Tvzbt~Spras = sy-langu

Inner join Vbfa as SalesLnk

on SalesLnk~vbeln = pu_vbeln and

SalesLnk~vbtyp_v = c_order

inner join Vbak on Vbak_{Vbeln = SalesLnk}Vbelv

Inner join Vbfa as DeliveryLnk

on DeliveryLnk~vbeln = pu_vbeln and

DeliveryLnk~vbtyp_v = c_Delivery

inner join Likp on Likp_{Vbeln = DeliveryLnk}Vbelv

where vbrk~vbeln = pu_Vbeln.

regards

Shiva

Re: inner join

Former Member — Tue, 01 Apr 2008 07:14:09 GMT

Hi,

Check the following select statement:

select zfpcd_{cadivi zfpcd}proforma zfpcd_{factura zfpcd}aniofactura

zfpcd_{montousd zfpcd}montoap zfpcd_{ebeln zfpcd}inco1

zfpcd_{lifnr lfa1}name1 zcdvs_{status zfpcd}conint

into it_lista

from zfpcd inner join zcdvs

on zfpcd_{ebeln = zcdvs}ebeln

and zfpcd_{proforma = zcdvs}proforma

and zfpcd_{lifnr = zcdvs}lifnr

inner join lfa1

on zfpcd_{lifnr = lfa1}lifnr

where zcdvs~status = '04'.

Regards,

Bhaskar

Re: inner join

ak_upadhyay — Tue, 01 Apr 2008 07:28:30 GMT

Hi,

Check this....


REPORT  YPRO_4 LINE-SIZE 150.                                 .

TABLES: VBAP, LIPS, VBRP.

SELECT-OPTIONS: A_VBELN FOR VBAP-VBELN.


DATA: BEGIN OF IT OCCURS 0,

      VBELN LIKE VBAP-VBELN,
      POSNR LIKE VBAP-POSNR,
      KWMENG LIKE VBAP-KWMENG,
      L_VBELN LIKE LIPS-VBELN,
      L_POSNR LIKE LIPS-POSNR,
      LFIMG LIKE LIPS-LFIMG,
      R_VBELN LIKE VBRP-VBELN,
      R_POSNR LIKE VBRP-POSNR,
      FKIMG LIKE VBRP-FKIMG,

      END OF IT.


      SELECT    VBAP~VBELN VBAP~POSNR VBAP~KWMENG
                LIPS~VBELN LIPS~POSNR LIPS~LFIMG
                VBRP~VBELN VBRP~POSNR VBRP~FKIMG
                INTO TABLE IT FROM LIPS INNER JOIN VBAP
                ON VBAP~VBELN = LIPS~VGBEL AND
                   VBAP~POSNR = LIPS~VGPOS
                   INNER JOIN VBRP ON
                 LIPS~VBELN = VBRP~VGBEL AND
                 LIPS~POSNR = VBRP~VGPOS
                 WHERE VBAP~VBELN
                IN A_VBELN.


      LOOP AT IT.

      FORMAT INVERSE ON.

      FORMAT COLOR 6.

      WRITE:/ SY-VLINE, IT-VBELN,
              SY-VLINE, IT-POSNR,
              SY-VLINE, IT-KWMENG,
              SY-VLINE, IT-L_VBELN,
              SY-VLINE, IT-L_POSNR,
              SY-VLINE, IT-LFIMG,
              SY-VLINE, IT-R_VBELN,
              SY-VLINE, IT-R_POSNR,
              SY-VLINE, IT-FKIMG,
              SY-VLINE.

      WRITE:/ SY-ULINE(129).

      FORMAT INVERSE OFF.

      FORMAT RESET.

      ENDLOOP.

Reward points if useful....

Regards