Dynamic Open SQL – within SUB ROUTINE

Above program is an example of Dynamic Open SQL used within a sub-routine. In a complex program which requires WHERE statement construction throughout the codes, the program will append dynamically various information into “STRWHERE” and “STRLINE”. As a part of another sub-routines and codes, this program will generate output dynamically based on what button the user click. In this example, if the user click “All Class” the program will display all booking list while if the user click “Business Class” the program will only display all booking in Business class.
The ability of Dynamic Open SQL is far more than just what explained in this paper, however using these simple examples we understand that there is always way to develop our current programming method or system.

read more...

Above program is featuring more complex programming techniques. If in the previous program the codes only use one parameter as Dynamic component, in above program the code must implement some additional features to allow the program runs dynamically.

This program will allow the user to search a particular customer name and one other search condition which will be executed during runtime. This program will then display all the records from table customer SCUSTOM which matches with the conditions defined by the user.

Apart from the usage of Internal Table, the program will also require the usage of Table EDPLINE. This is a special feature in Release 4.6 in its pre-release to allow the construction of string or characters which later will be used within the SELECT statement.

As any other program, the first requirement is variable and table declaration. This program will be using one internal table to keep all the data “ITAB_SCUSTOM” (Line 3) and one constructible WHERE statement built on a TABLE OF EDPLINE and EDPLINE (Line 4 and 5). EDPLINE will handle all small pieces or components of WHERE statement and the TABLE OF EDPLINE will construct all the pieces or components from EDPLINE into a WHERE string statement which will be used in the WHERE condition in SELECT statement.

For the selection screen, the program will allow the user to input a particular name LIKE which means any number of first character(s) which the user want to search on. And also, the program will allow one other optional selection condition from the user (Line 6 and 7). In this example the default is “A” for the name and “ID BETWEEN 1 and 99” as the other condition.


Once the user click “Execute” or F8 button, the program will start allocating information from the selection screen into EDPLINE which in this example called “STR_LINE” (Line 8). If the user input an additional condition in the selection screen, the program will also add that information into “STR_LINE” (Line 9 – 11).

After all the conditions constructed, the information in “STR_LINE” will be appended into “STR_WHERE” as the “TABLE OF EDPLINE” (Line 13) which will be used in the SELECT statement (Line 14).

Using that dynamically constructed SELECT statement, the program will display the matching records available in table customer SCUSTOM.

read more...

Dynamic Open SQL – FROM statement

This is a very simple example to show how Dynamic Open SQL can be implemented within the program, specifically in SELECT statement. In this example the program will calculate the number of records from the table which the user inputted. This program is far from perfect and no exception or error handling has been implemented.


The table name that the user inputted will be kept in a variable or parameter called “TBLNAME” (Line 2). During runtime, the program will display selection screen which allows the user to input a table name. As the default, the program will automatically input “SPFLI” as the table name.


After the user pressed “Execute” or F8 button, the value of “SPFLI” will be kept in a variable called “TBLNAME” which will be used to determine the table name of the SELECT statement. In this program, the SELECT statement will COUNT how many records are in the table (Line 4).

read more...

Dynamic Open SQL Commands

The basic of Dynamic Open SQL commands is the usage of variables or parameters within the SELECT statement. This can be done by carefully constructing its components such as FROM, INTO and WHERE statements. Even though Dynamic Open SQL has been developed since Release 3.0, the major development with enhanced features will only be available in Release 6.10. This release includes features such as:

* Creating internal tables dynamically
* Simplify the programming by allowing more transparent and integrated codes
* Additional exception handling within the codes.

Even though some of the essential commands will not available until 6.10 released, the basic features of Dynamic Open SQL (Release 4.6) has been considered as a major development on common Open SQL.

read more...

Concepts of Dynamic Open SQL

Open SQL is the main feature in ABAP language which enables the user to access database level from ABAP workbench editor. Open SQL is an independent variant / subset of standard SQL with some additional SAP unique features which is built into ABAP in SAP application. SQL is always identic with “SELECT” statement; code which capable to select a particular data from a certain tables with some possible conditions. Apart from “SELECT” statement, Open SQL also enables a program to INSERT, UPDATE, MODIFY or DELETE data from physical database in database layer (Adrian Görler & Ulrich Koch, 2001).

Even though Open SQL is seems powerful enough to handle all sort of database transaction, a common Open SQL is unable to dynamically bring a result which require user control over the code during runtime. In some cases, developing a program for such purposes using common Open SQL can be a nightmare as the programmer must pre-code all the possible action received from the user. Using Dynamic Open SQL, the programmer only needs to develop one program which responds variously to different actions from the user (Roger Hayen - Central Michigan University, 1999).

read more...