About Collect Statement.

[Former Member]

hi experts,

i am genereating a report in FICO module. i have to fetch teh data from the field DMBTR (AMOUNT). it has both credits anddebits. i have to do calculations and should display the final balance in the report. each GL account number has different credits and debits. i have to display each account with the balance. if i am using collect statement it only making sum of eitehr debits or credits. i want both to be sum up. is tehre any other way to do the same..thnx in advance,

santosh.

[Former Member]

loop at itab.

if itab-shkzg = 'H'. "Credit

amount = amount - itab-dmbtr.

else. "Debit

amount = amount + itab-dmbtr.

endif.

endloop.

[Former Member]

Please give me reward point...

[Former Member]

Syntax Diagram

COLLECT

Syntax

COLLECT wa INTO itab [result].

Effect

This statement inserts the contents of a work area wa either as single row into an internal table itab or adds the values of its numeric components to the corresponding values of existing rows with the same key. As of Release 6.10, you can use result to set a reference to the inserted or changed row in the form of a field symbol or data reference.

Prerequisite for the use of this statement is that wa is compatible with the row type of itab and all components that are not part of the table key must have a numeric data type (i, p, f).

In standard tables that are only filled using COLLECT, the entry is determined by a temporarily created hash administration. The workload is independent of the number of entries in the table. The hash administration is temporary and is generally invalidated when the table is accessed for changing. If further COLLECT statements are entered after an invalidation, a linear search of all table rows is performed. The workload for this search increases in a linear fashion in relation to the number of entries.

In sorted tables, the entry is determined using a binary search. The workload has a logarithmic relationship to the number of entries in the table.

In hashed tables, the entry is determined using the hash administration of the table and is always independent of the number of table entries.

If no line is found with an identical key, a row is inserted as described below, and filled with the content of wa:

In standard tables the line is appended.

In sorted tables, the new line is inserted in the sort sequence of the internal table according to its key values, and the table index of subsequent rows is increased by 1.

In hashed tables, the new row is inserted into the internal table by the hash administration, according to its key values.

If the internal table already contains one or more rows with an identical key, those values of the components of work area wa that are not part of the key, are added to the corresponding components of the uppermost existing row (in the case of index tables, this is the row with the lowest table index).

The COLLECT statement sets sy-tabix to the table index of the inserted or existing row, in the case of standard tables and sorted tables, and to the value 0 in the case of hashed tables.

Outside of classes, you can omit wa INTO if the internal table has an identically-named header line itab. The statement then implicitly uses the header line as the work area.

COLLECT should only be used if you want to create an internal table that is genuinely unique or compressed. In this case, COLLECT can greatly benefit performance. If uniqueness or compression are not required, or the uniqueness is guaranteed for other reasons, the INSERT statement should be used instead.

The use of COLLECT for standard tables is obsolete. COLLECT should primarily be used for hashed tables, as these have a unique table key and a stable hash administration.

If a standard table is filled using COLLECT, it should not be edited using any other statement with the exception of MODIFY. If the latter is used with the addition TRANSPORTING, you must ensure that no key fields are changed. This is the only way to guarantee that the table entries are always unique and compressed, and that the COLLECT statement functions correctly and benefits performance. The function module ABL_TABLE_HASH_STATE can be used to check whether a standard table is suitable for editing using COLLECT.

Example

Compressed insertion of data from the database table sflight into the internal table seats_tab. The rows in which the key components carrid and connid are identical are compressed by adding the number of occupied seats to the numeric component seatsocc.

DATA: BEGIN OF seats,

carrid TYPE sflight-carrid,

connid TYPE sflight-connid,

seatsocc TYPE sflight-seatsocc,

END OF seats.

DATA seats_tab LIKE HASHED TABLE OF seats

WITH UNIQUE KEY carrid connid.

SELECT carrid connid seatsocc

FROM sflight

INTO seats.

COLLECT seats INTO seats_tab.

ENDSELECT.

Exceptions

Catchable Exceptions

CX_SY_ARITHMETIC_OVERFLOW

Cause: Overflow in integer field during totals formation

Runtime Error: COLLECT_OVERFLOW

Cause: Overflow in type p field during totals formation

Runtime Error: COLLECT_OVERFLOW_TYPE_P

Non-Catchable Exceptions

Cause: COLLECT used for non-numeric fields

Runtime Error: TABLE_COLLECT_CHAR_IN_FUNCTION

[Former Member]

Hi Santosh,

In this case,first of all sort the internal table accordng to the G/L account and then you can use the Control level statement AT NEW in the internal table for the G/L accounts.

At every new G/L account uptill the last row of that account,go through each of the rowa and fnd out if the entry is a debit entry,then amount=amonut - debit and if it is credit,then amount = amount + credit.

At the end of avary G/L account,use AT END and display or store the fnal amount in another variable and then display.

In case you have any further clarifications,do let me know.

Regards,

Puneet Jhari.

[Former Member]

Hi puneeth,

thnx for ur suggestion. but even then i am getting amount field as zero in final output. i wrote teh code like this.

SORT IT_BSIS BY HKONT.

LOOP AT IT_BSIS.

AT NEW HKONT.

READ TABLE IT_BSIS .

IF IT_BSIS-SHKZG = 'H'.

IT_BSIS-DMBTR1 = IT_BSIS-DMBTR1 - IT_BSIS-DMBTR.

ELSE.

IT_BSIS-DMBTR1 = IT_BSIS-DMBTR1 + IT_BSIS-DMBTR.

ENDIF.

ENDAT.

ENDLOOP.

thnx,

santosh.

[Former Member]

Hi

<b>COLLECT</b>

COLLECT wa INTO itab [result].

This statement inserts the contents of a work area wa either as single row into an internal table itab or adds the values of its numeric components to the corresponding values of existing rows with the same key. As of Release 6.10, you can use result to set a reference to the inserted or changed row in form of a field symbol or data reference.

Prerequisite for the use of this statement is that wa is compatible with the row type of itab. The row type must be flat and all components that are not part of the table key must have a numeric data type ( i, p, f).

If the internal table does not already contain a row with an identical key, the COLLECT statement has the same effect as the following form of the INSERT statement:

<b>INSERT wa INTO TABLE itab [result].</b>

A row, whose position depends on the table key and the table type, is inserted and filled with the contents of wa.

If the internal table already contains one or more rows with an identical key, those values of the components of work area wa that are not part of the key, are added to the corresponding components of the uppermost existing row (in the case of index tables, this is the row with the lowest table index).

The COLLECT statement sets sy-tabix to the table index of the inserted or existing row, in the case of standard tables and sorted tables, and to the value 0 in the case of hashed tables.

Outside of classes, you can omit wa INTO if the internal table has an identically-named header line itab. The statement then implicitly uses the header line as the work area.

<b>Example</b> Compressed insertion of data from the database table sflight into the internal table seats_tab. The rows in which the key components carrid and connid are identical are compressed by adding the number of occupied seats to the numeric component seatsocc.

DATA: BEGIN OF seats,

carrid TYPE sflight-carrid,

connid TYPE sflight-connid,

seatsocc TYPE sflight-seatsocc,

END OF seats.

DATA seats_tab LIKE HASHED TABLE OF seats

WITH UNIQUE KEY carrid connid.

SELECT carrid connid seatsocc

FROM sflight

INTO seats.

COLLECT seats INTO seats_tab.

ENDSELECT.

<b>collect</b>

... { ASSIGNING <fs> }

| { REFERENCE INTO dref }... .

With the ASSIGNING addition, the inserted or existing row is assigned to a field symbol <fs> and with the REFERENCE INTO addition, a reference to the inserted or existing row is set in a reference variable.

The syntax and meaning are the same as when specifying the output behavior in the READ TABLE statement and the same restrictions regarding the manipulation of key fields for sorted and hashed tables apply.

<b>Reward if usefull</b>

[Former Member]

Hi,

You can use the below code :

First calculate the debit and credit entries and then use Collect statement:

loop at itab.
    IF gt_output-shkzg = 'H'.
      gt_output-dmbtr = 0 - gt_output-dmbtr.
    ENDIF.
modify itab.
clear itab.
endloop.

*Now use the below code for collect
loop at itab.

itab1-dmbtr = itab-dmbtr.
  collect itab1.
endloop.

Thanks,

Sriram Ponna.

[Clemenss]

Hi Santosh,

the field DMBTR is defined in the dictionary using domain WERT7. This means there is no sign for negative values possible.

The field SHKZG provides the sign as 'H' for debit and 'S' for debit item. Depending on the required results, one of them should be MINUS.

You can use the field with a sign in ALV lists and for program internal calculations.

You can set the DMBTR = - DMBTR if SHKZG = 'H' and collect into results table.

Regards,

Clemens