Wednesday, June 10, 2009

Open SQL

Open SQL consists of a set of ABAP statements that perform operations on the central database in the R/3 System. The results of the operations and any error messages are independent of the database system in use. Open SQL thus provides a uniform syntax and semantics for all of the database systems supported by SAP. ABAP programs that only use Open SQL statements will work in any R/3 System, regardless of the database system in use. Open SQL statements can only work with database tables that have been created in the ABAP Dictionary.

In the ABAP Dictionary, you can combine columns of different database tables to a database view (or view for short). In Open SQL statements, views are handled in exactly the same way as database tables. Any references to database tables in the following sections can equally apply to views.

Overview

Open SQL contains the following keywords:

Keyword

Function

SELECT

Reads data from database tables

INSERT

Adds lines to database tables

UPDATE

Changes the contents of lines of database tables

MODIFY

Inserts lines into database tables or changes the contents of existing lines

DELETE

Deletes lines from database tables

OPEN CURSOR,
FETCH,
CLOSE CURSOR

Reads lines of database tables using the cursor

Return Codes

All Open SQL statements fill the following two system fields with return codes:

  • SY-SUBRC

After every Open SQL statement, the system field SY-SUBRC contains the value 0 if the operation was successful, a value other than 0 if not.

  • SY-DBCNT

After an open SQL statement, the system field SY-DBCNT contains the number of database lines processed.

Client Handling

A single R/3 System can manage the application data for several separate areas of a business (for example, branches). Each of these commercially separate areas in the R/3 System is called a client, and has a number. When a user logs onto an R/3 System, they specify a client. The first column in the structure of every database table containing application data is the client field (MANDT, from the German word for client). It is also the first field of the table key. Only universal system tables are client-independent, and do not contain a client name.

By default, Open SQL statements use automatic client handling. Statements that access client-dependent application tables only use the data from the current client. You cannot specify a condition for the client field in the WHERE clause of an Open SQL statement. If you do so, the system will either return an error during the syntax check or a runtime error will occur. You cannot overwrite the MANDT field of a database using Open SQL statements. If you specify a different client in a work area, the ABAP runtime environment automatically overwrites it with the current one before processing the Open SQL statement further.

Should you need to specify the client specifically in an Open SQL statement, use the addition

... CLIENT SPECIFIED ....

directly after the name of the database table. This addition disables the automatic client handling and you can use the field MANDT both in the WHERE clause and in a table work area.

No comments:

Blog Archive