Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Not getting the output using for all entries-

Former Member
0 Kudos

Pls rely me....the values are not fetching for this select query...............


SELECT MAtnr

       MTART

       MBRSH

       MATKL

       MEINS

       GEWEI

       SPART

   from mara

   INTO TABLE it_mara

   where matnr in s_matnr.

IF  sy-subrc = 0.

   SELECT MATNR

            BWKEY

            VPRSV

            STPRS

        FROM mbew

        INTO TABLE it_mbew

        FOR ALL ENTRIES IN it_mara

        where matnr = it_mara-matnr.

     endif.

   IF sy-subrc = 0.

     SELECT MATNR

             SPRAS

       FROM makt

       INTO TABLE it_final

       FOR ALL ENTRIES IN it_mara

       WHERE matnr = it_mara-matnr.

   ENDIF.

   IF sy-subrc = 0.

        SELECT matnr

               werks

               ekgrp

          FROM marc

          INTO TABLE it_marc

          FOR ALL ENTRIES IN it_mara

          where matnr = it_mara-matnr.

   ENDif.

LOOP AT it_mara INTO wa_mara.

     READ TABLE it_mara INTO wa_mara WITH KEY matnr = wa_mara-matnr.

   wa_final-matnr = wa_mara-matnr.

   wa_final-Mtart = wa_mara-mtart.

   wa_final-mbrsh = wa_mara-mbrsh.

   wa_final-matkl = wa_mara-matkl.

   wa_final-meins = wa_mara-gewei.

   wa_final-spart = wa_mara-spart.

     READ TABLE it_mbew INTO wa_mbew with key matnr = wa_mara-matnr.

   IF sy-subrc = 0.

     wa_final-stprs = wa_mbew-stprs.

     wa_final-vprsv = wa_mbew-vprsv.

     wa_final-bwkey = wa_mbew-bwkey.

   ENDIF.

   READ TABLE it_marc INTO wa_marc WITH key matnr = wa_mara-matnr.

   IF sy-subrc = 0.

     wa_final-werks = wa_marc-werks.

     wa_final-ekgrp = wa_marc-ekgrp.

   ENDIF.

   READ TABLE it_makt INTO wa_makt WITH KEY matnr = wa_mara-matnr.

   IF sy-subrc = 0.

     wa_final-spras = wa_makt-spras.

   ENDIF.

   APPEND wa_final to it_final.

CLEAR wa_final.

ENDLOOP.

24 REPLIES 24

matt
Active Contributor
0 Kudos

Try this post: http://scn.sap.com/polls/1987 and switch to an INNER JOIN - in most cases it will be more efficient. If that still doesn't work, feel free to come back for more help.

gaurab_banerji
Active Participant
0 Kudos

when the first select works then sy-subrc is 0.

when the 2nd select fails then sy-subrc = 4 and thus all further checks fail because sy-subrc = 4.

So avoid using IF sy-subrc = 0. instead use IF lt_mara[] IS NOT INITIAL.

2nd... check manually in se16 if there are related entries at all before expecting some data.

hope this helps

Former Member
0 Kudos

These are normal select's only. you didn't nested them properly.

You are selecting MBEW and MARC based on MARA, check with SE16 also.

It is not necessary to maintain( extend ) Material in any plant - if maintained in plant then MARC will have data.

And all materials no need to be in MBEW. Check these in SE16 and debugging.

for a single material there can be multiple records in MARC and MBEW.

In this case you need to loop it_marc and it_mbew, read statement always gives first record data only.

SELECT MAtnr

       MTART

       MBRSH

       MATKL

       MEINS

       GEWEI

       SPART

   from mara

   INTO TABLE it_mara

   where matnr in s_matnr.

IF  sy-subrc = 0.

     SELECT MATNR

             SPRAS

       FROM makt

       INTO TABLE it_final

       FOR ALL ENTRIES IN it_mara

       WHERE matnr = it_mara-matnr.

   SELECT MATNR

            BWKEY

            VPRSV

            STPRS

        FROM mbew

        INTO TABLE it_mbew

        FOR ALL ENTRIES IN it_mara

        where matnr = it_mara-matnr.

        SELECT matnr

               werks

               ekgrp

          FROM marc

          INTO TABLE it_marc

          FOR ALL ENTRIES IN it_mara

          where matnr = it_mara-matnr.

endif.

0 Kudos

Thank u got the ans

matt
Active Contributor
0 Kudos

Oh good. I hope you switched to INNER JOIN and followed the advice of the experts who know what best practice is.

Why not share your solution, so that others can benefit from it?

0 Kudos

This message was moderated.

0 Kudos

first i tried with the inner join only but same process only........but wen i tired with the example of

Mordhwaj Gajpal  i got the ans..........wen i debug and saw the values are fetched properly

matt
Active Contributor
0 Kudos

That's all very well, but there's more to programming than just getting the right answer. There's using the best forms of programming.

Because you're using FAE, your program is more complicated than it need be. That means it will be harder and slower to maintain in the future, and that means it will be more expensive. If I were your development lead, I would insist you use INNER JOIN and that you get your program working with that.

If you take the time to get an INNER JOIN version working, you will be a better programmer.

0 Kudos

Hello Mat,

can you pls give sample example using inner join.....bcs mostly in our team we are using FAE..................................need to b a better programmer as u said

0 Kudos

a custom example from my report :
SELECT zvdms_rfd_log~vinnr zvdms_rfd_log~zlifnr zvdms_rfd_log~engid
     zvdms_rfd_log~mdescr zvdms_rfd_log~dloct zvdms_rfd_log~trlno zvdms_rfd_log~modcd
     zvdms_rfd_log~zkunnr
     zvdms_rfd_log~zone1 zvdms_rfd_log~dname zvdms_rfd_log~dspdt
     zvdms_rfd_log~loadno zvdms_nadin~invno zvdms_nadin~invdt zvdms_nadin~comno
     FROM zvdms_rfd_log INNER JOIN zvdms_nadin ON zvdms_nadin~vinnr = zvdms_rfd_log~vinnr
     INTO CORRESPONDING FIELDS OF TABLE itab WHERE  zvdms_rfd_log~dspdt IN date .

0 Kudos

JOINS Concept :

Getting the data from Two or More tables into PL using the select statement is called as Joins concept.

Requirements:

  1. Structures / internal tables
  2. Alias naming in Select statement.
  3. Types of Joins ( Inner Join / Outer Join )
  4. On Clause.

Data : begin of wa ,

matnr like mara-matnr,

mbrsh like mara-mbrsh,

mtart like mara-mtart,

meins like mara-meins,

maktx like makt-maktx,

end of wa.

select m~matnr m~mbrsh m~mtart m~meins k~maktx into wa from mara as m inner join makt as k on m~matnr = k~matnr.

write : / wa-matnr , wa-mbrsh, wa-mtart , wa-meins , wa-maktx.

endselect.

inner join : gets the data from both tables where ever similer records are matching , non matching records are ignored.

outer join : Is called in abap as "left outer join" or "Left Join" , used to fetch data from both tables where ever similer records are found and even the records existing in first table which are not matching with second table also will be returned.

Syntax:

select <fields_list> into <target_ara> from <tab1> [as <als_name>] <joined> <tab2> [as <als_name>] on <criteria>.

abap statements.

endselect.

example code.

REPORT ZINNER_JOINS.

Data : begin of wa ,

matnr like mara-matnr,

mbrsh like mara-mbrsh,

mtart like mara-mtart,

meins like mara-meins,

maktx like makt-maktx,

end of wa.

select m~matnr m~mbrsh m~mtart m~meins k~maktx into wa from mara as m inner join makt as k on m~matnr = k~matnr.

write : / wa-matnr , wa-mbrsh, wa-mtart , wa-meins , wa-maktx.

  1. endselect.

.

Former Member
0 Kudos

HI,

   Try this one..

   SELECT MATNR
       MTART
       MBRSH
       MATKL
       MEINS
       GEWEI
       SPART
   FROM MARA
   INTO TABLE IT_MARA

   WHERE MATNR IN S_MATNR.
SORT IT_MARA BY MATNR.             "Added Sort it_mara by matnr

IF NOT IT_MARA[] IS INITIAL.
  SELECT MATNR
         BWKEY
         VPRSV
         STPRS
       FROM MBEW
       INTO TABLE IT_MBEW
       FOR ALL ENTRIES IN IT_MARA
       WHERE MATNR = IT_MARA-MATNR.
  SELECT MATNR
         SPRAS
    FROM MAKT
    INTO TABLE IT_MAKT
    FOR ALL ENTRIES IN IT_MARA
    WHERE MATNR = IT_MARA-MATNR.
  SELECT MATNR
         WERKS
         EKGRP
    FROM MARC
    INTO TABLE IT_MARC
    FOR ALL ENTRIES IN IT_MARA
    WHERE MATNR = IT_MARA-MATNR.
ENDIF.

LOOP AT IT_MARA INTO WA_MARA.
*  READ TABLE IT_MARA INTO WA_MARA

*        WITH KEY MATNR = WA_MARA-MATNR.  "commented not require

  WA_FINAL-MATNR = WA_MARA-MATNR.
  WA_FINAL-MTART = WA_MARA-MTART.
  WA_FINAL-MBRSH = WA_MARA-MBRSH.
  WA_FINAL-MATKL = WA_MARA-MATKL.
  WA_FINAL-MEINS = WA_MARA-GEWEI.
  WA_FINAL-SPART = WA_MARA-SPART.
  CLEAR WA_MBEW.
  READ TABLE IT_MBEW INTO WA_MBEW WITH KEY MATNR = WA_MARA-MATNR.
  IF SY-SUBRC = 0.
    WA_FINAL-STPRS = WA_MBEW-STPRS.
    WA_FINAL-VPRSV = WA_MBEW-VPRSV.
    WA_FINAL-BWKEY = WA_MBEW-BWKEY.
  ENDIF.
  CLEAR WA_MARC.
  READ TABLE IT_MARC INTO WA_MARC WITH KEY MATNR = WA_MARA-MATNR.
  IF SY-SUBRC = 0.
    WA_FINAL-WERKS = WA_MARC-WERKS.
    WA_FINAL-EKGRP = WA_MARC-EKGRP.
  ENDIF.
  CLEAR WA_MAKT.
  READ TABLE IT_MAKT INTO WA_MAKT WITH KEY MATNR = WA_MARA-MATNR.
  IF SY-SUBRC = 0.
    WA_FINAL-SPRAS = WA_MAKT-SPRAS.
  ENDIF.
  APPEND WA_FINAL TO IT_FINAL.
  CLEAR WA_FINAL.
ENDLOOP.

ThomasZloch
Active Contributor
0 Kudos

What a perfect example...first there is awkward FAE handling, then awkward internal table handling...use a join and get it all into it_final in one statement...see Matt's reply.

Thomas

matt
Active Contributor
0 Kudos

I don't know - do you ever feel invisible? Here we are with a horrendous numbers of years of ABAP experience between us, and our advice is completely ignored.

0 Kudos

Maybe our avatars don't look reputable enough?

0 Kudos

Maybe is time for you to vote on Matt's poll...

0 Kudos

I did by now, but due to election secrecy I cannot disclose which box I ticked

0 Kudos

what is better ? (performance wise)

inner join or for all entries ?
any of them gonna be obsolete ?

matt
Active Contributor
0 Kudos

As I've said before, and probably will say many times until I eventually retire: in most cases INNER JOIN is more efficient than FOR ALL ENTRIES.

Read this for further discussion. http://scn.sap.com/polls/1987

As far as obsolescence goes, in a recent HANA workshop, the use of FAE was also discouraged.

Former Member
0 Kudos

This message was moderated.

Former Member
0 Kudos

hi

first try to understand the requirement, and learn what are the preqeuistes for all entries.

your code is completely wrong, i will send u the code in few minutes.

nagaraj

Former Member
0 Kudos

hi

try this code and first see whether entries are their in table for all fields.

SELECT MAtnr

       MTART

       MBRSH

       MATKL

       MEINS

       GEWEI

       SPART

   from mara

   INTO TABLE it_mara

   where matnr in s_matnr.

if it_mara[] is not initial.

   SELECT MATNR

BWKEY

VPRSV

STPRS

        FROM mbew

        INTO TABLE it_mbew

        FOR ALL ENTRIES IN it_mara

        where matnr = it_mara-matnr.

    

     SELECT MATNR

SPRAS

       FROM makt

       INTO TABLE it_final

       FOR ALL ENTRIES IN it_mara

       WHERE matnr = it_mara-matnr.

  

  

        SELECT matnr

werks

ekgrp

          FROM marc

          INTO TABLE it_marc

          FOR ALL ENTRIES IN it_mara

          where matnr = it_mara-matnr.

   ENDif.

LOOP AT it_mara INTO wa_mara.

   wa_final-matnr = wa_mara-matnr.

   wa_final-Mtart = wa_mara-mtart.

   wa_final-mbrsh = wa_mara-mbrsh.

   wa_final-matkl = wa_mara-matkl.

   wa_final-meins = wa_mara-gewei.

   wa_final-spart = wa_mara-spart.

     READ TABLE it_mbew INTO wa_mbew with key matnr = wa_mara-matnr.

   IF sy-subrc = 0.

     wa_final-stprs = wa_mbew-stprs.

     wa_final-vprsv = wa_mbew-vprsv.

     wa_final-bwkey = wa_mbew-bwkey.

   ENDIF.

   READ TABLE it_marc INTO wa_marc WITH key matnr = wa_mara-matnr.

   IF sy-subrc = 0.

     wa_final-werks = wa_marc-werks.

     wa_final-ekgrp = wa_marc-ekgrp.

   ENDIF.

   READ TABLE it_makt INTO wa_makt WITH KEY matnr = wa_mara-matnr.

   IF sy-subrc = 0.

     wa_final-spras = wa_makt-spras.

   ENDIF.

   APPEND wa_final to it_final.

CLEAR wa_final.

ENDLOOP.

with warm regards

nagaraj

matt
Active Contributor
0 Kudos

We're past FAE now and discussing how to use INNER JOIN, which is in most cases far more efficient.

Former Member
0 Kudos

This message was moderated.