Qry in JOINS

[Former Member]

Hi All,

Sorry if its very simple basic question .

Code:

tables:mara,makt.

data:t_mara type mara occurs 0.

select-options:s_mat for mara-matnr,

s_des for makt-maktx.

select a_{matnr a}mtart

into corresponding fields of table t_mara

from mara as a inner join makt as b

on a_{matnr eq b}matnr

where a_{matnr in s_mat and b}maktx in s_des.

Question :If i want to select all fields from mara table whats the easiest way ? why select * is not possible here .

Regards,

Jeeva

[Former Member]

Look at this. Remember, MAKTX is case sensitive, whereas MAKTG is uppercase version of the same description.

DATA: v_matnr LIKE mara-matnr,
      v_maktx LIKE makt-maktx.

DATA: t_mara TYPE mara OCCURS 0.

DATA: BEGIN OF t_makt OCCURS 0,
        matnr LIKE makt-matnr.
DATA: END OF t_makt.

SELECT-OPTIONS: s_matnr FOR v_matnr,
                s_maktx FOR v_maktx.


START-OF-SELECTION.

  SELECT matnr INTO TABLE t_makt
               FROM makt
              WHERE matnr IN s_matnr
                AND spras EQ sy-langu
                AND maktx IN s_maktx.

  SELECT * FROM mara
           INTO TABLE t_mara FOR ALL ENTRIES IN t_makt
          WHERE matnr = t_makt-matnr.

[Former Member]

Hi Jeeva,

If you want to work with the mara/makt tables joined together, it is often simplest to create a view.

In the view you can specify the join condition and choose which fields (all if you like) from each table you want to work with.

With a view, you can use select * also, so your select would look something like:

SELECT *
  FROM ZV_MARA_MAKT
 WHERE matnr in s_mat
   AND maktx in s_des.

The other benefit is that your code is a lot easier to read!!!

Hope that helps.

Brad

[Former Member]

Also,

Don't forget the language when selecting from MAKT!

You should include SPRAS as one of the fields in your view, and to avoid hard coding in the select, do something like:

SELECT *
  FROM ZV_MARA_MAKT
 WHERE matnr in s_mat
   AND maktx in s_des
   AND SPRAS eq sy-langu.  "using the users logon language"

OR

SELECT *
  FROM ZV_MARA_MAKT
 WHERE matnr in s_mat
   AND maktx in s_des
   AND SPRAS in s_spras.  "using a select option on the report"

Cheers,

Brad

[Former Member]

Create a Database view on MARA and MAKT

[andreas_mann3]

Hi Jeeva,

i think, to select an existing view of mara and makt

is the best solution

(search view with repository info system-

primary table = mara , base table = makt- you'll find 38 views!).

here's a poss. solution for your select-statement:

DATA:t_mara TYPE mara OCCURS 0.
SELECT-OPTIONS:s_mat FOR mara-matnr,
s_des FOR makt-maktx.
DATA: BEGIN OF fld OCCURS 0,
        fieldname TYPE dd03l-fieldname,
      END OF fld.
DATA: tabinfo TYPE  x031l OCCURS 0 WITH HEADER LINE.
*
CALL FUNCTION 'RFC_GET_NAMETAB'
     EXPORTING
          tabname          = 'MARA'
     TABLES
          nametab          = tabinfo
     EXCEPTIONS
          table_not_active = 1
          OTHERS           = 2.
*
LOOP AT tabinfo.
  CONCATENATE 'A~' tabinfo-fieldname INTO fld-fieldname.
  APPEND fld.
ENDLOOP.
*
SELECT (fld)
INTO CORRESPONDING FIELDS OF TABLE t_mara
FROM mara AS a INNER JOIN makt AS b
ON a~matnr EQ b~matnr
WHERE a~matnr IN s_mat AND b~maktx IN s_des.

regards Andreas

[Former Member]

Try this.....

<b>DATA: BEGIN OF t_mat OCCURS 0.

INCLUDE STRUCTURE mara.

DATA: spras TYPE makt-spras,

maktx TYPE makt-maktx,

maktg TYPE makt-maktg.

DATA: END OF t_mat.

SELECT *

UP TO 100 ROWS

FROM makt

JOIN mara ON mara_{matnr EQ makt}matnr

INTO corresponding fields of t_mat

WHERE spras EQ sy-langu.

APPEND t_mat.

ENDSELECT.

LOOP AT t_mat.

WRITE: / t_mat-matnr, t_mat-meins, t_mat-maktx, t_mat-maktg.

ENDLOOP.</b>

Nothing fancy.

The <b>INTO CORRESPONDING FIELDS OF <u>TABLE</u> itab</b> does not work, but <b>INTO CORRESPONDING FIELDS OF wa</b> works

Rishi

[Former Member]

Hi Jeeva

Try this! I Hope it is Simple & easy to Understand.

DATA: BEGIN OF ITAB OCCURS 0,

MATNR LIKE MARA-MATNR,

MTART LIKE MARA-MTART,

MAKTX LIKE MAKT-MAKTX,

END OF ITAB.

SELECT-OPTIONS: S_MAT FOR MARA-MATNR,

S_DES FOR MAKT-MAKTX.

SELECT MARA_{MATNR MARA}MTART MAKT~MAKTX

INTO CORRESPONDING FIELDS OF TABLE ITAB

FROM MARA

INNER JOIN MAKT

ON MARA_{MATNR EQ MAKT}MATNR

WHERE MATNR IN S_MAT

AND MAKTX IN S_DES

AND SPRAS EQ 'EN'.

Regards

Praveen