collect usage in itab

[Former Member]

Hi

Ihave some doubt in using the collect statement.

I need to sum the qty field . I have done like this.

loop at itab.

select single sum( field1)

into var

from table1 join table2 on

( condition )

where table1~matnr = itab-matnr

and .

itab-field1 = var.

modify itab.

endloop.

Please help me for the above code to do using collect.

Since collect reduces the performance

Have a nice Day.

Thanks

Priya

[Former Member]

Hi

Ihave some doubt in using the collect statement.

I need to sum the qty field . I have done like this.

loop at itab.

select single sum( field1)

into var

from table1 join table2 on

( condition )

where table1~matnr = itab-matnr

and .

itab-field1 = var.

modify itab.

endloop.

Please help me for the above code to do using collect.

Since collect reduces the performance

Have a nice Day.

Thanks

Priya

[Former Member]

loop at itab.
select single sum( field1)
into var
from table1 join table2 on
( condition )
where table1~matnr = itab-matnr
and .

itab-field1 = var.
<b>collect itab.</b>
endloop.

[Former Member]

I dont want to use the looping statement but simply want to use the select query and then obtain the sum. I tried as accroding the document but iam getting the dumpl erroe sayign rsql is not right . So pls tell how to do the sql for this type

[Former Member]

Hi

You should use the COLLECT statament instead of APPEND, but here you're searching your data (quantity ?) into the same loop of the table you need to do the COLLECT and I don't believe it's a good thing use the COLLECT instead of MODIFY.

Max

[Former Member]

HI

GOOD

CHECK WITH THIS EXAMPLE AND IMPLEMENT ACCORDINGLY IN YOUR REPORT.

Example

Compressed sales figures for each company

DATA: BEGIN OF COMPANIES OCCURS 10,

NAME(20),

SALES TYPE I,

END OF COMPANIES.

COMPANIES-NAME = 'Duck'. COMPANIES-SALES = 10.

COLLECT COMPANIES.

COMPANIES-NAME = 'Tiger'. COMPANIES-SALES = 20.

COLLECT COMPANIES.

COMPANIES-NAME = 'Duck'. COMPANIES-SALES = 30.

COLLECT COMPANIES.

THANKS

MRUTYUN

[Former Member]

Hai Priya Jawahar

go through the document

that will solved your problems

Basic form

COLLECT [wa INTO] itab.

Addition

... SORTED BY f

Effect

COLLECT is used to create unique or compressed datsets. The key fields are the default key fields of the internal table itab .

If you use only COLLECT to fill an internal table, COLLECT makes sure that the internal table does not contain two entries with the same default key fields.

If, besides its default key fields, the internal table contains number fields (see also ABAP/4 number types ), the contents of these number fields are added together if the internal table already contains an entry with the same key fields.

If the default key of an internal table processed with COLLECT is blank, all the values are added up in the first table line.

If you specify wa INTO , the entry to be processed is taken from the explicitly specified work area wa . If not, it comes from the header line of the internal table itab .

After COLLECT , the system field SY-TABIX contains the index of the - existing or new - table entry with default key fields which match those of the entry to be processed.

Notes

COLLECT can create unique or compressed datasets and should be used precisely for this purpose. If uniqueness or compression are unimportant, or two values with identical default key field values could not possibly occur in your particular task, you should use APPEND instead. However, for a unique or compressed dataset which is also efficient, COLLECT is the statement to use.

If you process a table with COLLECT , you should also use COLLECT to fill it. Only by doing this can you guarantee that

the internal table will actually be unique or compressed, as described above and

COLLECT will run very efficiently.

If you use COLLECT with an explicitly specified work area, it must be compatible with the line type of the internal table.

Example

Compressed sales figures for each company

DATA: BEGIN OF COMPANIES OCCURS 10,

NAME(20),

SALES TYPE I,

END OF COMPANIES.

COMPANIES-NAME = 'Duck'. COMPANIES-SALES = 10.

COLLECT COMPANIES.

COMPANIES-NAME = 'Tiger'. COMPANIES-SALES = 20.

COLLECT COMPANIES.

COMPANIES-NAME = 'Duck'. COMPANIES-SALES = 30.

COLLECT COMPANIES.

The table COMPANIES now has the following appearance:

NAME SALES

Duck 40

Tiger 20

Addition

... SORTED BY f

Effect

COLLECT ... SORTED BY f is obsolete and should no longer be used. Use APPEND ... SORTED BY f which has the same meaning.

Note

Performance

The cost of a COLLECT in terms of performance increases with the width of the default key needed in the search for table entries and the number of numeric fields with values which have to be added up, if an entry is found in the internal table to match the default key fields.

If no such entry is found, the cost is reduced to that required to append a new entry to the end of the table.

A COLLECT statement used on a table which is 100 bytes wide and has a key which is 60 bytes wide and seven numeric fields is about approx. 50 msn (standardized microseconds).

Note

Runtime errors

COLLECT_OVERFLOW : Overflow in integer field when calculating totals.

COLLECT_OVERFLOW_TYPE_P : Overflow in type P field when calculating totals.

Thanks & regards

Sreenivasulu P

[Former Member]

I could see good copies of data in the page: http://www.sts.tu-harburg.de/teaching/sap_r3/ABAP4/collect.htm going over here.

[Former Member]

Hi

As I just said in my prevoius answer the COLLECT should be used instead of APPEND (or INSERT) and not MODIFY.

So Ramana your suggestion is not good idea:

loop at itab.

select single sum( field1)

into var

from table1 join table2 on

( condition )

where table1~matnr = itab-matnr

and .

itab-field1 = var.

• modify itab <---------------------

collect itab. <----

---

endloop.

In this case it's not easy to say how to do the collect, because we can't know how the ITAB was filled.

So Priya, let's know how you're filling the ITAB.

Max

[Former Member]

hI,

Iam taking the matnr werks and qty from the table1 and updating it.

then

iam using the above code.

iam finding the sum.

I just want to know whether i can ust hte collect in this case.

[Former Member]

COLLECT will total up the numeric fields if the character fields of the internal table are same. I am not sure in your table if you have char fields other than matnr and werks. If yes and the data differs there, collect will not work.

If you have only 3 fields, then whenever matnr and werks are same collect will sum up the qty field, no clauses required In fact you cannot specify any conditions.

[aris_hidalgo]

Hello Priya,

You could do what ramana said or you could use the statement ADD.

Regards,

P.S. Please award points if found useful. Thanks!

[Former Member]

If you want to sum the field on a particular field use GROUP BY:

select sum(field1)

INTO TABLE itab

from table1 join table2 on

( condition )

where table1~matnr = itab-matnr

<b>GROUP BY field2.</b>

If you want the total sum:

select sum(field1)

from table1 join table2 on

( condition )

where table1~matnr = itab-matnr

[Former Member]

data: begin of var occurs 0,

matnr

field1

endof itab.

select matnr sum( field1)

into table var

from table1 join table2 on

( condition ) for all entries of itab

where table1~matnr = itab-matnr group by matnr.

sort var by matnr.

loop at itab.

read table var binary key with key matnr = itab-matnr.

itab-field1 = var-field1.

modify itab.

endloop.