collect with inner join

[Former Member]

hi all,

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

tables : mseg , mkpf.

select mseg_{matnr mseg}menge mseg_{bwart mkpf}mblnr

into corresponding fields of table itab

from mseg

inner join mkpf on mkpf_{mblnr = mseg}mblnr

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

[Former Member]

Hi,

What is your

itab

table type? Standard or some type of sorted one?

[Former Member]

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

Regards,

Reema.

[Former Member]

Hi reema,

U mean, i should try for below code for collect

data : itab1 like itab occurs 0 with header line.

loop at itab into itab1.

collect itab into itab1.

append itab1.

endloop.

refresh itab.

append lines of itab1 to itab.

[Former Member]

Yes...Thats fine

[Former Member]

Reema ,

it not gives me sum but the original value from internal table

plz help me.

[Former Member]

select mseg_{matnr mseg}menge mseg_{bwart mkpf}mblnr

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.

"""WHERE condition was not mentioned in your code for the join"""""""

And also,

loop at itab into wa_itab1.

collect wa_itab into itab1.

endloop.

" Dont use append "

all the preceding fields should be character typed and the field to be summed should be numeric.Please check those fields in itab if its in the manner mentioned .

I tried a sample and its working for me

Reemz

[varma_narayana]

Hi ankita,

Have u seen my Second solution using COLLECT it will surely work for ur Req:

Try out..

Regards,

Narayana Varma

[Former Member]

Hello Ankita,

Is it working now?Please close this thread and award points if useful

Reemz

[varma_narayana]

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 mseg_{matnr mseg}menge mseg_{bwart mkpf}mblnr

into corresponding fields of table itab

from mseg

inner join mkpf on mkpf_{mblnr = mseg}mblnr

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.

[Former Member]

Hi Narayana,

I want sum of menge on matnr .

ur code does not give me sum but thre original value form internal table. plz help me more

[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)

[varma_narayana]

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 mseg_{matnr mseg}menge mseg_{bwart mkpf}mblnr mkpf~budat

into table itab

from mseg

inner join mkpf on mkpf_{mblnr = mseg}mblnr

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>

[Former Member]

Hi Narayana,

I got problem in this collect statement.

and that is its only works for first bwart not for others. at first bwart it gives right value but for other bwart it gives dump value.

plz help me.

[Clemenss]

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

[Clemenss]

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