JOIN

[Former Member]

HI ALL,

i need get materials from mara and even from ekpo table,

for materials for which PO has created for that i need get from ekpo table. even for which po is not created also i need to get .

i have written left outer join in select query.

could you please suggest anything i have writen wrong because i am not getting the correct data.

code is

SELECT A~EBELN

A~EBELP

A~MATNR

A~WERKS

A~AEDAT

A~MEINS

A~UMREZ

A~UMREN

A~NETPR

A~PEINH

B~MATKL

B~MEINS

INTO corresponding fields of TABLE GT_EKPO

FROM ( EKPO AS A

LEFT OUTER JOIN MARA AS B

ON B_{MATNR EQ A}MATNR

AND B_{MATKL EQ A}MATKL )

WHERE A~MATNR IN S_MATNR

AND A~MATKL IN S_MATKL

AND A~BUKRS IN S_BUKRS

AND A~WERKS IN S_WERKS

AND A~AEDAT LE P_EDATE.

please help

[former_member386202]

Hi,

Use for all entries instead of join becoz it will take time to execute.

refer below code

&----

---

*& Form sub_read_ekpo

&----

---

• text

----

---

FORM sub_read_ekpo .

IF NOT it_ekko[] IS INITIAL.

*--Select Purchansing document purchasing document item and material

• from table EKPO into internal table it_ekpo

SELECT ebeln "Purchasing Document

ebelp "Purchasing Document Item

matnr "Material

FROM ekpo

INTO TABLE it_ekpo

FOR ALL ENTRIES IN it_ekko

WHERE ebeln EQ it_ekko-ebeln

AND matnr IN s_matnr.

IF sy-subrc EQ 0.

*--Sort table by purchasing document anf purchasing document item

SORT it_ekpo BY ebeln ebelp.

ENDIF.

ENDIF.

ENDFORM. " sub_read_ekpo

&----

---

*& Form sub_read_eket

&----

---

• text

----

---

FORM sub_read_eket .

IF NOT it_ekpo[] IS INITIAL.

*--Select Purchasing Document Number Item Number of Purchasing Document

• and Delivery Date from table EKET

SELECT ebeln "Purchasing Document Number

ebelp "Item Number of Purchasing Document

eindt "Delivery Date

FROM eket

INTO TABLE it_eket

FOR ALL ENTRIES IN it_ekpo

WHERE ebeln EQ it_ekpo-ebeln

AND ebelp EQ it_ekpo-ebelp.

IF sy-subrc EQ 0.

*--Sort table by purchasing document anf purchasing document item

SORT it_eket BY ebeln ebelp.

ENDIF.

ENDIF.

ENDFORM. " sub_read_eket

Regards,

Prashant

[Former Member]

Hi,

Try to fetch the details of EKPO after MARA based on SY-SUBRC, so that you may not have the confusion on fetching the records based on MATNR. Even Performance increases.

[Former Member]

Hello

When you use a left outer join in the FROM clause of a SELECT command, it makes a crucial difference whether the logical condition is in the ON or the WHERE clause. Since not all of the database systems supported by SAP themselves support the standard syntax and semantics of the left outer join, the syntax has been restricted to those cases that return the same solution in all database systems:

Only a table or view may come after the JOIN statement, not another join expression

The only logical operator allowed in the ON condition is AND

Each comparison in the ON comdition must contain a field from the right-hand table

Comparisons in the WHERE condition may not contain fields from the right-hand table

You can only use EQ (or 😃 as comparisons in the ON condition

The ON condition must contain at least one "real" JOIN condition (a condition containing a field from both tabref1 and tabref2)

So, try to use INNER JOIN instead of LEFT JOIN.

[venkatasriram_mygapula]

Hi,

SELECT A~EBELN

A~EBELP

A~MATNR

A~WERKS

A~AEDAT

A~MEINS

A~UMREZ

A~UMREN

A~NETPR

A~PEINH

B~MATKL

B~MEINS

INTO corresponding fields of TABLE GT_EKPO

FROM ( EKPO AS A

LEFT OUTER JOIN MARA AS B

ON B_{MATNR EQ A}MATNR

AND B_{MATKL EQ A}MATKL )

WHERE A~MATNR IN S_MATNR

AND A~MATKL IN S_MATKL

AND A~BUKRS IN S_BUKRS

AND A~WERKS IN S_WERKS

AND A~AEDAT LE P_EDATE.

try this code. declare two int.tables for mara(i_mara) and ekpo(i_ekpo).

first select

select matnr matkl mains into table i_mara

from mara

where matkl in s_matkl

and bukrs in s_bukrs

and werks in s_werks

and aedat le p_edat.

loop at i_mara.

select ebelp matnr werks aedat ........... into table i_ekpo

from ekpo

where matnr = i_mara-matnr.

read table i_ekpo with key matnr = i_mara-matnr.

i_mara-ebelp = i_ekpo-ebelp.

i_mara-werks = i_ekpo-werks.

i_mara-aedat = i_ekpo-aedat.

-

---

-

---

modify i_mara transporting ebelp werks aedat ...........

endloop.

finally u can write fields from i_mara.

regards.

sriram.

[Former Member]

Hiii!

Try out this example.

REPORT  z_sdn.
TABLES:
  mara,
  ekpo.

SELECT-OPTIONS:
  s_matnr FOR mara-matnr,
  s_matkl FOR mara-matkl,
  s_bukrs FOR ekpo-bukrs,
  s_werks FOR ekpo-werks.
PARAMETERS:
  p_edate TYPE mara-ersda.
DATA:
  BEGIN OF fs_mat,
    ebeln TYPE ekpo-ebeln,
    ebelp TYPE ekpo-ebelp,
    matnr TYPE ekpo-matnr,
    werks TYPE ekpo-werks,
    aedat TYPE ekpo-aedat,
    umrez TYPE ekpo-umrez,
    umren TYPE ekpo-umren,
    netpr TYPE ekpo-netpr,
    peinh TYPE ekpo-peinh,
    matkl TYPE mara-matkl,
    meins TYPE mara-meins,
  END OF fs_mat.


DATA:
  t_mat LIKE
  TABLE OF
        fs_mat.


START-OF-SELECTION.

  PERFORM get_data.
*&---------------------------------------------------------------------*
*&      Form  get_data
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM get_data .
  SELECT a~ebeln
         a~ebelp
         a~matnr
         a~werks
         a~aedat
         a~umrez
         a~umren
         a~netpr
         a~peinh
         b~matkl
         b~meins
    INTO CORRESPONDING FIELDS OF TABLE t_mat
    FROM ( ekpo AS a
           LEFT OUTER JOIN mara AS b
           ON b~matnr EQ a~matnr
           AND b~matkl EQ a~matkl )
   WHERE a~matnr IN s_matnr
     AND a~matkl IN s_matkl
     AND a~bukrs IN s_bukrs
     AND a~werks IN s_werks
     AND a~aedat LE p_edate.

ENDFORM.                    " get_data

Reward points if useful

Regards

Abhijeet Kulshreshtha

[Former Member]

Hi,

please correct yout query

SELECT A~EBELN

A~EBELP

A~MATNR

A~WERKS

A~AEDAT

A~MEINS

A~UMREZ

A~UMREN

A~NETPR

A~PEINH

B~MATKL

B~MEINS

up to 10 rows

dont write into corresponding table here

FROM EKPO AS A

LEFT OUTER JOIN MARA AS B

ON B_{MATNR EQ A}MATNR

AND B_{MATKL EQ A}MATKL

INTO corresponding fields of TABLE GT_EKPO.

thanks,

suman

Edited by: suman kumar chinnam on Jul 8, 2008 9:37 AM

[Former Member]

Hi srilatha,

Dont use left outer join use inner join instead since your requirement is to get the materials for which the purchase order is created.

Thanks,

Naveen kumar.