cross joins on queries

[Former Member]

HI,

how we can apply cross joins on select query.plz help me .I am using tables ekko,ekpo and lfa1 .Plz help.

[Former Member]

Hello, here is an example of a JOIN over ekko, ekpo, and lfa1.

I hope it is useful, and if it's what your looking for.

Depending on the data you need to read, you may have to modify it.

Cheers, Edwin.

-

---

REPORT zbc_temp.

* Table declarations
TABLES: ekko, "Purchasing Document Header
        ekpo, "Purchasing Document Item
        lfa1. "Vendor Master (General Section)

* Declaration of types:
TYPES: BEGIN OF ty_podata,
         ebeln TYPE ebeln,
         ebelp TYPE ebelp,
         lifnr TYPE lifnr,
       END OF ty_podata.

* Declaration of internal tables and workareas:
DATA: ta_podata TYPE TABLE OF ty_podata WITH HEADER LINE.
DATA: wa_podata TYPE ty_podata.

*Selection screen:
PARAMETERS: p_lifnr TYPE lifnr.

* Selection (JOIN):
START-OF-SELECTION.

  SELECT lfa1~lifnr ekko~ebeln ekpo~ebelp
         FROM lfa1 INNER JOIN ekko
                   ON lfa1~lifnr = ekko~lifnr
         INNER JOIN ekpo ON ekko~ebeln = ekpo~ebeln
         UP TO 100 ROWS
         INTO CORRESPONDING FIELDS OF TABLE ta_podata
         WHERE lfa1~lifnr = '0000100160'.

END-OF-SELECTION.

* Main program logic:
  LOOP AT ta_podata INTO wa_podata.
    WRITE: / wa_podata-ebeln,
             wa_podata-ebelp,
             wa_podata-lifnr.
  ENDLOOP.

[Former Member]

Hi Amardeep,

as code given by one of guru's in the above joins statement is good when only if use the KEY-FIELDS in where condition if not it causes performance issue , so that you can use the FOR ALL ENTRIES IN statement which it gives good perfomance.

• Table declarations

TABLES: ekko, "Purchasing Document Header

ekpo, "Purchasing Document Item

lfa1. "Vendor Master (General Section)

• Declaration of types:

data: BEGIN OF tbl_ekko,

ebeln TYPE ebeln,

lifnr TYPE lifnr,

END OF tbl_ekko.

data: BEGIN OF tbl_ekpo,

ebeln TYPE ebeln,

ebelp TYPE ebelp,

END OF tbl_ekpo.

data: BEGIN OF tbl_lfa1,

lifnr TYPE lifnr

ebelp TYPE ebelp,

END OF tbl_lfa1.

*Selection screen:

PARAMETERS: p_lifnr TYPE lifnr,

p_ebeln type ebeln.

• Selection (JOIN):

START-OF-SELECTION.

SELECT lifnr

FROM lfa1

INTO table tbl_lfa1

WHERE lifnr EQ p_lifnr.

if sy-subrc = 0.

sort tbl_lfa1 by lifnr.

endif.

if not tbl_lifnr is initial.

SELECT ebeln lifnr

FROM ekko

INTO table tbl_ekko

FOR ALL INTRIES IN tbl_lfa1

WHERE ebeln EQ p_ebeln

AND lifnr EQ tbl_lfa1-lifnr.

if sy-subrc = 0.

sort tbl_ ekko by ebeln.

endif.

endif.

if not tbl_ekko is initial.

SELECT ebeln ebelp

FROM ekpo

INTO table tbl_ekpo

FOR ALL INTRIES IN tbl_ekko

WHERE ebeln EQ tbl_ekko-eblen.

if sy-subrc = 0.

sort tbl_ ekpo by ebeln.

endif.

endif.

END-OF-SELECTION.

• Main program logic:

LOOP AT tbl_ekpo.

read table ekko with key ebeln = tbl_ekpo-ebeln BINARY SEARCH.

if sy-subrc = 0

WRITE: / tbl_ekko-ebeln,

tbl_ekko-ebelp,

tbl_ekpo-lifnr.

endif.

ENDLOOP.

<b>NOTE : use only KEY-FIELDS in WHERE condition for better perfomance</b>

In that above code you can change a/c to ur inputs availabe.

<b>Reward with points if helpful.</b>

Regards,

Vijay

[Former Member]

got it !!! thankx..