Concept of Dynamic Open SQL ABAP/4 SAP

by: Lukman Susanto - 2003

 

Concepts of Dynamic Open SQL.. 2

Dynamic Open SQL Commands. 2

Dynamic Open SQL – FROM statement 3

Dynamic Open SQL – WHERE statement 4

Dynamic Open SQL – within SUB ROUTINE. 7

References. 9

 

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).

 

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 (Adrian G๖rler & Ulrich Koch, 2001).

Dynamic Open SQL – FROM statement

Here is an example of ABAP program which will count number of records in a table defined by the user during runtime.

Text Box: 1 REPORT YA002DYN001 . 2 PARAMETERS: TBLNAME(50) DEFAULT 'SPFLI'. 3 DATA: TOTAL_ROWS TYPE P. 4 SELECT COUNT(*) FROM (TBLNAME) INTO TOTAL_ROWS. ็ 5 WRITE: / TBLNAME, TOTAL_ROWS.
 

 

 

 

 


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).

 

 

Dynamic Open SQL – WHERE statement

Text Box: 1 REPORT YA002DYN002 . 2 TABLES: SCUSTOM. 3 DATA: ITAB_SCUSTOM LIKE SCUSTOM OCCURS 0 WITH HEADER LINE. 4 DATA: STR_WHERE TYPE TABLE OF EDPLINE. 5 DATA: STR_LINE TYPE EDPLINE. 6 PARAMETERS: NAMELIKE(20) TYPE C DEFAULT 'A' OBLIGATORY. 7 PARAMETERS: OTHERCON(50) TYPE C DEFAULT 'ID BETWEEN 1 AND 99'. 8 CONCATENATE 'NAME LIKE ''' NAMELIKE '%''' INTO STR_LINE. 9 IF OTHERCON <> ''. 10 CONCATENATE STR_LINE 'AND' OTHERCON '' INTO STR_LINE 11 SEPARATED BY SPACE. 12 ENDIF. 13 APPEND STR_LINE TO STR_WHERE. 14 SELECT * FROM SCUSTOM INTO TABLE ITAB_SCUSTOM WHERE (STR_WHERE). ็ 15 WRITE: / 'CUST ID', 11 'CUSTOMER NAME', 40 'STREET ADDRESS', 59 'CITY', 75 'COUNTRY'. 16 LOOP AT ITAB_SCUSTOM. 17 WRITE: / ITAB_SCUSTOM-ID, 11 ITAB_SCUSTOM-NAME, 40 ITAB_SCUSTOM-STREET, 59 ITAB_SCUSTOM-CITY, 75 ITAB_SCUSTOM-COUNTRY. 18 ENDLOOP.
 

 

 

 

 

 

 

 

 

 

 

 

 

 


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.

 

 

 

 

Dynamic Open SQL – within SUB ROUTINE

Text Box: *-------------------- LOADING BOOKING SUB ROUTINE -------------------- 1 FORM LOAD_BOOKING USING CRITERIA. * Refreshing all internal tables 2 REFRESH ITAB_SBOOK. 3 REFRESH ITAB_SBOOK_SET. * Declaring variables to add Dynamic Open SQL ability 4 DATA: STRWHERE TYPE TABLE OF EDPLINE, STRLINE TYPE EDPLINE. * Declaring variable to differentiate Business class 5 DATA: C(1) TYPE C VALUE 'C'. * Building a string for WHERE statement 6 CONCATENATE 'CARRID = ''' ITAB_SFLIGHT_SET-CARRID '''' ' AND CONNID = ''' ITAB_SFLIGHT_SET-CONNID '''' ' AND FLDATE = ''' ITAB_SFLIGHT_SET-FLDATE '''' INTO STRLINE. 7 APPEND STRLINE TO STRWHERE. * If the user click Business Class button 8 IF CRITERIA = 'BUSSONLY'. 9 CONCATENATE ' AND CLASS = ''' C '''' INTO STRLINE. 10 APPEND STRLINE TO STRWHERE. 11 SET PF-STATUS 'BUSSSORTBUTTONS'. 12 SET TITLEBAR 'BUSSTITLE' WITH SY-UZEIT SY-DATUM. * If the user click All Class button 13 ELSE. 14 SET PF-STATUS 'SORTBUTTONS'. 15 SET TITLEBAR 'ALLTITLE' WITH SY-UZEIT SY-DATUM. 16 ENDIF. * Loading data from database table SBOOK to suit * the string STRWHERE - Dynamic Open SQL 17 SELECT * FROM SBOOK INTO TABLE ITAB_SBOOK WHERE (STRWHERE). ็ * Loop all record in internal table 18 LOOP AT ITAB_SBOOK. 19 ITAB_SBOOK_SET-BOOKID = ITAB_SBOOK-BOOKID. 20 ITAB_SBOOK_SET-CUSTOMID = ITAB_SBOOK-CUSTOMID. * Getting Customer Name 21 SELECT SINGLE * FROM SCUSTOM WHERE ID = ITAB_SBOOK-CUSTOMID. 22 ITAB_SBOOK_SET-NAME = SCUSTOM-NAME. * Getting Booking Class 23 CASE ITAB_SBOOK-CLASS. 24 WHEN 'F'. 25 ITAB_SBOOK_SET-CLASS = 'First'. 26 WHEN 'Y'. 27 ITAB_SBOOK_SET-CLASS = 'Economy'. 28 WHEN 'C'. 29 ITAB_SBOOK_SET-CLASS = 'Business'. 30 ENDCASE. 31 ITAB_SBOOK_SET-PRICE = ITAB_SBOOK-LOCCURAM. 32 ITAB_SBOOK_SET-ORDERDATE = ITAB_SBOOK-ORDER_DATE. 33 APPEND ITAB_SBOOK_SET. 34 ENDLOOP. 35 ENDFORM.
 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


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.

 

References

  • Enhanced ABAP Programming with Dynamic Open SQL

            Adrian G๖rler & Ulrich Koch, 2001

 

  • Dynamic Open SQL Commands

            Roger Hayen - Central Michigan University, 1999

            http://sap.mis.cmich.edu/sap-abap/abap06/

 

Lukman's WWW Groups

copyright © 2002 - 2003 Lukman Susanto

http://www.eastwoodfurniture.com.au
http://www.awesomefurniture.com
http://www.majawana.com.au