topic Re: JOIN in Application Development and Automation Discussions

JOIN

Former Member — Tue, 08 Jul 2008 07:18:00 GMT

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

Re: JOIN

former_member386202 — Tue, 08 Jul 2008 07:20:58 GMT

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.

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.

ENDFORM. " sub_read_eket

Regards,

Prashant

Re: JOIN

Former Member — Tue, 08 Jul 2008 07:21:56 GMT

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.

Re: JOIN

Former Member — Tue, 08 Jul 2008 07:30:36 GMT

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.

Re: JOIN

venkatasriram_mygapula — Tue, 08 Jul 2008 07:32:42 GMT

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.

Re: JOIN

Former Member — Tue, 08 Jul 2008 07:32:55 GMT

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

Re: JOIN

Former Member — Tue, 08 Jul 2008 07:37:13 GMT

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

Re: JOIN

Former Member — Tue, 08 Jul 2008 07:55:07 GMT

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.