21天学会APAP4的基础 Day13

Day 13Advanced Internal Tables, Part 2∙Chapter Objectives∙Filling an Internal Table from a Database Tableo Selecting Multiple Rows Directly into an Internal Tableo Adding Rows one by one Using selecto Summing Up select, Internal Tables, and Efficiency∙Using the lfa1, lfb1, lfc1, and lfc3 Sample Tables∙Control Break Processingo Using the at first and at last Statementso Using the at new and at end of Statementso Using the sum Statemento Using the on change of Statement∙Summary∙Q&A∙Workshopo Quizo Exercise 1o Exercise 2Chapter ObjectivesAfter 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 TableQuite 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:272∙Selecting multiple rows directly into the body of an internal table∙Selecting single rows into a work area and then appendingSelecting Multiple Rows Directly into an Internal TableTo 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.Syntax for the into table Addition of the select StatementThe following is the syntax for the into table addition of the select statement.(a) select *(b) select f1f2 . . .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.273The 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 tableListing 13.1 This Program Reads Rows from a Database Table Directly into an Internal Table1 report ztx1301.2 tables ztxlfa1.3 data it like ztxlfa1 occurs 23 with header line.45 select * from ztxlfa1 into table it. "don't code an endselect6 loop at it.7 write: / it-lifnr, it-name1.8 endloop.910 select * from ztxlfa1 into table it11 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 Unlimited1010 Industrial Pumps Inc.1020 Chemical Nation Ltd.1030 ChickenFeed Ltd.1050 The Bit Bucket1060 Memory Lane Ltd.1070 Flip My Switch Inc.V10 Duncan's Mouse Inc.V6 Anna Banana Ltd.274275 V8 Smile When You Say That Ltd.V11 Weiner Schnittsel Inc.V12 Saurkrouten1040 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. Theexisting contents of it are discarded before it is filled.select into table and SortingSuppose 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 bethe preferred method because it offloads cycles from the database server to the application server. Fields You Select Must Fit into the Internal TableImagine 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 the276structure 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.277Listings 13.2 and 13.3 illustrate this concept.Listing 13.2 What Happens if Table Structures Differ When Using the Select-into Statement1 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.78 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.1213 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 0V12 DE 14 0V8 DE 03 0278V11 DE 07V12 DE 14V8 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 ztxlfa1begins 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 Table1 report ztx1303.2 tables ztxlfa1.3 data: begin of it occurs 23,4 lifnr like ztxlfa1-lifnr, "this is a char 10 field5 land1 like ztxlfa1-land1, "this is a char 3 field6 end of it.78 *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.279∙Line 3 defines an internal table that has two components. The total length of it is 13 bytes.∙Line 9 selects all fields from ztxlfa1into it. The total length of all of these fields is more than 13 bytes. Consequently, a short dump occurs.Using the corresponding fields AdditionIf 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 internaltable body.280The 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 Database1 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.1213 * This is efficient usage:14 select lifnr land1 from ztxlfa115 into corresponding fields of table it116 where lifnr between 'V10' and 'V12'.17 loop at it1.18 write: / it1-lifnr, it1-land1.19 endloop.2021 * This is inefficient:22 select * from ztxlfa123 into corresponding fields of table it224 where lifnr between 'V10' and 'V12'.25 skip.26 loop at it1.27 write: / it1-lifnr, it1-land1.28 endloop.2928130 * Instead, write:31 select lifnr land1 from ztxlfa1 into table it232 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 CCV11 DEV12 DEV10 CCV11 DEV12 DEV10 CCV11 DEV12 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 ztxlfa1into 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 selectUsing 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.282Alternatively, 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 Tables1 report ztx1305.2 tables ztxlfa1.3 data it like ztxlfa1 occurs 2 with header line.45 *Do it this way6 select * from ztxlfa1 into it "notice 'table' is omitted so the7 where land1 = 'DE'. "row goes into the header line of it8 append it.9 endselect.1011 loop at it.12 write: / it-lifnr, it-land1, it-regio.13 endloop.14 refresh it.1516 *Or this way17 select * from ztxlfa1 "no 'into' so the row goes into the18 where land1 = 'DE'. "default table work area19 append ztxlfa1 to it. "and then is appended to it20 endselect.2122 skip.23 loop at it.24 write: / it-lifnr, it-land1, it-regio.25 endloop.26 refresh it.2728 *Not this way29 select * from ztxlfa1 "row goes into default table work area30 where land1 = 'DE'.31 it = ztxlfa1. "then is assigned to the header line28332 append it.33 endselect.3435 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 07V12 DE 14V8 DE 03V11 DE 07V12 DE 14V8 DE 03V11 DE 07V12 DE 14V8 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.284Listing 13.6 How the corresponding fields Addition to the select Statement Produces the Same Effect as the move-corresponding Statement1 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.89 *Do it this way:10 select lifnr land1 from ztxlfa111 into corresponding fields of it "notice 'table' is omitted12 where land1 = 'DE'.13 append it.14 endselect.1516 loop at it.17 write: / it-lifnr, it-row_id, it-land1.18 endloop.19 refresh it.2021 *Not this way:22 select * from ztxlfa123 where land1 = 'DE'.24 move-corresponding ztxlfa1 to it.25 append it.26 endselect.2728 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 DE285V12 0 DEV8 0 DEV11 0 DEV12 0 DEV8 0 DEOn 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 EfficiencyTable 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.Using the lfa1, lfb1, lfc1, and lfc3 Sample TablesBefore 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 review286the 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 lfb1contains 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. lfb1uses the vendor number and company code. lfc1uses 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.287The set of fields named umNNs, umNNh, and umNNu is repeated 16 times within an lfc1row. 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.Table lfc3contains 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 ProcessingAfter 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 / endat288∙sum∙on change of / endonThe first statement of each of these statement pairs-except forsum-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 StatementsUse 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 StatementsThe 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 could289have two at first and three at last statements within one loop and they can appear inany 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 headingsUse at last for:∙Loop termination processing∙Writing totals at the bottom of a report∙Writing footingsListing 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 Line1 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.29013 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.001000 1000 1996 5,000.001000 1000 1998 4,000.001040 4000 1997 0.001070 2000 1997 1,000.001090 2000 1997 250.50V1 1000 1992 1,000.00V1 3000 1990 1,000.00V1 3000 1994 1,000.00V4 4000 1997 100.00V6 2000 1997 1,000.00V6 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 Gone291Between 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,statement.inside an at or endatListing 13.8 The Contents of the Internal Table Fields Between at and endat1 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.29228 free it.The code in Listing 13.8 produces this output:Before 'at first':1000 1000 1990 A 1,000.00 500.00Inside 'at first':********** **** **** * 0.00 0.00Between 'at first' and 'at last':1000 1000 1990 A 1,000.00 500.00Inside 'at last':********** **** **** * 0.00 0.00After '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 StatementsUse 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 StatementsThe following is the syntax for the at new and at end of statements.sort by c.loop at it.293---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 newEach 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 arebehaves in a similar fashion.all executed. at end ofA 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.294。