Skip to Content
author's profile photo
Former Member

Inner join

hi to all,

i have to display data from 3 tables according to select-option, im using inner join for the same.Is my going in right way. Is thr any other method to do the same. I have used inner join but not able to display the data. Please check my code and pt out my mistakes.

Thanks & Regards

Amit

my code :

REPORT zpmr_sample_prog.

TABLES : ekko,

ekpo,

eket.

SELECTION-SCREEN: BEGIN OF BLOCK b1.

SELECT-OPTIONS: s_p_doc FOR ekko-ebeln ,

s_c_code FOR ekko-bukrs ,

s_m_no FOR ekpo-matnr,

s_d_type FOR ekko-bsart.

SELECTION-SCREEN: END OF BLOCK b1.

DATA: BEGIN OF wa,

pur_doc TYPE ekko-ebeln,

pur_item TYPE ekpo-ebelp,

doctype TYPE ekko-bsart,

mat_no TYPE ekpo-matnr,

sch_qty TYPE eket-menge,

plant TYPE ekpo-werks,

co_code TYPE ekko-bukrs,

END OF wa.

DATA: itab LIKE STANDARD TABLE OF wa WITH HEADER LINE.

SELECT aebeln bebelp absart bmatnr cmenge bwerks INTO CORRESPONDING FIELDS OF TABLE itab

FROM ekko AS a

INNER JOIN ekpo AS b ON aebeln = bebeln INNER JOIN eket AS c ON aebeln = cebeln WHERE a~ebeln IN s_p_doc AND

abukrs IN s_c_code AND absart IN s_d_type AND b~matnr = s_m_no.

LOOP AT itab.

WRITE : itab-pur_doc, / itab-pur_item, itab-doctype, itab-mat_no, itab-sch_qty, itab-plant.

ENDLOOP.

Add comment
10|10000 characters needed characters exceeded

6 Answers

  • Best Answer
    author's profile photo
    Former Member
    Posted on Aug 23, 2007 at 08:03 AM

    Hi,

    In your code, so many fields in where conditions used are no-key fields are bukrs,bsart,matnr, etc...........

    you can use the following code to avoid joins by using the for all entres concept.

    TABLES : ekko,

    ekpo,

    eket.

    DATA: BEGIN OF itab OCCURS 0,

    pur_doc TYPE ekko-ebeln,

    pur_item TYPE ekpo-ebelp,

    doctype TYPE ekko-bsart,

    mat_no TYPE ekpo-matnr,

    sch_qty TYPE eket-menge,

    plant TYPE ekpo-werks,

    co_code TYPE ekko-bukrs,

    END OF itab.

    SELECT-OPTIONS : s_p_doc FOR ekko-ebeln,

    s_c_code FOR ekko-bukrs,

    s_d_type FOR ekko-bsart,

    s_m_no FOR ekpo-matnr.

    DATA : BEGIN OF tbl_ekko OCCURS 0,

    pur_doc TYPE ekko-ebeln,

    co_code TYPE ekko-bukrs,

    doctype TYPE ekko-bsart,

    END OF tbl_ekko.

    DATA : BEGIN OF tbl_ekpo OCCURS 0,

    pur_doc TYPE ekko-ebeln,

    pur_item TYPE ekpo-ebelp,

    mat_no TYPE ekpo-matnr,

    plant TYPE ekpo-werks,

    END OF tbl_ekpo.

    DATA : BEGIN OF tbl_eket OCCURS 0,

    pur_doc TYPE ekko-ebeln,

    pur_item TYPE ekpo-ebelp,

    sch_qty TYPE eket-menge,

    END OF tbl_eket.

    SELECT ebeln bukrs bsart

    INTO TABLE tbl_ekko

    FROM ekko

    WHERE ebeln IN s_p_doc AND

    bukrs IN s_c_code AND

    bsart IN s_d_type.

    IF sy-subrc = 0.

    SORT tbl_ekko BY pur_doc.

    ENDIF.

    IF NOT tbl_ekko[] IS INITIAL.

    SELECT ebeln ebelp matnr werks

    INTO TABLE tbl_ekpo

    FROM ekpo

    FOR ALL ENTRIES IN tbl_ekko

    WHERE ebeln EQ tbl_ekko-pur_doc AND

    matnr IN s_m_no.

    IF sy-subrc = 0.

    SORT tbl_ekpo BY pur_doc pur_item.

    ENDIF.

    ENDIF.

    IF NOT tbl_ekpo[] IS INITIAL.

    SELECT ebeln ebelp menge

    INTO TABLE tbl_eket

    FROM ekpo

    FOR ALL ENTRIES IN tbl_ekpo

    WHERE ebeln EQ tbl_ekpo-pur_doc AND

    ebelp EQ tbl_ekpo-pur_item.

    IF sy-subrc = 0.

    SORT tbl_eket BY pur_doc pur_item.

    ENDIF.

    ENDIF.

    LOOP AT tbl_ekko.

    itab-pur_doc = tbl_ekko-pur_doc.

    itab-doctype = tbl_ekko-doctype.

    itab-co_code = tbl_ekko-co_code.

    CLEAR tbl_ekpo.

    READ TABLE tbl_ekpo WITH KEY pur_doc = tbl_ekko-pur_doc

    BINARY SEARCH.

    IF sy-subrc = 0.

    itab-pur_item = tbl_ekpo-pur_item.

    itab-mat_no = tbl_ekpo-mat_no .

    itab-plant = tbl_ekpo-plant.

    CLEAR tbl_eket.

    READ TABLE tbl_eket WITH KEY pur_doc = tbl_ekpo-pur_doc

    pur_item = tbl_ekpo-pur_item

    BINARY SEARCH.

    IF sy-subrc = 0.

    itab-sch_qty = tbl_eket-sch_qty .

    ENDIF.

    ENDIF.

    APPEND itab.

    CLEAR : tbl_ekko, itab.

    ENDLOOP.

    LOOP AT itab.

    WRITE : itab-pur_doc, / itab-pur_item, itab-doctype, itab-mat_no, itab-sch_qty, itab-plant.

    ENDLOOP.

    *******************************************

    Let me know if you have doubts.

    Points if useful.

    Regards,

    Vijay.

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Posted on Aug 23, 2007 at 07:00 AM

    Hi amit solanki ,

    I think all 3 table what u have mention is in relationship, If yes directly u can create the <b>DATABASE VIEW</b> with required fields and use that view in ur program to retrive the data.

    For example ur view name is ZDB_VIEW.

    select * or fields from ZZDB_VIEW.

    if sy-subrc = 0.

    Business logic

    else.

    Business logic

    endif.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Amit,

      As far as i know there will not be any performance issue here. We can use the <b>DATABASE VIEW</b> to retrive the data from more then one table (If table are related wiht the <b>FK</b>.

      Here the one issue is u have to create the VIEW it means it is new object while transporting (To Quality or Production) u have to transport one object extra that is ur VIEW.

      Reward If helpfull.

      Anees Jawad.

      9886358645

      Message was edited by:

      anees jawad

  • Posted on Aug 23, 2007 at 06:27 AM

    Stop using INNER JOIN, is always a performance problem after.

    use : select .. into table ...

    check not it_tab1[] is initial.

    select .. into table .. from .. for all entries in it_tab1 ..

    check not it_tab2[] is initial.

    ...

    use inner join only between header and item for example : VBAK VBAP, VBRK VBRP ...

    Fred

    Add comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 23, 2007 at 07:03 AM

    Hi,

    Don't use inner join its performance wise really bad so better to use

    for all entries.

    first select data from ekko then check if its initial or not then select the data from

    ekpo using for all entries and then from eket.

    then lastly

    loop at it_ekko.

    read ekpo and read eket

    and append it into on final internal table and then display it.

    endloop

    regards,

    prashant

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Posted on Aug 23, 2007 at 07:22 AM

    Hi,

    U can use inner join upto two table. If u use morethan two tables in inner join performance of the program will be degraded.

    In any case don't use INTO CORRESPONDING FIELDS OF, because it will effect the performance instead u can define the table according to the selecting fields and using directly INTO TABLE.

    REGARDS,

    SANKAR

    Add comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 23, 2007 at 08:20 AM

    Hi,

    Your join has two problems:

    1. in your definition of internal table where the data should be saved you have changed the name of the fields (instead of EBELN -> PUR_DOC); thats why you should change also the name of the fields in the select statement. This was the main reason not to get any data

    2. in your join definition you should join EKPO with EKET by using EBELN and EBELP fields (this is the primary key for EKET table ...)

    Your join should look like this:

    SELECT 
    a~ebeln as pur_doc
    b~ebelp as pur_item
    a~bsart as doctype
    b~matnr as mat_no
    c~menge as sch_qty
    b~werks as plant
    INTO CORRESPONDING FIELDS OF TABLE itab
    FROM ekko AS a INNER JOIN ekpo AS b ON a~ebeln = b~ebeln 
    	       INNER JOIN eket AS c ON b~ebeln = c~ebeln and
                                           b~ebelp = c~ebelp
    WHERE a~ebeln IN s_p_doc AND
          a~bukrs IN s_c_code AND 
          a~bsart IN s_d_type AND 
          b~matnr = s_m_no.

    Related to the other comments and answers posted to your question:

    - joining table is not a bad performance solution at all. I have programs with joins between 10 or 15 tables (ALL by PRIMARY KEY !!!) and they are performing really fast. This is because the select is done by the database from the back directly and this database is optimized for this kind of tasks.

    - if you are using FOR ALL ENTRIES ... you should take a lot of care to that the internal table content referred should not have to many lines, other wise your performance can go down.

    BR,

    Valentin

    Add comment
    10|10000 characters needed characters exceeded