Posted: Fri Sep 28, 2007 10:33 am Post subject: Concept of Dynamic Open SQL
Concept of Dynamic Open SQL ABAP/4 SAP
by: Lukman Susanto - 2003
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.
Code:
REPORT YA002DYN001 .
PARAMETERS: TBLNAME(50) DEFAULT 'SPFLI'.
DATA: TOTAL_ROWS TYPE P.
SELECT COUNT(*) FROM (TBLNAME) INTO TOTAL_ROWS. з
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
Code:
REPORT YA002DYN002 .
TABLES: SCUSTOM.
DATA: ITAB_SCUSTOM LIKE SCUSTOM OCCURS 0 WITH HEADER LINE.
DATA: STR_WHERE TYPE TABLE OF EDPLINE.
DATA: STR_LINE TYPE EDPLINE.
PARAMETERS: NAMELIKE(20) TYPE C DEFAULT 'A' OBLIGATORY.
PARAMETERS: OTHERCON(50) TYPE C DEFAULT 'ID BETWEEN 1 AND 99'.
CONCATENATE 'NAME LIKE ''' NAMELIKE '%''' INTO STR_LINE.
IF OTHERCON <> ''.
CONCATENATE STR_LINE 'AND' OTHERCON '' INTO STR_LINE
SEPARATED BY SPACE.
ENDIF.
APPEND STR_LINE TO STR_WHERE.
SELECT * FROM SCUSTOM INTO TABLE ITAB_SCUSTOM WHERE (STR_WHERE).
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 . 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
Code:
*-------------------- LOADING BOOKING SUB ROUTINE --------------------
FORM LOAD_BOOKING USING CRITERIA.
* Refreshing all internal tables
REFRESH ITAB_SBOOK.
REFRESH ITAB_SBOOK_SET.
* Declaring variables to add Dynamic Open SQL ability
DATA: STRWHERE TYPE TABLE OF EDPLINE, STRLINE TYPE EDPLINE.
* Declaring variable to differentiate Business class
DATA: C(1) TYPE C VALUE 'C'.
* Building a string for WHERE statement
CONCATENATE 'CARRID = ''' ITAB_SFLIGHT_SET-CARRID ''''
' AND CONNID = ''' ITAB_SFLIGHT_SET-CONNID ''''
' AND FLDATE = ''' ITAB_SFLIGHT_SET-FLDATE ''''
INTO STRLINE.
APPEND STRLINE TO STRWHERE.
* If the user click Business Class button
IF CRITERIA = 'BUSSONLY'.
CONCATENATE ' AND CLASS = ''' C '''' INTO STRLINE.
APPEND STRLINE TO STRWHERE.
SET PF-STATUS 'BUSSSORTBUTTONS'.
SET TITLEBAR 'BUSSTITLE' WITH SY-UZEIT SY-DATUM.
* If the user click All Class button
ELSE.
SET PF-STATUS 'SORTBUTTONS'.
SET TITLEBAR 'ALLTITLE' WITH SY-UZEIT SY-DATUM.
ENDIF.
* Loading data from database table SBOOK to suit
* the string STRWHERE - Dynamic Open SQL
SELECT * FROM SBOOK INTO TABLE ITAB_SBOOK WHERE (STRWHERE). з
* Loop all record in internal table
LOOP AT ITAB_SBOOK.
ITAB_SBOOK_SET-BOOKID = ITAB_SBOOK-BOOKID.
ITAB_SBOOK_SET-CUSTOMID = ITAB_SBOOK-CUSTOMID.
* Getting Customer Name
SELECT SINGLE * FROM SCUSTOM WHERE ID = ITAB_SBOOK-CUSTOMID.
ITAB_SBOOK_SET-NAME = SCUSTOM-NAME.
* Getting Booking Class
CASE ITAB_SBOOK-CLASS.
WHEN 'F'.
ITAB_SBOOK_SET-CLASS = 'First'.
WHEN 'Y'.
ITAB_SBOOK_SET-CLASS = 'Economy'.
WHEN 'C'.
ITAB_SBOOK_SET-CLASS = 'Business'.
ENDCASE.
ITAB_SBOOK_SET-PRICE = ITAB_SBOOK-LOCCURAM.
ITAB_SBOOK_SET-ORDERDATE = ITAB_SBOOK-ORDER_DATE.
APPEND ITAB_SBOOK_SET.
ENDLOOP.
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/
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG. SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG. Every effort is made to ensure content integrity. Use information on this site at your own risk.
. If the user input an additional condition in the selection screen, the program will also add that information into “STR_LINE” (Line 9 – 11).
