Sams Teach Yourself ABAP/4^® in 21 Days

Day 13

Advanced Internal Tables, Part 2

Chapter Objectives
Filling an Internal Table from a Database Table
Using the lfa1, lfb1, lfc1, and lfc3 Sample Tables
Control Break Processing
Summary
Q&A
Workshop

Chapter Objectives

After you complete this chapter, you will be able to:

Fill an internal table from a database table using the most efficient constructs
Perform control break processing on internal tables using at and on change of

Filling an Internal Table from a Database Table

Quite often in ABAP/4, the data you put into an internal table comes from one or more database tables. This section presents the best ways to do this.

The ways of filling an internal table from the database fall into two categories:

Selecting multiple rows directly into the body of an internal table
Selecting single rows into a work area and then appending

Selecting Multiple Rows Directly into an Internal Table

To select multiple rows directly into the body of an internal table, use the into table addition of the select statement. It takes the selected rows and places them into the body of an internal table in a single operation known as an array operation. No work areas are used or needed.

An array operation is any statement that performs an operation on multiple rows of an internal table, instead of a single row at a time. Array operations are always more efficient than single row operations.

TIP

select into table is the most efficient way to fill an internal table from the database.

Syntax for the into table Addition of the select Statement

The following is the syntax for the into table addition of the select statement.

(a) select *
(b) select f1 f2 . . .
                         
             from dbtab into [corresponding fields of] table it.

where:

dbtab is the name of a database table.
f1 and f2 are fields within dbtab.
it is the name of an internal table.

The following points apply:

it can have a header line.
Other additions, such as where and order by, can follow it.
endselect is not used with into table. select into table does not start a loop, consequently no endselect is needed.

The select into table statement places all selected rows directly into the body of it. Existing internal table contents are first discarded. Listing 13.1 contains a sample program using select into table.

Listing 13.1 This Program Reads Rows from a Database Table Directly into an Internal Table

1  report ztx1301.
2  tables ztxlfa1.
3  data it like ztxlfa1 occurs 23 with header line.
4 
5  select * from ztxlfa1 into table it.   "don't code an endselect
6  loop at it.
7      write: / it-lifnr, it-name1.
8      endloop.
9 
10 select * from ztxlfa1 into table it
11     where lifnr between 'V2' and 'V5'.
12 skip.
13 loop at it.
14     write: / it-lifnr, it-name1.
15     endloop.
16 free it.

The code in Listing 13.1 produces this output:

V9         Code Now, Specs Later Ltd.         
1000       Parts Unlimited                    
1010       Industrial Pumps Inc.              
1020       Chemical Nation Ltd.               
1030       ChickenFeed Ltd.                   
1050       The Bit Bucket                     
1060       Memory Lane Ltd.                   
1070       Flip My Switch Inc.                
V10        Duncan's Mouse Inc.                
V6         Anna Banana Ltd.                  
V8         Smile When You Say That Ltd.       
V11        Weiner Schnittsel Inc.             
V12        Saurkrouten                        
1040       Motherboards Inc.                  
1080       Silicon Sandwich Ltd.              
1090       Consume Inc.                       
2000       Monitors and More Ltd.             
V1         Quantity First Ltd.                
V2         OverPriced Goods Inc.              
V3         Fluffy Bunnies Ltd.                
V4         Moo like a Cow Inc.                
V5         Wolfman Sport Accessories Inc.     
V7         The Breakfast Club Inc.            
                                              
V2         OverPriced Goods Inc.              
V3         Fluffy Bunnies Ltd.           
V4         Moo like a Cow Inc.           
V5         Wolfman Sport Accessories Inc.

Line 5 retrieves all rows from database table ztxlfa1 and places them directly into the body of the internal table it. The header line is not used and its contents are unchanged.
Line 10 is like line 5, except that it only retrieves vendor numbers between V2 and V5. The existing contents of it are discarded before it is filled.

NOTE

Don't use an endselect with select into table. A syntax error will occur.

select into table and Sorting

Suppose your program already reads data into an internal table. If that data needs to be sorted, use the sort statement. Don't use order by on select even if those fields are supported by an index. On a quiet, standalone system, measurements reveal that the sort statement is a little faster. Even if they were at a par, sort would still be the preferred method because it offloads cycles from the database server to the application server.

NOTE

Don't use order by with select into table. Use sort on the internal table instead.

Fields You Select Must Fit into the Internal Table

Imagine this scenario:

A row is retrieved from dbtab.
A row is allocated in it.
The row from dbtab is moved byte-by-byte into the new row in it.

Each row from dbtab is moved byte-by-byte into a new row of it, which is exactly like assigning one field string to another. There is only one difference between this and a field string assignment: dbtab can be shorter (in bytes) than it, but it cannot be longer than it. If it is longer, a short dump (error SAPSQL_SELECT_TAB_TOO_SMALL) occurs. The data types and lengths of each sending field in dbtab should match the receiving field in it. Any remaining fields in it are filled with initial values (blanks or zeros). Figures 13.1 through 13.4 illustrate this point.

Figure 13.1 : Database table ztx1302 contains three fields: f1, f2, and f3. f1 is a single byte, f2 is two bytes long, and f3 is one byte.

Figure 13.2 : The * causes all fields to be selected, in order, from database table ztx1302. They fit exactly into each row of it because the structure of it matches that of the database table.

Figure 13.3 : In this example, only the first two fields are selected from database table ztx1302, for a total of three bytes. They are placed byte-by- byte into the first three bytes of each row of it.

Figure 13.4 : Again, the first two fields are selected from database table ztx1302, but this time in a different order than they appear in the internal table. This causes corrupt data when they are placed byte-by-byte into each row of it. it-f1 receives the first byte of f2, and it-f2 receives the last byte of f2 and one byte from f1.

Table 13.1 summarizes the rules for using select with into table. Selected fields must fit into the internal table. This table describes the restrictions you will face.

Table 13.1 Rules for Using sELECT with INTO TABLE

Statement	Which Fields Are Selected?	Restrictions
`Select *`	All fields in the database table	The internal table must contain at least as many fields as the database table, and they must be like the database table fields.
`Select f1`	`f1` from the database table	The internal table must begin with a field like `f1`, or have only one field and it must be like `f1`.

Listings 13.2 and 13.3 illustrate this concept.

Listing 13.2 What Happens if Table Structures Differ When Using the Select-into Statement

1  report ztx1302.
2  tables ztxlfa1.
3  data               begin of it occurs 2.
4  include structure      ztxlfa1.
5  data:                  invoice_amt type p,
6                         end of it.
7 
8  select * from ztxlfa1 into table it where land1 = 'DE'.
9  loop at it.
10     write: / it-lifnr, it-land1, it-regio, it-invoice_amt.
11     endloop.
12
13 skip.
14 select lifnr land1 regio from ztxlfa1 into table it where land1 = 'DE'.
15 loop at it.
16     write: / it-mandt, it-lifnr, it-land1, it-regio.
17     endloop.
18 free it.

The code in Listing 13.2 produces this output:

V11        DE  07                0 
V12        DE  14                0 
V8         DE  03                0 

V11 DE         07
V12 DE         14
V8  DE         03

Line 3 defines it as having all of the fields of DDIC structure ztxlfa1, plus an additional field.
Line 8 selects all fields from ztxlfa1. They fill the beginning of each row in it. Invoice_amt is set to zero.
Line 14 selects lifnr land1 and regio into the first part of each row of it. However, it begins with mandt, not lifnr, because the DDIC structure ztxlfa1 begins with mandt. The output shows that the first three bytes of lifnr end up in it-mandt, it-lifnr is shifted left three bytes and also contains land1, and regio is empty.

Listing 13.3 Your Program Will Produce a Short Dump If You Try to Put More Fields into an Internal Table than Exist in That Table

1  report ztx1303.
2  tables ztxlfa1.
3  data: begin of it occurs 23,
4            lifnr like ztxlfa1-lifnr,    "this is a char 10 field
5            land1 like ztxlfa1-land1,    "this is a char  3 field
6            end of it.
7 
8  *The next line causes a short dump. The internal table is too narrow.
9  select * from ztxlfa1 into table it.

The code in Listing 13.3 produces the output shown in Figure 13.5.

Figure 13.5 : This short dump occurs when you run report ztx1303. It happens when you select more fields than will fit into the internal table.

Line 3 defines an internal table that has two components. The total length of it is 13 bytes.
Line 9 selects all fields from ztxlfa1 into it. The total length of all of these fields is more than 13 bytes. Consequently, a short dump occurs.

Using the corresponding fields Addition

If the components of the internal table aren't in the same order as those from the database, or if they don't have the same data type and length, you can use the corresponding fields addition. This addition has the same effect as the move corresponding statement does on a field string: It moves fields from the database table into fields of the same name in the internal table body.

TIP

corresponding fields performs one assignment per field instead of a single assignment for the entire row, so it adds overhead to the select statement. You should use it only when necessary.

The following points apply:

The order of the sending and receiving fields does not matter.
Sending fields that do not have a corresponding receiving field are discarded.
Receiving fields that do not have a corresponding sending field are unchanged.

Listing 13.4 shows efficient and inefficient uses for this addition.

Listing 13.4 Using the corresponding fields Addition to Fill Internal Tables from the Database

1  report ztx1304.
2  tables ztxlfa1.
3  data: begin of it1 occurs 23,
4            lifnr     like ztxlfa1-lifnr,
5            lifnr_ext like ztxlfa1-lifnr,
6            land1     like ztxlfa1-land1,
7            end of it1,
8        begin of it2 occurs 23,
9            lifnr     like ztxlfa1-lifnr,
10           land1     like ztxlfa1-land1,
11           end of it2.
12
13 * This is efficient usage:
14 select lifnr land1 from ztxlfa1
15     into corresponding fields of table it1
16     where lifnr between 'V10' and 'V12'.
17 loop at it1.
18     write: / it1-lifnr, it1-land1.
19     endloop.
20
21 * This is inefficient:
22 select * from ztxlfa1
23     into corresponding fields of table it2
24     where lifnr between 'V10' and 'V12'.
25 skip.
26 loop at it1.
27     write: / it1-lifnr, it1-land1.
28     endloop.
29
30 * Instead, write:
31 select lifnr land1 from ztxlfa1 into table it2
32     where lifnr between 'V10' and 'V12'.
33 skip.
34 loop at it2.
35     write: / it2-lifnr, it2-land1.
36     endloop.
37 free: it1, it2.

The code in Listing 13.4 produces this output:

     V10        CC 
     V11        DE 
     V12        DE 
                   
     V10        CC 
     V11        DE 
     V12        DE 
                   
     V10        CC 
     V11        DE 
     V12        DE

Line 14 reads the lifnr and land1 fields from table ztxlfa1 into the lifnr and land1 fields of internal table it1. The internal table has field lifnr_ext between lifnr and land1, so you cannot use into table. If it1 requires the fields to be in this order, the usage of corresponding fields is appropriate here.
Line 22 selects all fields from table ztxlfa1 into only two fields of table it2. This is inefficient. Instead, line 31 shows that by selecting only the needed fields in the correct order, you can use into table.

Adding Rows one by one Using select

Using select to add rows one at a time requires the use of a work area and a second statement such as append, insert, or collect. Omitting the table addition causes the row to be assigned to a work area. It is common to use the header line of an internal table as an explicit work area. Alternatively, you can use the default table work area (defined using tables).

Listing 13.5 shows some examples of using select to add rows one at a time to an internal table.

Listing 13.5 Using select with the append Statement to Fill Internal Tables

1  report ztx1305.
2  tables ztxlfa1.
3  data it like ztxlfa1 occurs 2 with header line.
4 
5  *Do it this way
6  select * from ztxlfa1 into it   "notice 'table' is omitted so the
7           where land1 = 'DE'.    "row goes into the header line of it
8      append it.
9      endselect.
10
11 loop at it.
12     write: / it-lifnr, it-land1, it-regio.
13     endloop.
14 refresh it.
15
16 *Or this way
17 select * from ztxlfa1           "no 'into' so the row goes into the
18          where land1 = 'DE'.    "default table work area
19     append ztxlfa1 to it.       "and then is appended to it
20     endselect.
21
22 skip.
23 loop at it.
24     write: / it-lifnr, it-land1, it-regio.
25     endloop.
26 refresh it.
27
28 *Not this way
29 select * from ztxlfa1           "row goes into default table work area
30          where land1 = 'DE'.
31     it = ztxlfa1.               "then is assigned to the header line
32     append it.
33     endselect.
34
35 skip.
36 loop at it.
37     write: / it-lifnr, it-land1, it-regio.
38     endloop.
39 free it.

The code in Listing 13.5 produces this output:

     V11        DE  07
     V12        DE  14
     V8         DE  03
                     
     V11        DE  07
     V12        DE  14
     V8         DE  03
                     
     V11        DE  07
     V12        DE  14
     V8         DE  03

On line 6, table does not appear before it, so the fields from ztxlfa1 go into the header line for it. Line 8 appends the header line to it.
On line 17, there is no into clause, so each row goes into the default table work area ztxlfa1. Line 19 appends work area ztxlfa1 to the internal table (the header line is not used).
Line 29 places each row into work area ztxlfa1. Line 31 moves ztxlfa1 to the header line of it before appending. This is the least efficient method.

into corresponding fields can also be used to place data into a work area instead of into the body of the internal table. The effect is similar to that of the move-corresponding statement and is more efficient. Listing 13.6 shows how.

Listing 13.6 How the corresponding fields Addition to the select Statement Produces the Same Effect as the move-corresponding Statement

 1 report ztx1306.
 2 tables ztxlfa1.
 3 data: begin of it occurs 2,
 4           lifnr     like ztxlfa1-lifnr,
 5           row_id    like sy-index,
 6           land1     like ztxlfa1-land1,
 7           end of it.
 8
 9 *Do it this way:
10 select lifnr land1 from ztxlfa1
11        into corresponding fields of it   "notice 'table' is omitted
12        where land1 = 'DE'.
13     append it.
14     endselect.
15
16 loop at it.
17     write: / it-lifnr, it-row_id, it-land1.
18     endloop.
19 refresh it.
20
21 *Not this way:
22 select * from ztxlfa1
23        where land1 = 'DE'.
24     move-corresponding ztxlfa1 to it.
25     append it.
26     endselect.
27
28 skip.
29 loop at it.
30     write: / it-lifnr, it-row_id, it-land1.
31     endloop.
32 free it.

The code in Listing 13.6 produces this output:

     V11                 0  DE
     V12                 0  DE
     V8                  0  DE
     
     V11                 0  DE
     V12                 0  DE
     V8                  0  DE

On line 11, the table addition is omitted from before it so that the row goes into the header line. lifnr goes into it-lifnr and land1 goes into it-land1. Line 7 appends the header line to it.

Line 22 moves rows into work area ztxlfa1. Line 24 uses an additional statement-move-corresponding-to transfer the work area to the header line it. Line 25 then appends the header line to the body of it. This accomplishes the same result as the first select, but it involves an additional statement and so is less efficient.

Summing Up select, Internal Tables, and Efficiency

Table 13.2 contains a list of the various forms of select as it is used with internal tables and their relative efficiency. They are in descending order of most-to-least efficient.

Table 13.2 Various Forms of SELECT when Filling an Internal Table

Statement(s)	Writes To
`select into table it`	Body
`select into corresponding fields of table it`	Body
`select into it`	Header line
`select into corresponding fields of it`	Header line

Using the lfa1, lfb1, lfc1, and lfc3 Sample Tables

Before discussing the next topic, it will be helpful to become more acquainted with the sample tables ztxlfa1, ztxlfb1, ztxlfc1, and ztxlfc3; they will be frequently used in the examples and exercises that follow. These tables are based on the R/3 tables lfa1, lfb1, lfc1, and lfc3. Please review the structures of these tables within the DDIC as you read the descriptions below.

SAP designed R/3 to be usable by a conglomerate that consists of multiple companies. During initial R/3 configuration, a company code is assigned to each company in the conglomerate. The company code forms part of the primary key in many master data tables, which enables the conglomerate to keep the information for all of its companies in a single database. Management can run reports for an individual company code as well as consolidated reports spanning multiple companies.

Table lfa1 contains vendor master information that is consistent across all companies. Ignoring mandt, its primary key consists only of lifnr, the vendor number. The fields of lfa1, for example, are vendor name and address, telephone numbers, spoken language, and industry key (defined by the type of product the vendor produces, such as chemical, agricultural, and so on).

Table lfb1 contains vendor master information that is specific to a company. Its primary key consists of lifnr and bukrs: the company code field (see Figure 13.6). Stored within lfb1 are the company's account number with the vendor, reconciliation account number, withholding tax information, an interest calculation indicator, and so on.

Figure 13.6 : The relationship between the primary keys.

Figure 13.6 is the relationship between the primary keys of tables lfa1 through lfc3. They all begin with lifnr, and lfa1 uses it as the entire primary key. lfb1 uses the vendor number and company code. lfc1 uses vendor number, company code, and fiscal year. lfc3 uses those same fields plus a special G/L indicator.

Table lfc1 contains G/L (general ledger) transaction figures. Each row holds one fiscal year's worth of transaction figures for a vendor within a company. The primary key consists of lifnr, bukrs, and gjahr, the fiscal year.

The set of fields named umNNs, umNNh, and umNNu is repeated 16 times within an lfc1 row. Each of the first 12 sets contains the debit postings, credit postings, and sales for one posting period (usually a month) of the fiscal year. The last four sets are additional closing periods used to contain year-end accounting adjustments.

NOTE

There can be either 12 or 13 posting periods in a fiscal year. This is deter-mined when the system is initially configured. If there are 13, then there are only three additional closing periods.

Table lfc3 contains special G/L transaction figures. Special G/Ls use an alternative reconciliation account in the general ledger. Each row of lfc3 holds a summary of the special G/Ls for an entire fiscal year in three fields: saldv, solll, and habnl. These contain the balance carried forward and the total debit and credit posting for the fiscal year. The primary key is the same as lfc1, plus a special G/L indicator: shbkz. The value in this field indicates which alternative reconciliation account is used.

Most master data tables use a similar model for their primary key structures.

In this book, simplified versions of these tables are used: ztxlfa1, ztxlfb1, ztxlfc1, and ztxlfc3. These were created and populated by the setup routine.

Control Break Processing

After you fill an internal table with data, you often need to write the data out. This output will frequently contain summary information (such as totals) at the top or bottom of the report. There might also be interim summaries (such as subtotals) within the body of the report.

For example, suppose you need to write the G/L figures from ztxlfc1 for each vendor, with subtotals by fiscal year and a grand total at the bottom of the report.

To do this, you can read the data into an internal table and then, within loop at, use the following statements:

at first / endat
at last / endat
at new / endat
at end of / endat
sum
on change of / endon

The first statement of each of these statement pairs-except for sum-controls when the code that lies between them is executed. This type of control is called a control break. Their purpose is to execute the code between them whenever a specific condition in the data is detected during the processing of the loop.

Using the at first and at last Statements

Use the at first and at last statements to perform processing during the first or last loop pass of an internal table.

Syntax for the at first and at last Statements

The following is the syntax for the at first and at last statements.

loop at it.
    ---
    at first.
        ---
        endat.
    ---
    at last.
        ---
        endat.
    ---
    endloop.

where:

it is an internal table.
--- represents any number of lines of code (even zero).

The following points apply:

These statements can only be used within loop at; they cannot be used within select.
at first does not have to come before at last. The order of these statements can be interchanged.
These statements can appear multiple times within the same loop. For example, you could have two at first and three at last statements within one loop and they can appear in any order.
These statements should not be nested inside of one another (that is, at last should not be placed inside of at first and endat).
There are no additions to these statements.

The first time through the loop, the lines of code between at first and endat are executed. The last time through the loop, the lines of code between at last and endat are executed. If there are multiple occurrences of at first, they are all executed. at last behaves in a similar fashion.

Use at first for:

Loop initialization processing
Writing totals at the top of a report
Writing headings

Use at last for:

Loop termination processing
Writing totals at the bottom of a report
Writing footings

Listing 13.7 shows a sample program that uses these constructs.

Listing 13.7 Using at first to Write Headings and at last to Underline the Last Line

1  report ztx1307.
2  tables ztxlfc3.
3  data it like ztxlfc3 occurs 25 with header line.
4  select * from ztxlfc3 into table it where shbkz = 'Z'.
5  loop at it.
6      at first.
7          write: /  'Vendor',
8                 12 'Cpny',
9                 17 'Year',
10                22 'Bal C/F'.
11         uline.
12         endat.
13     write: /   it-lifnr,
14            12  it-bukrs,
15            17  it-gjahr,
16            22  it-saldv.
17     at last.
18         write: /  '----------',
19                12 '----',
20                17 '----',
21                22 '-------------------'.
22         endat.
23     endloop.
24 free it.

The code in Listing 13.7 produces this output:

Vendor     Cpny Year            Bal C/F
----------------------------------------
1000       1000 1995                0.00    
1000       1000 1996            5,000.00    
1000       1000 1998            4,000.00    
1040       4000 1997                0.00    
1070       2000 1997            1,000.00    
1090       2000 1997              250.50    
V1         1000 1992            1,000.00    
V1         3000 1990            1,000.00    
V1         3000 1994            1,000.00    
V4         4000 1997              100.00    
V6         2000 1997            1,000.00    
V6         4000 1997                0.00    
---------- ---- ---- -------------------

Lines 6 through 11 are only executed the first time through the loop. This example writes headings followed by an underline (line 11).
Lines 13 through 16 write out the contents of a row. They are executed for every row.
Lines 17 through 22 are only executed the last time through the loop. This example writes an underline at the bottom of each of the columns.

Using the Unexpected: Component Values Gone

Between the at first and endat, or between the at last and endat, the component values of the work area row will not contain any data. The default key fields are filled with * (asterisks) and the numeric fields are set to zeros. The endat restores the contents to the values they had prior to entering the at. Changes to the work area within at and endat are lost.

Listing 13.8 demonstrates that the components of the default key fields are filled with asterisks, and the non-key fields filled with zeros, inside an at or endat statement.

Listing 13.8 The Contents of the Internal Table Fields Between at and endat

1  report ztx1308.
2  tables ztxlfc3.
3  data it like ztxlfc3 occurs 1 with header line.
4  select * up to 1 rows from ztxlfc3 into table it.
5  loop at it.
6      write: / 'Before ''at first'':',
7             / it-lifnr, it-bukrs, it-gjahr, it-shbkz, it-saldv,
8               it-solll.
9      at first.
10         write: / 'Inside ''at first'':',
11                / it-lifnr, it-bukrs, it-gjahr, it-shbkz, it-saldv,
12                  it-solll.
13         it-lifnr = 'XXXX'.
14         endat.
15     write: / 'Between ''at first'' and ''at last'':',
16            / it-lifnr, it-bukrs, it-gjahr, it-shbkz, it-saldv,
17              it-solll.
18     at last.
19         write: / 'Inside ''at last'':',
20                / it-lifnr, it-bukrs, it-gjahr, it-shbkz, it-saldv,
21                  it-solll.
22         it-lifnr = 'XXXX'.
23         endat.
24     write: / 'After ''at last'':',
25            / it-lifnr, it-bukrs, it-gjahr, it-shbkz, it-saldv,
26              it-solll.
27     endloop.
28 free it.

The code in Listing 13.8 produces this output:

Before 'at first':
1000       1000 1990 A            1,000.00               500.00
Inside 'at first':
********** **** **** *                0.00                 0.00
Between 'at first' and 'at last':
1000       1000 1990 A            1,000.00               500.00
Inside 'at last':
********** **** **** *                0.00                 0.00
After 'at last':
1000       1000 1990 A            1,000.00               500.00

Line 4 selects one record from ztxlfc3 into it.
Within the loop that begins on line 5, the contents of the row are written out first. They are then written again inside of an at first and endat. The output shows that the default key fields contain asterisks and the rest contain zeros.
Line 13 assigns a new value to it-lifnr.
After the endat on line 14, the output shows that all values have been restored to the work area. Changes to the header line made inside of the at / endat are lost.
Line 18 shows that at last exhibits the same behavior.
Line 27 exits the loop on the first pass to keep the output simple.

Using the at new and at end of Statements

Use the at new and at end of statements to detect a change in a column from one loop pass to the next. These statements enable you to execute code at the beginning and end of a group of records.

Syntax for the at new and at end ff Statements

The following is the syntax for the at new and at end of statements.

sort by c.
loop at it.
    ---
    at new c.
        ---
        endat.
    ---
    at end of c.
        ---
        endat.
    ---
    endloop.

where:

it is an internal table.
c is a component of it.
--- represents any number of lines of code (even zero).

The following points apply:

These statements can only be used within loop at; they cannot be used within select.
at new does not have to come before at end of. These statements can appear in any order.
These statements can appear multiple times within the same loop. For example, you could have two at new and three at end of statements within one loop and they can appear in any order.
These statements should not be nested inside of one another (that is, at end of should not be placed inside of at new / endat).
There are no additions to these statements.

Using at new

Each time the value of c changes, the lines of code between at new and endat are executed. This block is also executed during the first loop pass or if any fields to the left of c change. Between at and endat, the numeric fields to the right of c are set to zero. The non-numeric fields are filled with asterisks (*). If there are multiple occurrences of at new, they are all executed. at end of behaves in a similar fashion.

A control level is the component named on a control break statement; it regulates the control break. For example, in the following code snippet, f2 is a control level because it appears on the at new statement.

loop at it.
    at new f2.
        "(some code here)
        endat.
    endloop.

It is said that a control break is triggered if the control level changes. This means that when the contents of the control level change, the code between the at and endat is executed.

A control break is also triggered if any of the fields prior to the control level in the structure change. Therefore, you should define the internal table structure to begin with the fields that form your control levels. You must also sort by all fields prior to and including c.

Between at and endat, numeric fields to the right of the control level will be zero and non-numeric fields will be filled with asterisks.

Figures 13.7 and 13.8 illustrate the use of at new.

Figure 13.7 : This figure shows that the control level is triggered when the value of f1 changes. It also shows that f2 contains an asterisk within the control break (between at and endat).

Figure 13.8 : This figure shows that the control level is triggered when f2 or f1 changes. This happens because f1 is prior to f2 in the structure of it.

This program provides a working example of Figures 13.7 and 13.8. It illustrates the point that a control break is triggered whenever the control level changes or any field prior to the control level changes.

Listing 13.9 The Code in Figures 13.7 and 13.8 Is Reproduced in a Working Example

 1 report ztx1309.
 2 data: begin of it occurs 4,
 3           f1,
4            f2,
5            end of it.
6 
7  it = '1A'. append it. "Fill it with data
8  it = '3A'. append it.
9  it = '1B'. append it.
10 it = '2B'. append it.
11
12 sort it by f1.        "it now looks like figure 13.7
13 loop at it.
14     at new f1.
15         write: / it-f1, it-f2.
16         endat.
17     endloop.
18 skip.
19 sort it by f2.        "it now looks like figure 13.8
20 loop at it.
21     at new f2.
22         write: / it-f1, it-f2.
23         endat.
24     endloop.
25 skip.
26 sort it by f1 f2.     "it now looks like figure 13.8
27 loop at it.
28     at new f1.
29         write: / it-f1.
30         endat.
31     at new f2.
32         write: /4 it-f2.
33         endat.
34     endloop.
35 free it.

The code in Listing 13.9 produces this output:

Line 12 sorts it by f1. On line 14, at new is triggered the first time through the loop and each time f1 changes.
Line 19 sorts it by f2. On line 20, at new is triggered the first time through or each time the value of f1 or f2 changes. This happens because a control level is triggered each time f2 changes or any field prior to f2 changes.
Line 26 sorts it by both f1 and f2. On line 28, at new is triggered each time the value of f1 changes, and on line 31, at new is triggered each time f1 or f2 changes.

Using at end of

The lines of code between at end of and endat are executed:

If the control level changes.
If any field prior to the control level changes.
If this is the last row of the table.

Listing 13.10 illustrates this statement.

Listing 13.10 How the at new and at end of Are Triggered by the Field Changes and Control Level Changes

1  report ztx1310.
2  data: begin of it occurs 4,
3            f1,
4            f2,
5            end of it.
6 
7  it = '1A'. append it. "Fill it with data
8  it = '3A'. append it.
9  it = '1B'. append it.
10 it = '2B'. append it.
11
12 sort it by f1.
13 loop at it.
14     at new f1.
15         write: / 'start of:', it-f1.
16         endat.
17     write: /4 it-f1.
18     at end of f1.
19         write: / 'end   of:', it-f1.
20         endat.
21     endloop.
22 free it.

The code in Listing 13.10 produces this output:

start of: 1  
     1         
     1         
  end   of: 1  
  start of: 2  
     2         
  end   of: 2  
  start of: 3  
     3         
  end   of: 3

Line 12 sorts it by f1.
On line 14, at new is triggered each time f1 changes.
On line 18, at end of is triggered if the control level will change when the next row is read or if this is the last row.

CAUTION

Do not use control break statements within a loop at it where . . . con-struct; the effects are unpredictable.

Using the sum Statement

Use the sum statement to calculate totals for the rows of a control level.

Syntax for the sum Statement

The following is the syntax for the sum statement.

at first/last/new/end of.
    ---
    sum.
    ---
    endat.

where:

--- represents any number of lines of code.

Sum calculates a total for the current value of the control level that contains it. This is clearer if you imagine that it does the following:

It finds all rows that have the same values within the control level field and all fields to the left of it.
It sums each numeric column to the right of the control level.
It places the totals in the corresponding fields of the work area.

Listing 13.11 illustrates this statement.

Listing 13.11 Using the sum Statement to Calculate the Totals of a Control Level

1  report ztx1311.
2  data: begin of it occurs 4,
3            f1,
4            f2 type i,
5            f3 type i,
6            end of it.
7 
8  it-f1 = 'A'. it-f2 = 1. it-f3 = 10. append it.
9  it-f1 = 'B'. it-f2 = 3. it-f3 = 30. append it.
10 it-f1 = 'A'. it-f2 = 2. it-f3 = 20. append it.
11 it-f1 = 'B'. it-f2 = 4. it-f3 = 40. append it.
12
13 sort it by f1.        "it now looks like figure 13.9
14 loop at it.
15     at new f1.
16         sum.
17         write: / 'total:', it-f1, it-f2, it-f3.
18         endat.
19     write: /11 it-f2, it-f3.
20     endloop.
21 free it.

The code in Listing 13.11 produces this output:

total: A      3      30
                1      10
                2      20
total: B      7      70
                3      30 
                4      40

Line 13 sorts it by f1 ascending. The contents after sorting appear as shown in Figure 13.9.
The first time through the loop, the code between lines 15 and 18 is executed. The control level contains the value 'A'.
Before line 16 is executed, the work area fields to the right of the control level contain zeros. The sum statement finds all adjacent rows that have an 'A' in f1. It sums the numeric columns to the right of the control level and places the results in the work area (see Figure 13.9).
Figure 13.9 : This figure illustrates what occurs in Listing 13.11. The first time through the loop the control break is triggered and the sum statement is executed. All rows having the same value as the control level are found and the numeric fields to the right of the control level are summed up into the work area.
In the second iteration of the loop, at new is not triggered.
In the third loop iteration, f1 contains 'B' and at new (line 15) is triggered.
Before line 16 is executed, the work area fields to the right of the control level contain zeros. The sum statement finds all adjacent rows that have a 'B' in f1. It sums the numeric columns to the right of the control level and places the results in the work area (see Figure 13.10).
Figure 13.10: This figure shows what transpires the second time the control break is triggered and the sum statement is executed.

TIP

Summing can result in overflow because the total is placed into a field of the same length. Overflow causes a short dump with the error SUM_OVERFLOW. When using sum, avoid overflow by increasing the length of numeric fields before populating the internal table.

Using the on change of Statement

Another statement you can use to perform control break processing is on change of. It behaves in a manner similar to at new.

Syntax for the on change of Statement

The following is the syntax for the on change of statement.

on change of v1 [or v2 . . .].
    ---
[else.
    ---]
    endon.

where:

v1 and v2 are variable or field string names.
. . . indicates that any number of or conditions might follow.
--- represents any number of lines of code.

The following points apply:

If the value of any of the variables (v1, v2, and so on) changes from one test to the next, the statements following on change of are executed.
If no change is detected and else is specified, the statements following else are executed.

on change of differs from at new in the following respects:

It can be used in any loop construct, not just loop at. For example, it can be used within select and endselect, do and enddo, or while and endwhile, as well as inside get events.
A single on change of can be triggered by a change within one or more fields named after of and separated by or. These fields can be elementary fields or field strings. If you are within a loop, these fields do not have to belong to the loop.
When used within a loop, a change in a field to the left of the control level does not trigger a control break.
When used within a loop, fields to the right still contain their original values; they are not changed to contain zeros or asterisks.
You can use else between on change of and endon.
You can use it with loop at it where . . ..
You can use sum with on change of. It sums all numeric fields except the one(s) named after of.
Any values changed within on change of remain changed after endon. The contents of the header line are not restored as they are for at and endat.

Behind the Scenes of on change of

When a loop begins execution, the system creates a global auxiliary field for each field named in an on change of statement contained by the loop. On creation, these fields are given default initial values (blanks or zeros). They are freed when the loop ends.

Each time on change of is executed, the contents of its fields are compared with the contents of the global auxiliary fields. If they are different, the on change of is triggered and the auxiliary fields are updated with the new values. If they are the same, the code within on change of is not executed.

NOTE

Because global auxiliary fields do not exist outside a loop, you cannot use on change of outside of a loop.

This concept is graphically illustrated in Figures 13.11 through 13.16.

Figure 13.11: This is the first time though a loop using on change of.

Figure 13.12: When on change of is triggered, the auxiliary field is updated.

Figure 13.13: This is the second loop pass. The auxiliary field contents match and on change of is not triggered.

Figure 13.14: This is the third loop pass. on change of is triggered.

Figure 13.15: Here the auxiliary field is updated.

Figure 13.16: This is the fourth loop pass. on change of is not triggered.

Using on change of

Listing 13.12 illustrates the use of on change of.

Listing 13.12 Using on change of in Two Different Ways: Inside of loop at and Inside of select

1  report ztx1312.
2  tables ztxlfa1.
3  data: begin of it occurs 4,
4            f1 type i,
5            f2,
6            f3 type i,
7            f4,
8            end of it.
9 
10 it-f1 = 1. it-f2 = 'A'. it-f3 = 11. it-f4 = 'W'. append it.
11 it-f1 = 3. it-f2 = 'A'. it-f3 = 22. it-f4 = 'X'. append it.
12 it-f1 = 1. it-f2 = 'A'. it-f3 = 33. it-f4 = 'Y'. append it.
13 it-f1 = 2. it-f2 = 'A'. it-f3 = 44. it-f4 = 'Z'. append it.
14
15 loop at it.
16     on change of it-f2.
17         write: / it-f1, it-f2, it-f3, it-f4.
18         endon.
19     endloop.
20 write: / 'End of loop'.
21
22 * executing the same code again - the aux field still contains 'A'
23 loop at it.
24     at first.
25         write: / 'Looping without a reset...'.
26         endat.
27     on change of it-f2.
28         write: / it-f1, it-f2, it-f3, it-f4.
29     else.
30         write: / 'on change of not triggered for row', sy-tabix.
31         endon.
32     endloop.
33 write: / 'End of loop'.
34
35 *reset the aux field to blanks
36 clear it-f2.
37 on change of it-f2.
38     endon.
39 loop at it.
40     at first.
41         write: / 'Looping after reset...'.
42         endat.
43     on change of it-f2.
44         write: / it-f1, it-f2, it-f3, it-f4.
45         endon.
46     endloop.
47 write: / 'End of loop'.
48 free it.
49
50 select * from ztxlfa1 where land1 = 'US'.
51     on change of ztxlfa1-land1.
52         write: / 'land1=', ztxlfa1-land1.
53         endon.
54     endselect.
55 write: / 'End of select'.
56
57 *executing the same select again without a reset works find
58 select * from ztxlfa1 where land1 = 'US'.
59     on change of ztxlfa1-land1.
60         write: / 'land1=', ztxlfa1-land1.
61         endon.
62     endselect.
63 write: / 'End of select'.

In a 3.0F environment and above, the code in Listing 13.12 produces this output:

        1  A         11  W                 
End of loop                                 
Looping without a reset... 
on change of not triggered for row          2
on change of not triggered for row          3
on change of not triggered for row          4
End of loop                                 
Looping after reset...                      
         1  A         11  W                 
End of loop                                 
land1= US                                   
End of select                               
land1= US                                   
End of select

Lines 10 through 13 seed the internal table with data. it-f2 contains value 'A' in all four rows.
Line 16 writes out each row as it is processed.
Lines 17, 20, 23, and 26 are triggered the first time through the loop. This happens because the auxiliary fields contain zeros or blanks, so they differ from the values in the first row. The first row values are copied to the auxiliary fields and the values are written out by the write statements.
Line 20 is not triggered again because on all subsequent loop passes, the value of it-f2 is 'A' and the global auxiliary field also contains 'A'. Notice that a change in the value to the left of f2 does not trigger on change of as it does with at new.
The internal table is not used after line 31, so here it is freed. This deletes all rows and releases the memory.
Line 33 selects all rows from ztxlfa1 in land1 order.
Although this select returns 23 rows, as shown by the last line of output, only five unique values of land1 exist and are written out.

Using the Hidden Danger in on change of

The first time through a loop, the global auxiliary field is created with initial values (blanks or zeros). If the first value in a loop happens to be blank or zero, on change of will not be triggered. Listing 13.13 illustrates this problem and offers a solution.

Listing 13.13 The First Row of Loop #1 Does Not Trigger on change of

1  report ztx1313.
2  data: begin of it occurs 4,
3            f1 type i,
4            end of it.
5 
6  it-f1 = 0. append it.
7  it-f1 = 3. append it.
8  it-f1 = 1. append it.
9  it-f1 = 2. append it.
10
11 loop at it.                                "loop #1
12     write: / '==new row==', 12 it-f1.
13     on change of it-f1.
14         write: / 'f1 changed:', 12 it-f1.
15         endon.
16     endloop.
17
18 skip.
19 loop at it.                                "loop #2
20     write: / '==new row==', 12 it-f1.
21     on change of it-f1.
22         write: / 'f1 changed:', 12 it-f1.
23     else.
24         if sy-tabix = 1.
25             write: / 'f1 changed:', 12 it-f1.
26             endif.
27         endon.
28     endloop.
29 free it.

The code in Listing 13.13 produces this output:

   ==new row==         0
   ==new row==         3
   f1 changed:         3
   ==new row==         1
   f1 changed:         1
   ==new row==         2
   f1 changed:         2
                        
   ==new row==         0
   f1 changed:         0
   ==new row==         3
   f1 changed:         3
   ==new row==         1
   f1 changed:         1
   ==new row==         2
   f1 changed:         2

Lines 6 though 9 fill the internal table with four rows. The value of it-f1 in the first row is zero.
On line 13, the first loop pass does not trigger the on change of because the global auxiliary field contains zero.
Lines 23 and 24 have been added to loop #2 to detect the error. Notice that at first cannot be used to detect this problem because all numeric fields in the work area will be set to zero inside of at first and endat.

NOTE

In versions prior to 3.0F, only select and get statements automatically reset the global auxiliary fields. However, if you reset them yourself, you can use on change of in any type of loop. Use this code to perform the reset: clear f1. on change of f1. endon. clear moves initial values to f1. on change of compares f1 to the global auxiliary field. If they differ, the empty on change of overwrites it with initial values. Use this to fill the auxiliary fields with any value from f1.

Summary

An array operation is the most efficient way to fill an internal table from a database table. It reads rows from the database table directly into the body of the internal table without using any work areas. It is implemented via the into table addition on the select statement.
The internal table must be wide enough to accommodate the columns you select from the database table. To be precise, the width of an internal table row in bytes must be greater than or equal to the total number of bytes from all columns selected from the database table into the internal table. If the internal table row is wider, the columns selected from the database table fill the left-most bytes of each internal table row. The remaining bytes will be set to default initial values.
Use the into corresponding fields addition if the fields of the internal table are in a different order or are separated from each other by fields not selected from the database table.
Use at and endat to trigger processing based on a change in the column of an internal table or any column to the left of it.
Use sum within at and endat or on change of to calculate totals and subtotals.
Use on change of within loops to trigger processing based on a change in one of the selected columns.

NOTE

Don't use select and append. Whenever possible, use select into table instead.

Don't use order by with select into table. Use sort on the internal table instead.

Q&A

Q	The first part of this chapter seems to place a lot of emphasis on efficiency. I'm just learning and I find all of these methods confusing. Does this really make that much of a difference, or are you splitting hairs?
A	Experience has taught me that students who learn correctly the first time are far better off. ABAP/4 programs often process gargantuan amounts of data, so unfortunately, "easy code" often translates into "overhead overhead overhead." If you only learn easy methods first, they become habits and are difficult to discard. Poor code affects the performance of your programs and of the system.

Workshop

The Workshop provides two ways for you to affirm what you've learned in this chapter. The Quiz section poses questions to help you solidify your understanding of the material covered and the Exercise section provides you with experience in using what you have learned. You can find answers to the quiz questions and exercises in Appendix B, "Answers to Quiz Questions and Exercises."

Quiz

What are the values of the fields to the right of the control level between the at and endat statements?

Exercise 1

Copy program ztx1110 to -e1301. Change it so that if a row is found and the contents of any fields in the found row differ from what is in the header line, overwrite the found row with the current contents of the header line. The output should look like this:

10          1  XX               1   
20          2  XX               2   
30          3  XX               3   
40          4  XX               4   
50          5  XX               5

Hint: Use the comparing addition on the read table statement.

Exercise 2

The following program produces a short dump. Describe the problem, copy the program to -e1302 and fix it.

report zty1302
tables: ztxlfa1.
data: begin of it occurs 10,
          lifnr like ztxlfa1-lifnr,
          land1 like ztxlfa1-land1,
          end of it.                
select * from ztxlfa1 into table it.
loop at it.
    write: / it-lifnr,
             it-land1.
    endloop.

Sams Teach Yourself ABAP/4® in 21 Days

Day 13

Advanced Internal Tables, Part 2

Syntax for the into table Addition of the select Statement

select into table and Sorting

Fields You Select Must Fit into the Internal Table

Using the corresponding fields Addition

Syntax for the at first and at last Statements

Using the Unexpected: Component Values Gone

Syntax for the at new and at end ff Statements

Using at new

Using at end of

Syntax for the sum Statement

Syntax for the on change of Statement

Behind the Scenes of on change of

Using on change of

Using the Hidden Danger in on change of

Sams Teach Yourself ABAP/4^® in 21 Days