LOGICAL DATA BASES

In general, the system reads data that will appear in a list from the database.

You can use OPEN SQL or NATIVE SQL statements to read data from the database.

The use of a logical database provides you with an alternative to having to program database accesses individually. Logical databases retrieve data records and make them available to ABAP programs.

The same logical database can be the data source for several Quick Views, queries, and programs. In the Quick View, the LDB can be specified directly as a data source. A query works with the logical database when the functional area that generated the query is defined with a logical database. In the case of type 1 programs, the LDB is entered in the attributes or called using function module LDB_PROCESS. See appendix for information on how to use the function module.

Logical databases offer several advantages:

The system generates a selection screen. The use of selection screen versions or variants provides the required flexibility.

The user does not have to know the exact structure of the tables involved (especially the foreign key dependencies); the data is made available in the correct order at GET events.

Performance improvements within logical databases directly affect all programs linked to the logical database, without having to change the programs themselves.

Maintenance can be performed at a central location.

Authorization checks can also be performed centrally.

A logical database is an ABAP program that reads predefined data from the database and makes it available to other programs.

A hierarchical structure determines the order in which the data is supplied to the programs. A logical database also provides a selection screen that checks user entries and conducts error dialogs. These can be extended in programs.

SAP provides some 200 logical databases in Release 4.6. The names of logical databases have been extended to 20 places in Release 4.0 (namespace prefix max. 10 characters).

In the case of executable programs, you can enter a logical database in the attributes.

Use the NODES statement to specify the nodes of the logical database that You want to use in the program. NODES allocates the appropriate storage space for the node - that is, a work area or a table area depending on the node type.

The logical database makes the data records available for the corresponding GET events.
The sequence in which these events are processed is determined by the structure of the logical database.

Logical databases are made up of several sub-objects. The structure determines the hierarchy, and thus the read sequence of the data records.

Node names can contain up to 14 characters. There are four different node types.

Table (type T): The node name is the name of a transparent table (this type corresponds to the concept prior to Release 4.0A). The table name must be identical to the node name. Deep types (complex) are not allowed.

DDIC type (type S): Any node name is possible. It is assigned a structure or a table type from the Dictionary. The node name can differ from the type name. Deep structures are possible.

Type groups (type C): The node type is defined in a type group. The name of the type group must be maintained in the "Type group" field. You should generally prefer DDIC types, as the other applications that use the logical database (such as SAP Query) can access them (short texts, and so on).

Dynamic nodes (type A): These nodes do not have a fixed type; they are not classified until the program runtime. Which types are generally allowed is determined when the structure is created.

Nodes are declared using language element NODES.

Processing blocks are always allocated to an event. A processing block is closed by the next event key word, the start of form routines, or by the end of the program.

The START-OF-SELECTION event is triggered before control is given to the read routine of the logical database. The END-OF-SELECTION event is triggered after all GET events have been processed - that is, all data records have been read and processed.

The GET event is triggered whenever the logical database supplies data for this node. This means that GET events are processed several times, and that data has already been read from the database for these events. The sequence in which the GET events are processed is determined by the structure of the logical database.

The GET LATE event is triggered when all subordinate nodes of node have been processed, before the data is read for the next ; that is, whenever a hierarchy level has been completed.

At the start of the event, the system automatically adds a line feed and configures the default formats (for example, INTENSIFIED ON).

CHECK statements end the current processing block.

STOP statements end program processing. However, in contrast to the EXIT statement, the processing block END-OF-SELECTION is processed first (if it exists).

If there is a STOP statement within the END-OF-SELECTION processing block, program processing ends immediately and a list is displayed.

The EXIT statement exits the program and displays the list.

You can also use the REJECT statement. The data record is not processed further. Processing continues on the same hierarchy level when the next data record is read. REJECT, unlike the CHECK statement, can also be used within a subroutine.

Use the selection include dbsel to define selection screens for logical databases. The addition FOR NODE assigns selections to individual logical nodes. The appearance of a selection screen thus directly depends on the NODES statement contained within your program.

A field selection can be defined for the individual nodes. To do this, you have to specify the addition FIELD SELECTION FOR NODE in the SELECTION-SCREEN statement. You can then use GET FIELDS to restrict the amount of data returned.

You can designate individual nodes for dynamic selection using the addition DYNAMIC SELECTIONS FOR NODE. The Dynamic selection pushbutton then appears on your selection screen. You can determine which selection fields can be set by choosing a particular selection view yourself (type: CUS) or by using the selection view delivered by SAP (type: SAP).

With large logical databases you can define several selection screen versions. Each selection screen version contains a subset of your selection criteria (language element: EXCLUDE). Specify the name of a selection screen version in the program attributes.

When you enter a logical database in the attributes of your type 1 program, the system processes the selection screen of the logical database. The concrete characteristics of the selection screen depend upon the node specified in the NODES statement. If you specify a node of type T (table), you can also declare the table work area with the TABLES statement.

If you address only subordinate nodes (in the hierarchy) of the logical database in the program (for example sflight), the selection screen criteria for the superior node in the hierarchy (spfli) also appear. You can thus restrict the dataset to be read so that it meets your specific requirements.

Note: A logical database always reads in accordance with its structure. This means that if you only need data from a node deep in the hierarchy, you will achieve better performance by programming the access yourself. This avoids unnecessary reading of the database.

If the logical database supports dynamic selections, the pushbutton for Dynamic selections appears on the selection screen. When the user presses this button, a second selection screen is displayed.
This screen allows the user to select additional database fields. The system transfers the selections directly to the logical database program and therefore to the database (dynamic selections).

he selection view determines which fields are displayed on the selection screen. Create your own view with type CUS, and have it override the view with type SAP.

Database program sapdb for logical database is a collection of subroutines, each of which is performed for specific events. For example, subroutine is processed once at the start of the database program. This program can be used to define default values for the selection screen of the LDB.

Other subroutines also exist that are processed during events PBO (Process Before Output) and PAI (Process After Input) of the selection screen. Checks, such as authorization checks (AUTHORITY-CHECK), are usually performed during event PAI.

The database accesses (SELECT statements) are programmed in the put_ subroutines. These subroutines may be processed several times, depending on which selection criteria the user specifies. The sequence in which these subroutines are processed is determined by the structure of the logical database.

Database access (SELECT statements) should be programmed with optimal performance in mind. When creating a logical database you generate the corresponding database program after first having determined its structure and selection attributes. You can find performance tips in the comment lines.

When a program that has been assigned a logical database is started, control is initially passed to the database program of the logical database. Each event has a corresponding subroutine in the database program - for example, subroutine init for event INITIALIZATION. During the interaction between the LDB and the associated program, the subroutine is always processed first, followed by the event (if there is one in the report).

Logical database programs read data from a database according to the structure declared for the logical database. They begin with the root node and then process the individual "branches" consecutively from top to bottom.

The logical database reads the data in the put_ subroutines. During event PUT, control is passed from the database program to the GET event of the associated report.

The data is made available in the corresponding work areas in the report. The processing block defined for the GET event is performed. Control then returns to the logical database.
PUT activates the next form subroutine found in the structure. This flow is continued until the report has collected all the available data.

The depth of data read in the structure depends upon a program's GET events. A logical database reads to the lowest GET event contained within the structure attributes. Only those GET events for which processing is supposed to take place are written into the report program. Logical databases read all data records found on the direct access path.

If you specify a logical database and declare additional selections in the program attributes that refer to the fields of a node not designated for dynamic selection, you must use the CHECK statement to see if the current data record fulfills the selection criteria.

If the data record does not fulfill these selection criteria, current event block processing ends.

PROGRAMMING DATA RETRIVAL

Whenever a logical database cannot supply your program with all necessary data, you must program database access directly into the program itself. This can be done using either Open SQL or Native SQL statements.

Open SQL statements offer several advantages. These include being able to program independent of your underlying database, access to a syntax check, and the use of a local SAP buffer.

Native SQL statements are bound into a program using
EXEC SQL [PERFORMING form.
.
ENDEXEC

Pay attention to the following when programming Native SQL:
Try not to use update operations (INSERT, DELETE, UPDATE)
Group EXEC SQL statements together (in an include) in order to be able to alter them centrally for different database systems
Restrict yourself to Standard SQL 

In order to optimize performance, choose your SQL statements carefully when accessing several (dependent) tables at a time.
To insure optimal database performance:

Follow these general rules:
Keep the amount of selected data as small as possible (use WHERE conditions, for
example)

Keep data transfer between the application server and the database to a minimum (use field lists, for example)

Reduce the number of database inquiries if possible (use table joins instead of nested SELECT statements, for example)

Reduce search size (this optimizes your database index)

Minimize database server load (use SAP buffers, for example).

Always subject programs containing SQL statements to an SQL trace. Which processing sequence is chosen by the Optimizer? Are indices used? If so, are the right ones used?

Is a FULL TABLE SCAN performed? Based on the results of this analysis, you should reprogram your SQL statements (WHERE) conditions, create a database index, or buffer the tables better. To start the SQL trace, use menu path GDA-1.

You can create database views in the ABAP Dictionary. Views (aggregate objects) are application specific and allow you to work with multiple database tables. The link is mapped in an INNER JOIN LOGIC (see slide on INNER JOIN).

From Release 4.0 you can buffer database views. You can then read from views using the SAP buffer on the relevant application server. The same rules apply when buffering views as when buffering tables.

Database view advantages:

Central maintenance
Accessible to all users
Only one SELECT statement is required in the program
One disadvantage of the view is its low flexibility.

In a join, the tables (base tables) are combined to form one results table. The join conditions are applied to this results table. The resulting composite for an inner join logic contains only those records for which matching records exist in each base table.

Join conditions are not limited to key fields.

If columns from two tables have the same name, then you have to ensure that the field labels are unique by prefixing the table name or a table alias.

A table join is generally the most efficient way to read from the database. The database is responsible for deciding which table is read first and which index is used (DB Optimizer).

At LEFT OUTER JOIN, results tables can also contain entries from the designated left hand table without the presence of corresponding data records (join conditions) from the table on the right. These table fields are filled by the database with null values and are then initialized according to ABAP type.

It makes sense to use a LEFT OUTER JOIN when data from the table on the left is needed for which there are no corresponding entries in the table on the right.

The following limitations apply for the Left Outer Join:

you can only have a table or a view to the right of the JOIN operator, you cannot have another join statement
Only AND can be used as a logical operator in an ON condition.
every comparison in the ON condition must contain a field from the table on the right.
if the FROM clause contains an Outer Join, then all ON conditions must contain at least one 'true' JOIN condition (a condition that contains a field from tab1 and a field from tab2).

FOR ALL ENTRIES works with a database in a quantity-oriented manner. Initially all data is collected in an internal table. Make sure that this table contains at least one entry (query sy-subrc or DESCRIBE), otherwise the subsequent transaction will be carried out without any restrictions).

SELECT...FOR ALL ENTRIES IN is treated like a SELECT statement with an external OR condition. The system only selects those table entries that meet the logical condition .
Using FOR ALL ENTRIES is recommended when data is not being read from the database, that is, it is already available in the program, for example, if the user has input the data. Otherwise a join is recommended.

The easiest technical option for reading from multiple (dependent) tables is to use nested SELECT statements. The biggest disadvantage of this method is that for every data record contained in the external loop a SELECT statement is run using the database. This leads to a considerably worse performance in client/server systems.

SAP ABAP DATA FORMATTING AND CONTROL LEVEL PROCESSING

You can use control level processing to create structured lists. Control levels are determined by the contents of the fields that are to be displayed. there is a control level change whenever the content of a field changes. This means that there is no point in creating control levels unless the data are sorted.

The data to be displayed must be saved temporarily if you want to use control level processing. You can also use internal tables and intermediate datasets.

You can use an array fetch in a SELECT statement to fill an internal table in one go.

You can use the APPEND statement to insert table entries at the end of an internal table. The variant of the APPEND statement on the slide is permitted only for standard or sorted tables. After an APPEND statement, system field SY-TABIX contains the index value of the newly inserted table entry.

You use the COLLECT statement to generate unique or compressed datasets. The contents of the work area of the internal table are recorded as a new entry at the end of the table or are added to an existing entry. The latter occurs when the internal table already contains an entry with the same key field values as those currently in the work area. The numeric fields that do not belong to the key are added to the corresponding fields of the existing entry.

When the COLLECT statement is used, all the fields that are not part of the key must be numeric.

The SORT statement sorts the entries in internal table in ascending order. If the addition BY ..., is missing, then the key assigned when the table was defined is used.

If addition BY ... is used, then fields , , ... are used as sort keys. The fields can be of any type.

You can use the additions ASCENDING and DESCENDING with the SORT statement to determine whether the fields are sorted in ascending (default) or descending order.

For more information about the SORT statement, please refer to appendix documentation DAP-3.

You can use the loop statement LOOP AT ... ENDLOOP to process an internal table. The data records in the internal table are processed sequentially.

The CONTINUE statement can be used to prematurely exit the current loop pass and skip to the next pass.

The EXIT statement can be used to exit loop processing.

At the end of loop processing (after ENDLOOP), return value sy-subrc indicates whether the loop was passed or not.

SY-SUBRC = 0: The loop was passed at least once
SY-SUBRC = 4: The loop was not passed because no entry was available.

You can use special control structures for control level processing. All the structures begin with AT and end with ENDAT. These control structures can only be used within a LOOP.

The statement blocks AT FIRST and AT LAST are run exactly once: at the first AT FIRST and at the last AT LAST loop.

The statements within AT NEW ... ENDAT are executed when the value of field changes within the current LOOP (start of a control level) or the value of one of the fields in the table definition (further to the left).

The statements within AT END OF ... ENDAT are executed when the value of field changes during the next LOOP (end of a control level) or the value of one of the fields in the table definition (further to the left).

At entry of the control level (directly after AT), - all fields with the same character types after (to the right of) the current control level key are filled with "*" - all other fields after (to the right of) the current control level key are set to default values.

When a control structure is exited (at ENDAT), all fields of the query area are filled with the data from the current loop pass.

The SUM statement supplies the respective group totals in the query area of the LOOP in all fields of TYPE I, F and P.

The control level structure in internal tables is static. It corresponds exactly to the sequence of columns in the internal table (from left to right). In particular, the control level structure for internal tables is independent of the criteria used to sort the internal table. The table must be sorted according to the internal table fields.

When you implement control level processing, you must follow the sequence of individual control levels within the LOOP as illustrated in the slide. The sequence follows the sequence of fields in the internal table and is therefore also the sort sequence.

The processing block between AT FIRST and ENDAT is executed before processing of the single lines begins. The processing block AT LAST and ENDAT is executed after all single lines have been processed.

ABAP DATA BASE ACCESS FROM UNIX FILE

PROGRAM TO LOAD A DATABASE TABLE FROM A UNIX FILE

report zmjud001 no standard page heading.

tables: z_mver.

parameters: test(60) lower case default '/dir/judit.txt'.
data: begin of unix_intab occurs 100,
field(53),
end of unix_intab.
data: msg(60).

***open the unix file

open dataset test for input in text mode message msg.
if sy-subrc <> 0.
write: / msg.
exit.
endif.

***load the unix file into an internal table

do.
read dataset test into unix_intab.
if sy-subrc ne 0.
exit.
else.
append unix_intab.
endif.
enddo.

close dataset test.

***to process the data. load the database table

loop at unix_intab.
z_mver-mandt = sy-mandt.
z_mver-matnr = unix_intab-field(10).
translate z_mver-matnr to upper case.
z_mver-werks = unix_intab-field+10(4).
translate z_mver-werks to upper case.
z_mver-gjahr = sy-datum(4).
z_mver-perkz = 'M'.

z_mver-mgv01 = unix_intab-field+14(13).
z_mver-mgv02 = unix_intab-field+27(13).
z_mver-mgv03 = unix_intab-field+40(13).
* to check the data on the screen (this is just for checking purpose)
write: / z_mver-mandt, z_mver-matnr, z_mver-werks, z_mver-gjahr,
z_mver-perkz, z_mver-mgv01,
z_mver-mgv02, z_mver-mgv03.

insert z_mver client specified.

*if the data already had been in table z_mver then sy-subrc will not be
*equal with zero. (this can be *interesting for you - (this list is
*not necessary but it maybe useful for you)
if sy-subrc ne 0.
write:/ z_mver-matnr, z_mver-werks.
endif.
endloop.

NOTES:

1. This solution is recommended only if the database table is NOT a standard SAP database table .
2. In the above mentioned unix file record's size is 53 bytes.
Every record in the unix file as the same size:


10 bytes for material
04 bytes for plant
13 bytes for corrected consumption for January
13 bytes for corrected consumption for February
13 bytes for corrected consumption for March
3. Table Z_MVER


This table was created to store the consumption values for first quarter of the year.
Fields Data Element
mandt mandt
matnr matnr
werks werks
gjahr gjahr
perkz perkz
mgv01 mgvbr
mgv02 mgvbr
mgv03 mgvbr