topic Re: Inner Join - whats wrong? in Application Development and Automation Discussions

Inner Join - whats wrong?

Former Member — Mon, 20 Jun 2011 08:15:51 GMT

Hello there,

I was trying to make an Inner Join, but unfortunately without success.

there is the code, can someone pls tell me what is wrong??

&----

DATA: gs_mara TYPE mara,
      gt_mara TYPE STANDARD TABLE OF mara.
DATA: gs_marc TYPE marc,
      gt_marc TYPE STANDARD TABLE OF marc.
DATA: gs_makt TYPE makt,
      gt_makt TYPE STANDARD TABLE OF makt.
DATA: gs_t001w TYPE t001w,
      gt_t001w TYPE STANDARD TABLE OF t001w.


TYPES: BEGIN OF ts_ausgabe,
       matnr TYPE matnr,
       mtart TYPE mtart,
       matkl TYPE matkl,
       meins TYPE meins,
       werks TYPE werks_d,
       dispo TYPE dispo,
       adrnr TYPE adrnr,
       END OF ts_ausgabe.

DATA: gs_ausgabe TYPE ts_ausgabe.
DATA: gt_ausgabe TYPE STANDARD TABLE OF ts_ausgabe.



SELECT-OPTIONS: pr_matnr FOR gs_mara-matnr,
                pr_werks FOR gs_marc-werks.

START-OF-SELECTION.


  select a~matnr b~werks c~dispo d~adrnr e~mtart f~matkl g~meins
  INTO CORRESPONDING FIELDS OF TABLE gt_ausgabe
  FROM        mara AS a  INNER  JOIN marc AS b 
  ON b~matnr = a~matnr
  INNER  JOIN marc AS c  ON c~matnr = a~matnr
  INNER JOIN t001w AS d  ON d~werks = a~matnr  
  INNER  JOIN mara AS e  ON e~matnr = a~matnr
  INNER  JOIN mara AS f  ON f~matnr = a~matnr
  INNER  JOIN mara AS g  ON g~matnr = a~matnr


  where a~matnr = pr_matnr
    and b~werks = pr_werks
    and c~dispo = pr_dispo
    and d~adrnr = pr_adrnr
    and e~mtart = pr_mtart
    and f~matkl = pr_matkl
    and g~meins = pr_meins.
  
  write: / matnr dispo adrnr mtart matkl meins.


    LOOP AT gt_mara INTO gs_mara.
      READ TABLE gt_marc INTO gs_marc WITH KEY matnr = gs_mara-matnr.

      IF sy-subrc = 0.
        MOVE: gs_mara-matnr TO gs_ausgabe-matnr,
              gs_mara-mtart TO gs_ausgabe-mtart,
              gs_mara-matkl TO gs_ausgabe-matkl,
              gs_mara-meins TO gs_ausgabe-meins,
              gs_marc-werks TO gs_ausgabe-werks,
              gs_marc-dispo TO gs_ausgabe-dispo,
              gs_t001w-adrnr TO gs_ausgabe-adrnr.

        WRITE: / gs_mara-matnr, gs_mara-mtart, gs_mara-matkl, gs_mara-meins, gs_marc-werks, gs_marc-dispo, gs_t001w-adrnr.
        APPEND gs_ausgabe TO gt_ausgabe.
      ENDIF.
    ENDLOOP.

    LOOP AT gt_ausgabe INTO gs_ausgabe.

      WRITE: / sy-vline, gs_ausgabe-matnr,
               sy-vline, gs_ausgabe-mtart,
               sy-vline, gs_ausgabe-matkl,
               sy-vline, gs_ausgabe-meins,
               sy-vline, gs_ausgabe-werks,
               sy-vline, gs_ausgabe-dispo,
               sy-vline, gs_ausgabe-adrnr.
      ULINE.
    ENDLOOP.

Edited by: Thomas Zloch on Jun 20, 2011 10:19 AM - code tags added

Re: Inner Join - whats wrong?

ThomasZloch — Mon, 20 Jun 2011 08:20:24 GMT

Please define "without success", syntax error, short dump, too much data, missing data?

Thomas

Re: Inner Join - whats wrong?

Former Member — Mon, 20 Jun 2011 08:24:02 GMT

Hi Kfir,


   select a~matnr b~werks c~dispo d~adrnr e~mtart f~matkl g~meins
   INTO CORRESPONDING FIELDS OF TABLE gt_ausgabe
   FROM        mara AS a  INNER  JOIN marc AS b 
   ON b~matnr = a~matnr
   INNER  JOIN marc AS c  ON c~matnr = a~matnr
   INNER JOIN t001w AS d  ON d~werks = a~matnr  
   INNER  JOIN mara AS e  ON e~matnr = a~matnr
   INNER  JOIN mara AS f  ON f~matnr = a~matnr
   INNER  JOIN mara AS g  ON g~matnr = a~matnr

joining t001_{werks on mara}matnr will not work...

Regards

REA

Re: Inner Join - whats wrong?

Former Member — Mon, 20 Jun 2011 08:26:41 GMT

well first thank you for the quick respond.

Im quite sure that I have a problem with the structure of the Inner Join, but I dont find the problem...

Re: Inner Join - whats wrong?

ThomasZloch — Mon, 20 Jun 2011 08:28:05 GMT

I think REA nailed it down already

Plus, why are you joining MARA four times and MARC twice? You seem to be joining once per field required, not necessary, once per table is fine.

Thomas

http://help.sap.com/abapdocu_702/en/abapselect_join.htm

Edited by: Thomas Zloch on Jun 20, 2011 10:32 AM

Re: Inner Join - whats wrong?

deepak_dhamat — Mon, 20 Jun 2011 08:31:13 GMT

hi ,

use this query

select a~matnr b~werks b~dispo adrnr mtart matkl meins
  INTO CORRESPONDING FIELDS OF TABLE gt_ausgabe
  FROM        mara AS a  INNER  JOIN marc AS b
  ON b~matnr = a~matnr
*  INNER  JOIN marc AS c  ON c~matnr = a~matnr
  INNER JOIN t001w AS d  ON d~werks = b~werks  
  where a~matnr = pr_matnr
    and b~werks = pr_werks
    and c~dispo = pr_dispo
    and adrnr = pr_adrnr
    and mtart = pr_mtart
    and matkl = pr_matkl
    and meins = pr_meins.

Why you are using mara table and marc table multiple times ?

regards

Deepak.

Edited by: Deepak Dhamat on Jun 20, 2011 10:31 AM

Re: Inner Join - whats wrong?

Former Member — Mon, 20 Jun 2011 08:32:30 GMT

Mara is 4 times in JOIn as "a", "e", "f" and "g".

One time would be enough! Use only MARA as "a".

Regards

Klaius

Re: Inner Join - whats wrong?

Former Member — Mon, 20 Jun 2011 08:37:42 GMT

well, I will try it in other way...

I have these values, which i have to unify in one table:

from mara:

matnr, mtart, matkl, meins

from marc:

werks, dispo, matnr

from makt:

matnr

from t001w:

werks, adrnr

my way is to build a new table, and with inner join to fill the new table.

is the way that I wrote is right?

thank you all for your time!

Re: Inner Join - whats wrong?

deepak_dhamat — Mon, 20 Jun 2011 08:40:59 GMT

hi ,

There is no need to build new table , just use above query where

you will get all data in your internal table

then use that table for further processing .

regards

Deepak.

Re: Inner Join - whats wrong?

Former Member — Mon, 20 Jun 2011 08:54:09 GMT

Hi,

check below statement...

select a_{matnr b}werks b_{dispo c}adrnr a_{mtart a}matkl a~meins

INTO CORRESPONDING FIELDS OF TABLE gt_ausgabe

FROM mara AS a

INNER JOIN marc AS b

ON b_{matnr = a}matnr

INNER JOIN t001w AS c ON

c_{werks = b}werks

where a~matnr in pr_matnr and

b~werks in pr_werks

Ram.

Re: Inner Join - whats wrong?

Former Member — Mon, 20 Jun 2011 09:11:49 GMT

ohh, I think that I get it:

when I write ´´ a_{matnr ´´ I dont really mean to the value ``matnr`` that exist in mara, but this ´´a}´´ refer to the whole DB mara/marc...

thank you !

Re: Inner Join - whats wrong?

Former Member — Mon, 20 Jun 2011 09:18:24 GMT

Hi,

if you have

mara as a

a~matnr refers to mara-matnr, not to marc-matnr!

Regards,

Klaus

Edited by: Klaus Babl on Jun 20, 2011 11:18 AM

Re: Inner Join - whats wrong?

Former Member — Mon, 20 Jun 2011 09:30:38 GMT

ok thank you. now I understand it better !