Skip to Content
avatar image
Former Member

collect with inner join

hi all,

I m using below code to get sum according to matnr.

tables : mseg , mkpf.

select msegmatnr msegmenge msegbwart mkpfmblnr

into corresponding fields of table itab

from mseg

inner join mkpf on mkpfmblnr = msegmblnr

and mseg~matnr in matnr

and mseg~bwart in bwart

and mkpf~budat in dat.

collect itab.

but I am not able to get sum for it.

plz help me

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    Aug 21, 2007 at 09:02 AM

    Hi Ankita..

    If u want to Use COLLECT then follow this code.

    <b>Step 1: Declare itabs like this:</b>

    DATA : BEGIN OF ITAB,

    MATNR TYPE MSEG-MATNR,

    MENGE TYPE MSEG-MENGE,

    BWART TYPE MSEG-BWART,

    MBLNR TYPE MKPF-MBLNR,

    BUDAT TYPE MKPF-BUDAT,

    END OF ITAB.

    DATA : BEGIN OF ITAB_TOTAL,

    MATNR TYPE MSEG-MATNR,

    MENGE TYPE MSEG-MENGE,

    END OF ITAB_TOTAL.

    <b>Step 2: Join like this</b>

    select msegmatnr msegmenge msegbwart mkpfmblnr mkpf~budat

    into table itab

    from mseg

    inner join mkpf on mkpfmblnr = msegmblnr

    and mseg~matnr in matnr

    and mseg~bwart in bwart

    and mkpf~budat in dat.

    Step 3: Summarize using COLLECT like this

    LOOP AT ITAB .

    MOVE-CORRESPONDING ITAB TO ITAB_TOTAL.

    COLLECT ITAB_TOTAL.

    ENDLOOP.

    <b>Reward points if helpful</b>

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi ankita,

      a dump always occurs for a reason. One of the most-often causes for dump in collect is that the target field is too small and the dump is caused by an overflow. The moment the dump occurs, you may enter debugger and check the values. Look for a dictionary type with enough digits to take the value.

      Did you try the database solution I posted earlier?

      Regards,

      Clemens

      Message was edited by:

      Clemens Li

  • avatar image
    Former Member
    Aug 21, 2007 at 08:51 AM

    Hi,

    What is your

    itab

    table type? Standard or some type of sorted one?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 21, 2007 at 08:51 AM

    Collect ITAB should be in a a loop of the ITAB.Then only it will work

    Regards,

    Reema.

    Add comment
    10|10000 characters needed characters exceeded

  • Aug 21, 2007 at 08:54 AM

    Hi Ankita,,

    This is not the Way to use the collect Statement.

    Point 1: It must be used inside a loop

    Point 2: It will compare all the Non numeric fields.. not only MATNR.

    SO...Here you can use the AT NEW ... AT END OF to calculate the SUM.

    Check this code:

    select msegmatnr msegmenge msegbwart mkpfmblnr

    into corresponding fields of table itab

    from mseg

    inner join mkpf on mkpfmblnr = msegmblnr

    and mseg~matnr in matnr

    and mseg~bwart in bwart

    and mkpf~budat in dat.

    LOOP AT ITAB.

    AT NEW MATNR.

    Write:/ itab-matnr.

    ENDAT.

    WRITE:/ ITAB-BWART, ITAB-MBLNR, ITAB-MENGE.

    <b> AT END OF MATNR.

    SUM.

    Write:/ 'Total Stock for ', ITAB-MATNR , '=', ITAB-MENGE.

    ENDAT.</b>

    ENDLOOP.

    <b>Reward if Helpful.</b>

    ENDLOOP.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      check the type of the field which u need the sum.

      The statement SUM calculates the component total with the numeric data type ( i, p, f)

  • avatar image
    Former Member
    Aug 21, 2007 at 09:23 AM

    Hi ankita,

    what about let the database do the summing?

    select mseg~matnr sum( mseg~menge ) as menge mseg~bwart 
      into corresponding fields of table itab 
      from mseg
      inner join mkpf on mkpf~mblnr = mseg~mblnr
      where mseg~matnr in matnr
        and mseg~bwart in bwart
        and mkpf~budat in dat
      group by matnr bwart.
    

    Regards,

    Clemens

    sorry, typo.

    Message was edited by:

    Clemens Li

    Add comment
    10|10000 characters needed characters exceeded