topic Re: INNER JOINT in Application Development and Automation Discussions

INNER JOINT

Former Member — Sun, 25 Nov 2007 07:07:15 GMT

HI Frds...

actually we should not write joint select qurrei for ( like & in )...but i did here

i got requriment if there is no input in selection also they want to see out put ...

at that when they exicute total data will come into internal table ... so in buffer

area capacity of this table is occurs 0.

so exectly i need to know if we keep occurs 100 . to this internal table IT_SEL.

the memory will increase or not ....

SELECT

A~MBLNR

A~BUDAT

A~XBLNR

B~BWART

B~MATNR

B~WERKS

B~LIFNR

B~MENGE

B~SGTXT

B~UMWRK

INTO CORRESPONDING FIELDS OF TABLE IT_SEL

FROM MKPF AS A INNER JOIN MSEG AS B

ON A_{MBLNR = B}MBLNR

AND A_{MJAHR = B}MJAHR

WHERE A~MBLNR IN S_MBLNR

AND A~BUDAT IN S_BUDAT

AND B~WERKS IN S_WERKS.

Re: INNER JOINT

naimesh_patel — Sun, 25 Nov 2007 07:50:39 GMT

Yes System will allocate memory as your data grows. Initially it will allocate the memory for 100 records for the syntax OCCURS 100 and then for next 100 ... and so on.

Regards,

Naimesh Patel

Re: INNER JOINT

Former Member — Mon, 26 Nov 2007 18:42:16 GMT

Try using the following code

TABLES: mkpf,
        mseg.

SELECT-OPTIONS: s_mblnr FOR mkpf-mblnr,
                s_budat FOR mkpf-budat,
                s_werks FOR mseg-werks.


TYPES: BEGIN OF ty_mkpf,
         mblnr TYPE mkpf-mblnr,
         mjahr TYPE mkpf-mjahr,
         budat TYPE mkpf-budat,
         xblnr TYPE mkpf-xblnr,
       END OF ty_mkpf,

       BEGIN OF ty_mseg,
         mblnr TYPE mseg-mblnr,
         mjahr TYPE mseg-mjahr,
         zeile TYPE mseg-zeile,
         bwart TYPE mseg-bwart,
         matnr TYPE mseg-matnr,
         werks TYPE mseg-werks,
         lifnr TYPE mseg-lifnr,
         menge TYPE mseg-menge,
         sgtxt TYPE mseg-sgtxt,
         umwrk TYPE mseg-umwrk,
       END OF ty_mseg,

       BEGIN OF ty_sel,
         mblnr TYPE mkpf-mblnr,
         budat TYPE mkpf-budat,
         xblnr TYPE mkpf-xblnr,
         bwart TYPE mseg-bwart,
         matnr TYPE mseg-matnr,
         werks TYPE mseg-werks,
         lifnr TYPE mseg-lifnr,
         menge TYPE mseg-menge,
         sgtxt TYPE mseg-sgtxt,
         umwrk TYPE mseg-umwrk,
       END OF ty_sel.

DATA: w_mkpf TYPE                 ty_mkpf,
      w_mseg TYPE                 ty_mseg,
      w_sel  TYPE                 ty_sel ,
      it_mkpf type hashed table of ty_mkpf
        with unique key mblnr mjahr,
      it_mseg type        table of ty_mseg,
      it_sel type         table of ty_sel .


SELECT mblnr
 mjahr
 budat
 xblnr
FROM mkpf
INTO TABLE it_mkpf
WHERE mblnr IN s_mblnr
AND   budat IN s_budat.

IF sy-subrc EQ 0.

  SELECT mblnr
         mjahr
         zeile
         bwart
         matnr
         werks
         lifnr
         menge
         sgtxt
         umwrk
    FROM mseg
    INTO TABLE it_mseg
    FOR ALL ENTRIES IN it_mkpf
    WHERE mblnr EQ it_mkpf-mblnr
    AND   mjahr EQ it_mkpf-mjahr
    AND   werks IN s_werks.

  IF sy-subrc EQ 0.

    SORT it_mseg BY mblnr mjahr zeile.

  ENDIF.

ENDIF.


REFRESH it_sel.

LOOP AT it_mseg INTO w_mseg.

  AT NEW mjahr.
    READ TABLE it_mkpf INTO w_mkpf WITH KEY mblnr = w_mseg-mblnr
                                           mjahr = w_mseg-mjahr
                                           TRANSPORTING
                                             budat
                                             xblnr.
  ENDAT.

  w_sel-mblnr = w_mseg-mblnr.
  w_sel-budat = w_mkpf-budat.
  w_sel-xblnr = w_mkpf-xblnr.
  w_sel-bwart = w_mseg-bwart.
  w_sel-matnr = w_mseg-matnr.
  w_sel-werks = w_mseg-werks.
  w_sel-lifnr = w_mseg-lifnr.
  w_sel-menge = w_mseg-menge.
  w_sel-sgtxt = w_mseg-sgtxt.
  w_sel-umwrk = w_mseg-umwrk.
  APPEND w_sel TO it_sel.
  CLEAR  w_sel.

ENDLOOP.