IBM Global Services
Retrieving Information From the Database
Data
DB
Slide 1
Copyright IBM Corporation 2003
IBM Global Services
The SAP three-tier client/server architecture
Slide 2
Copyright IBM Corporation 2003
IBM Global Services
Open SQL
Open SQL - a subset of standard SQL
Portable across various databases
Slide 3
Copyright IBM Corporation 2003
IBM Global Services
Open SQL
Set of ABAP statements that perform operations on the central database in the R/3 System. Provides a uniform syntax and semantics for all of the database systems supported by SAP. Views are handled in exactly the same way as database tables.
Slide 4
Copyright IBM Corporation 2003
IBM Global Services
Database Access I
Physical Database
Logical Database
REPORT: ZRACER01. TABLES: LFA1, LFB1, LFC3. GET LFA1. GET LFB1.
Authorisation Checking
No Authorisation Checking
GET LFB3.
ABAP Open SQL ABAP Native SQL
Slide 5
Copyright IBM Corporation 2003
IBM Global Services
Keywords
SELECT INSERT MODIFY DELETE Reads data from database tables Adds lines to database tables
UPDATE Changes the contents of lines of database tables Inserts lines into database tables or changes existing lines Deletes lines from database tables
OPEN CURSOR, FETCH, CLOSE CURSOR Reads lines of database tables using the cursor
Slide 6
Copyright IBM Corporation 2003
IBM Global Services
Relevant System Variables
sy-subrc becomes 0 for successful SQL statements or 4 for unsuccessful ones . sy-dbcnt contains the number of records affected .
Client Handling
Open SQL statements use automatic client handling by using the data from the current client of client dependent tables . Data from other clients cannot be fetched by specifying client code in WHERE clause . For that the addition... CLIENT SPECIFIED .is used 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.
Slide 7
Copyright IBM Corporation 2003
IBM Global Services
Reading Records from Database Tables
SELECT <result> INTO <target> FROM <source> [WHERE <condition>] [GROUP BY <fields>] [HAVING <cond>] [ORDER BY <fields>].
Slide 8
Copyright IBM Corporation 2003
IBM Global Services
Basic Significance
SELECT statement specifies columns and rows to be selected . It can also select values for all columns using SELECT * FROM .. And can also retrieve all information in a table omitting the WHERE clause. It can select single/few rows/all rows from database table depending on the SEARCH criteria , WHERE . The result of the selection is either an elementary field or a flat structure or an internal table or table work area, depending on the number of columns you specified in <cols> and number of records fetched.
Slide 9
Copyright IBM Corporation 2003
IBM Global Services
Reading a Single Line
To read a single entry from the database, the syntax is :
SELECT SINGLE <cols> ... WHERE ...
To ensure that the line can be uniquely identified, the values for all of the fields of the primary key of the table in the WHERE clause must be specified. Else, the syntax check produces a warning, and the SELECT statement reads the first entry that it finds that matches the key fields that have been specified.
Slide 10
Copyright IBM Corporation 2003
IBM Global Services
Eliminating Inconsistency
SELECT [SINGLE [FOR UPDATE] .
result of a SELECT statement is itself a table
SINGLE would retrieve only one entry when entire primary key is provided
FOR UPDATE protects the selected entry against simultaneous updates until next commit, have to specify the primary key or returns empty with a sy-subrc = 8
DISTINCT would automatically delete duplicate entries The columns of the result set will have exactly the same sequence, names as the fields of the database table
Slide 11
Copyright IBM Corporation 2003
IBM Global Services
Example - Use of SELECT SINGLE
REPORT . YSUBDEL0
tables : mara .
SELECT SINGLE * FROM MARA WHERE MATNR = 'TRNG'. IF sy-subrc eq 0. write:/5 mara-matnr. ENDIF.
Slide 12
Copyright IBM Corporation 2003
IBM Global Services
Reading Multiple Lines
The following syntax is used to read several records from the database : SELECT [DISTINCT] <cols> ... WHERE ... DISTINCT clause eliminates duplicate entries as per the SEARCH criteria .
The result of the selection is a table. The target area of the INTO clause can be an internal table with a line type appropriate for <cols>. If the target area is not an internal table, but a flat structure, an ENDSELECT statement must be included after the SELECT statement.
(SELECTENDSELECT statements can be nested , but is not recommendable for performance reasons .)
Slide 13
Copyright IBM Corporation 2003
IBM Global Services
System Variables for SELECT Statement SELECT is an OPEN SQL statement. Result table contains at least 1 record: Sy-Subrc = 0 Result table is empty: Sy-Subrc = 4 Exception - Select count(*) Fromstatement If no records found, Result table contains 1 line with the result 0 Sy-Subrc = 4, Sy-Dbcnt = 0 The system field SY-DBCNT contains the number of lines read by SELECT
Slide 14
Copyright IBM Corporation 2003
IBM Global Services
Example : SELECT---ENDSELECT
REPORT . YSUBDEL0
tables : mara . SELECT * FROM MARA WHERE MTART = 'ROH'. IF sy-subrc eq 0. write:/5 mara-matnr. ENDIF.
ENDSELECT.
skip 4. write:/5 sy-dbcnt , 'Records Selected'.
Slide 15
Copyright IBM Corporation 2003
IBM Global Services
Example - Selecting Data from Different Client : CLIENT SPECIFIED
REPORT YSUBDEL0 .
tables : mara . SELECT * FROM MARA CLIENT SPECIFIED WHERE MTART = 'ROH' AND MANDT = '777'. IF sy-subrc eq 0. write:/5 mara-matnr. ENDIF. ENDSELECT. skip 4. write:/5 sy-dbcnt , 'Records Selected'.
Slide 16
Copyright IBM Corporation 2003
IBM Global Services
Specifying Columns
To read from specific columns from the database table, use the following:
SELECT <s1> [AS <a 1>] <s 2> [AS <a 2>] ...
OR ,
SELECT <col1> <col2> into (a1,a2)
Slide 17
Copyright IBM Corporation 2003
IBM Global Services
Example Specifying Columns in SELECT Statement
REPORT . YSUBDEL0
tables : mara .
SELECT MATNR MEINS FROM MARA
INTO (MARA-MATNR,MARA-MEINS) WHERE MTART = 'ROH' AND NOT MEINS EQ SPACE.
IF sy-subrc eq 0.
write:/5 mara-matnr, mara-meins. ENDIF. ENDSELECT.
Slide 18
Copyright IBM Corporation 2003
IBM Global Services
Example Specifying Columns using different names in SELECT Statement
REPORT YSUBDEL0 .
tables : mara .
SELECT MATNR as MATERIAL MEINS as UNIT FROM MARA INTO (MARA-MATNR,MARA-MEINS) WHERE MTART = 'ROH' AND NOT MEINS EQ SPACE. IF sy-subrc eq 0. write:/5 mara-matnr, mara-meins. ENDIF. ENDSELECT.
Slide 19
Copyright IBM Corporation 2003
IBM Global Services
Selecting Data into Structure
SelectEndselect loop is mandatory to fetch more than one records into a structure . INTO CORRESPONDING clause is used to map the selected columns and components of a structure .
Slide 20
Copyright IBM Corporation 2003
IBM Global Services
INTO Clause
... INTO CORRESPONDING FIELDS OF wa Each field of the result set is transported to the field of the same name in wa. If no such field exists, this is ignored, and NO runtime error occurs ... INTO (f1, ..., fn) Places the result set in the target area (f1, ..., fn) from left to right is allowed only if a list of elements is specified in the Select clause For runtime-critical applications use this option as compared to INTO CORRESPONDING FIELDS
Slide 21
Copyright IBM Corporation 2003
IBM Global Services
Example SELECT into structure with CORRESPONDING FIELDS OF
REPORT YSUBDEL0 . id(4) type c ,
data : begin of x_mat,
matnr
meins
like mara-matnr ,
like mara-meins ,
end of x_mat . SELECT matnr meins FROM mara INTO CORRESPONDING FIELDS OF x_mat WHERE mtart = 'ROH'. IF sy-subrc eq 0. WRITE:/5 x_mat-matnr , x_mat-meins. ENDIF. ENDSELECT.
Slide 22
Copyright IBM Corporation 2003
IBM Global Services
Selecting Records into Internal Table
Filling internal table with Records Selected :SELECT <col1> <col2> . INTO CORRESPONDING FIELDS OF TABLE <INTTABLE> .
Appending the Selected Records with Pre Existing Contents of an Internal table :
SELECT <col1> <col2> . APPENDING CORRESPONDING FIELDS OF TABLE <INTTABLE> .
Table addition is mandatory to specify selection into internal table .Otherwise , internal table header line will be used .
SELECT.ENDSELECT block is not required if record is selected into internal table body (I.e., TABLE clause is used ) .
Slide 23
Copyright IBM Corporation 2003
IBM Global Services
Inserting records into Internal tableINTO vs APPENDING
Slide 24
Copyright IBM Corporation 2003
IBM Global Services
Using Package Size
Reading selected rows into an internal table in packages of a predefined size is possible using the PACKAGE SIZE <n> option with the INTO clause. The SELECT statement with the PACKAGE SIZE option initiates a loop which is terminated with an ENDSELECT statement. For every package of <n> rows the system makes one pass through the loop. Outside the SELECT loop, the contents of the internal table are unknown. Therefore, any processing on the rows that is to be performed, must be done inside the SELECT ENDSELECT loop.
Slide 25
Copyright IBM Corporation 2003
IBM Global Services
INTO Clause
Performance If selected data to be evaluated only once, use wa. Reading internal tables would incur additional costs and would use more memory space Read the data into an internal table in a single operation than to read it line by line in a SELECT loop and then use APPEND to append the data to a internal table INTO CORRESPONDING FIELDshould only be used when retrieving large volumes of data otherwise the time required to compare the field names would be too high.
Slide 26
Copyright IBM Corporation 2003
IBM Global Services
Aggregate Functions in SQL
SELECT <lines> <agg>( [DISTINCT] <s1> ) [AS <a 1>] <agg>( [DISTINCT] <s2> ) [AS <a 2>] ...
Aggregate Functions Allowed :
SUM , DISTINCT ,COUNT,MAX,MIN,AVG . Some aggregate functions can also be nested , such as : COUNT(DISTINCT(empcode)) .
Slide 27
Copyright IBM Corporation 2003
IBM Global Services
Having Clause Syntax/Example
Basic form
... HAVING condition.
Used in a SELECT command, it allows you to use a logical condition for the groups in a GROUP BY clause.
SELECT carrid connid COUNT( *) SUM( luggweight ) INTO (carrid, connid, count, sum_weight) FROM sbook WHERE carrid = 'LH' GROUP BY carrid connid HAVING SUM( luggweight ) > 25. WRITE:/ carrid, connid, count, sum_weight. ENDSELECT.
Slide 28
Copyright IBM Corporation 2003
IBM Global Services
Specifying Columns Dynamically
SELECT <lines> (<itab>) ...
The parentheses must include the name of an internal table <itab> that is either empty or contains <s 1 > <s 2 > ... to specify the columns or aggregate expressions to be read. For this purpose, the line type of <itab> must be a type C field with a maximum length of 72. If the internal table is empty, the system reads all columns.
Slide 29
Copyright IBM Corporation 2003
IBM Global Services
Example - Specifying Columns Dynamically
Slide 30
Copyright IBM Corporation 2003
IBM Global Services
Specifying Database Tables Dynamically
To specify the name of a database table dynamically, use the following:
SELECT ... FROM (<name>) <options> ...
The name of the database table must be specified in the variable content , <name> . This variable should be a character variable, with sufficient length to contain the name of the table.
You
can only specify the database table name at runtime if you specify an INTO clause at the same time.
Name
of the database table should always be entered in uppercase
Slide 31
Copyright IBM Corporation 2003
IBM Global Services
Example - Specifying Table name Dynamically
Slide 32
Copyright IBM Corporation 2003
IBM Global Services
Selection from Buffered Table
If buffering is allowed for a table in the ABAP Dictionary, the SELECT statement always reads the data from the buffer in the database interface of the current application server. To read data directly from the database table instead of from the buffer, the following syntax is used : SELECT ... FROM <tables> BYPASSING BUFFER ... This addition guarantees that the data you read is the most up to date.
Slide 33
Copyright IBM Corporation 2003
IBM Global Services
Restricting number of Records to be selected
To restrict the absolute number of lines included in the selection : SELECT ... FROM <tables> UP TO <n> ROWS ... If <n> is a positive integer, the system reads a maximum of <n> lines. If <n> is zero, the system reads all lines that meet the selection criteria.
If ORDER BY clause is used, the system reads all lines belonging to the selection, sorts them, and then places the first <n> lines in the selection set.
Slide 34
Copyright IBM Corporation 2003
IBM Global Services
Example Selecting few records
Slide 35
Copyright IBM Corporation 2003
IBM Global Services
Comparison Operators in WHERE Clause
Symbol Literal Significance
=
< > , >< >= <= > <
EQ
NE GE LE GT LT
Equals to
Not equal To Greater than or equal to Lesser than or equal to Greater Than Lesser than
Slide 36
Copyright IBM Corporation 2003
IBM Global Services
Comparison Operators in WHERE Clause
Values in Intervals
To find out whether the value of a column lies within a particular interval :-
SELECT ... WHERE <s> [NOT ] BETWEEN <f 1> AND <f 2> ...
Comparing Strings
To find out whether the value of a column matches a pattern:-
SELECT ... WHERE <s> [NOT ] LIKE <f> [ESCAPE <h>] ...
Following wildcard characters in <f> can be used : % for a sequence of any characters (including spaces).
_ for a single character.
Slide 37
Copyright IBM Corporation 2003
IBM Global Services
Use of Escape Options
IF two wildcard characters are used explicitly in the comparison, ESCAPE option is used . ESCAPE <h> specifies an escape symbol <h>. If preceded by <h>, the wildcards and the escape symbol itself lose their usual function within the pattern <f>. For example :SELECT * FROM MAKT WHERE MAKTG LIKE ROME#_%' ESCAPE '#'. This condition is true if the contents of the column MAKT begin with ROME_.
Slide 38
Copyright IBM Corporation 2003
IBM Global Services
Checking from List of Values and null values
Checking Lists of Values
To find out whether the value of a column is contained in a list of values : SELECT ... WHERE <s> [NOT ] IN (<f 1>, ......, <f n>) ...
The condition is true if the value of column <s> is [not] in the list <f1> ... <f n>.
Checking for Null Values
To find out whether the value of a column is null, use: SELECT ... WHERE <s> IS [NOT ] NULL ...
Slide 39
Copyright IBM Corporation 2003
IBM Global Services
IN: Example of a Range/Select-option
tables: lfa1. This internal table is the same one data: begin of tab occurs 10. that is created automatically when sign (1), option (2), a selection screen is processed. low like lfa1-lifnr, The RANGES statement builds the high like lfa1-lifnr, structure of the table automatically. end of tab. move: I to tab-sign, BT to tab-option, RANGES <name> FOR <field>. 0000000001 to tab-low, 9999999999 to tab-high. DATA: BEGIN OF <name> append tab. OCCURS <n>, select from lfa1 tables: lfa1. SIGN(1), where lifnr in tab. Ranges: tab for lfa1-lifnr. OPTION(2), write: / lfa1-lifnr, lfa1-name1. move: I to tab-sign, LOW LIKE <field>, endselect. BT to tab-option, HIGH LIKE <field>, 0000000001 to tab-low, END OF <name>. 9999999999 to tab-high. append tab. select* from lfa1 where lifnr in tab. write: / lfa1-lifnr, lfa1-name1. endselect.
Slide 40
Copyright IBM Corporation 2003
IBM Global Services
Negative Conditions
To negate the result of a condition, use: SELECT ... WHERE NOT <cond> ... Checking Subqueries To find out whether the value of a column is contained in a scalar subquery : SELECT ... WHERE <s> [NOT ] IN <subquery>
To find out whether the selection of a subquery contains lines at all :
SELECT ... WHERE [ NOT ] EXISTS <subquery> ...
Slide 41
Copyright IBM Corporation 2003
IBM Global Services
Dynamic WHERE clause
To specify a condition dynamically, use: SELECT ... WHERE (<itab>) ...
where <itab> is an internal table with line type C and maximum length 72 characters. All of the conditions listed above except for selection tables, can be written into the lines of <itab>.
To specify a part of the condition dynamically, use:
SELECT ... WHERE <cond> AND (<itab>) ...
Slide 42
Copyright IBM Corporation 2003
IBM Global Services
Example : The Dynamic WHERE Clause
REPORT Y170DM74. TABLES: TABNA. PARAMETERS: WHERECL1(72) DEFAULT COUNTRY = USA , WHERECL2(3) DEFAULT OR, WHERECL3(72) DEFAULT COUNTRY = GB . TYPE: BEGIN OF ITAB_RECORD, TEXT(72), END OF ITAB_RECORD. DATA: WHERE_ITAB TYPE STANDARD TABLE OF ITAB_RECORD INITIAL SIZE 3 WITH HEADER LINE.
The parameters entered make up the contents of the WHERE clause. The user has the option of changing the conditions and dynamically effecting which way the program will execute.
WHERE_ITAB-TEXT = WHERECL1. APPEND WHERE_ITAB. WHERE_ITAB-TEXT = WHERECL2. APPEND WHERE_ITAB. WHERE_ITAB-TEXT = WHERECL3. APPEND WHERE_ITAB. SELECT * FROM TABNA WHERE (WHERE_ITAB). WRITE: / TABNA-COUNTRY, TABNA-ID. ENDSELECT.
Slide 43
An internal table is created which holds the WHERE clause until it is used in the SELECT statement.
Copyright IBM Corporation 2003
IBM Global Services
Example : Dynamic WHERE Clause
REPORT Y170DM74. TABLES: TABNA. PARAMETERS: WHERECL1(72) DEFAULT COUNTRY = USA , WHERECL2(3) DEFAULT OR, WHERECL3(72) DEFAULT COUNTRY = GB .
CONCATENATE <source field 1> <source field 2> <source field 3> : : <source field n> <target field> <constant>.
TYPE: BEGIN OF ITAB_RECORD, TEXT(72), END OF ITAB_RECORD.
DATA: WHERE_ITAB TYPE STANDARD TABLE OF ITAB_RECORD INITIAL SIZE 3 WITH HEADER LINE.
INTO SEPARATED BY
CONCATENATE WHERECL1 WHERECL2 WHERECL3 INTO WHERE_ITAB-TEXT SEPARATED BY SPACE.
APPEND WHERE_ITAB. SELECT * FROM TABNA WHERE (WHERE_ITAB). WRITE: / TABNA-COUNTRY, TABNA-ID. ENDSELECT.
Slide 44
Copyright IBM Corporation 2003
IBM Global Services
FOR ALL ENTRIES IN addition
The WHERE clause of the SELECT statement has a special variant that allows you to derive conditions from the lines and columns of an internal table: SELECT ... FOR ALL ENTRIES IN <itab> WHERE <cond> ... If the internal table <itab> is empty, all the records in the database table gets selected. So, please check the internal table <itab> to ensure that it contains records before using it in SELECT statement.
Comparison
operators like BETWEEN, LIKE, or IN expression cannot be used in the WHERE clause
Order
by f1..fn cannot be used in the ORDER BY clause
Slide 45
Copyright IBM Corporation 2003
IBM Global Services
Information Systems Platform Programme
WARNINGS while using FOR ALL ENTRIES
WARNING #1: FOR ALL ENTRIES retrieves a unique result set so ensure you retrieve the full key from the database
WARNING #2: Beware if you have a large table, or a large range, the resulting SQL statement can be extremely large, and take the database server a long time to build. If the ABAP program causes the build of a larger-than-allowed SQL statement , it may crash
Slide 46
Copyright IBM Corporation 2003
IBM Global Services
WARNINGS while using FOR ALL ENTRIES
WARNING #3: CHECK that the internal table used in FOR ALL ENTRIES is NOT empty, this will retrieve all entries from the table. In order to avoid this, use the DESCRIBE TABLE.
WARNING #4: Database Hint should be used for SELECT statements having FOR ALL ENTRIES, if the proper index is not being used. (This is applicable if the Database is Oracle 8).
Slide 47
Copyright IBM Corporation 2003
IBM Global Services
Example : Using an Internal Table in SELECT Clause
Slide 48
Copyright IBM Corporation 2003
IBM Global Services
Sorting records
Sorting By Primary Key : To sort the selection set in ascending order by the primary key, use the following: SELECT <lines> * ... ORDER BY PRIMARY KEY. This sorting method is only possible if asterisk (*) in the SELECT clause is used to select all columns. Sorting By Any Number of Columns SELECT ... ... ORDER BY <s1> [ASCENDING|DESCENDING] <s2> [ASCENDING|DESCENDING] ...
Slide 49
Copyright IBM Corporation 2003
IBM Global Services
Specifying Sorting Columns Dynamically
To specify the columns in the ORDER BY clause dynamically:
SELECT ... ORDER BY (<itab>). where <itab> is an internal table with line type C and maximum length 72 characters containing the column names <s 1 > <s 2 > .....
Slide 50
Copyright IBM Corporation 2003
IBM Global Services
ORDER BY clause
Performance Unless a Primary or Secondary index exists for the field list specified, ORDER BY f1.fn should not be used to sort the data on Database Server, but data should rather be sorted on the Application Server
Slide 51
Copyright IBM Corporation 2003
IBM Global Services
Subquery
SCARR SFLIGHT
Which airlines are not found in the sflight table?
Slide 52
Copyright IBM Corporation 2003
IBM Global Services
Subquery Syntax/Example
SELECT * FROM scarr WHERE NOT carrid IN ( SELECT carrid FROM sflight ). WRITE:/ scarr-carrid, scarr-carrname. ENDSELECT.
Slide 53
Copyright IBM Corporation 2003
IBM Global Services
Joins: Why We Should Use Them
Joins are more efficient than logical databases and nested selects. They access multiple tables with one select statement.
Slide 54
Copyright IBM Corporation 2003
IBM Global Services
Inner Joins
SCARR SFLIGHT
Slide 55
Copyright IBM Corporation 2003
IBM Global Services
Inner Joins Syntax
SELECT <table1~field1 table1~field2 table2~field3. . . > INTO (<target >) FROM <table1 > INNER JOIN <table2 > ON <table1~keyfield1 > = <table2~keyfield1 > AND <table1~keyfield2 > = <table2~keyfield2 > AND . . . WHERE . . . ENDSELECT.
The The
key word INNER can be omitted.
database system creates a temporary table containing the lines that meet the ON condition. The WHERE condition is then applied to the temporary table
Slide 56
Copyright IBM Corporation 2003
IBM Global Services
The Driving table
SELECT scarr~carrname sflight~carrid sflight~connid sflight~fldate INTO (carrid, connid, date, carrname) FROM scarr INNER JOIN sflight ON scarr~carrid = sflight~carrid. WRITE: / carrid, connid, date, carrname. ENDSELECT.
Slide 57
Copyright IBM Corporation 2003
IBM Global Services
Left Outer Join Syntax
SELECT <table1~field1 table1~field2 table2~field3. . . > INTO (<target >) FROM <table1 > LEFT OUTER JOIN <table2 > ON <table1~keyfield1 > = <table2~keyfield1 > AND <table1~keyfield2 > = <table2~keyfield2 > AND . . . WHERE . . . ENDSELECT.
the
database system creates a temporary table containing the lines that meet the ON condition. The remaining rows from the left hand table are then added to this table and their corresponding fields from the right hand table are filled with NULL values.
Slide 58
Copyright IBM Corporation 2003
IBM Global Services
Left Outer Joins
SCARR SFLIGHT
Slide 59
Copyright IBM Corporation 2003
IBM Global Services
Open SQL Syntax Restrictions
The syntax for joins have been given certain restrictions in order to insure that they produce the same results for all SAP supported databases.
Slide 60
Copyright IBM Corporation 2003
IBM Global Services
Redundancy
LFA1
BSIK
Slide 61
Copyright IBM Corporation 2003
IBM Global Services
CURSOR Processing
OPEN CURSOR [WITH HOLD] <cursor name> FOR <SELECT statement>.
FETCH NEXT CURSOR <cursor name> INTO <work area>.
CLOSE CURSOR <cursor name>.
Slide 62
Copyright IBM Corporation 2003
IBM Global Services
Declaring and OPENing a CURSOR
REPORT Y170DM77. When defining a CURSOR, TABLES: TABNA. it must be declared with the DATA: TABCUR TYPE CURSOR, BEGIN OF TABNA_WA, data type of CURSOR. NAME1 LIKE TABNA-NAME1, COUNTRY LIKE TABNA-COUNTRY, END OF TABNA_WA. When OPENing the CURSOR, the OPEN CURSOR TABCUR FOR associated SELECT SELECT NAME1 COUNTRY FROM TABNA. statement is executed with the result set DO. FETCH NEXT CURSOR TABCUR INTO TABNA_WA. created and stored in memory. IF SY-SUBRC <> 0. CLOSE CURSOR TABCUR. EXIT. ELSE. WRITE: / TABNA_WA-COUNTRY, TABNA_WA-NAME1. ENDIF. ENDDO.
Slide 63
Copyright IBM Corporation 2003
IBM Global Services
FETCHing Records from a CURSOR
REPORT Y170DM77. TABLES: TABNA. DATA: TABCUR TYPE CURSOR, BEGIN OF TABNA_WA, NAME1 LIKE TABNA-NAME1, COUNTRY LIKE TABNA-COUNTRY, END OF TABNA_WA. OPEN CURSOR TABCUR FOR SELECT NAME1 COUNTRY FROM TABNA.
The FETCH statement retrieves the rows from the CURSOR one by one until there are no more rows left.
DO. FETCH NEXT CURSOR TABCUR INTO TABNA_WA. IF SY-SUBRC <> 0. After the FETCH is executed, the SY-SUBRC is CLOSE CURSOR TABCUR. tested. Zero indicates a successful retrieval; EXIT. Four indicates there are no more rows. ELSE. WRITE: / TABNA_WA-COUNTRY, TABNA-NAME1. ENDIF. ENDDO.
Slide 64
Copyright IBM Corporation 2003
IBM Global Services
CLOSING the CURSOR
REPORT Y170DM77. TABLES: TABNA. DATA: TABCUR TYPE CURSOR, BEGIN OF TABNA_WA, NAME1 LIKE TABNA-NAME1, COUNTRY LIKE TABNA-COUNTRY, END OF TABNA_WA. OPEN CURSOR TABCUR FOR SELECT NAME1 COUNTRY FROM TABNA.
DO. FETCH NEXT CURSOR TABCUR INTO TABNA_WA. IF SY-SUBRC <> 0. CLOSE CURSOR TABCUR. EXIT. ELSE. WRITE: / TABNA_WA-COUNTRY, TABNA-NAME1. ENDIF. ENDDO.
Slide 65
When CURSOR processing is completed, the CURSOR should be closed with the CLOSE CURSOR statement.
Copyright IBM Corporation 2003
IBM Global Services
Inserting Records
Slide 66
Copyright IBM Corporation 2003
IBM Global Services
Inserting Records by Specifying the Values
Insert into <target> [client specified] values ( <val1>,<val2>.) . Target can be database table or view based on single table for which Insert permission is granted in its maintenance .
Insert Record From The work area :
Insert into <target> values <wa> Insert <target> from <wa> .
Insert Record From The Table work area :
Insert <dbtab> .
Slide 67
Copyright IBM Corporation 2003
IBM Global Services
Inserting Records from Internal Table
Inserting Several Lines INSERT <target table> FROM TABLE <itab> [ACCEPTING DUPLICATE KEYS] . Writes all lines of the internal table <itab> to the database table in one single operation. ACCEPTING DUPLICATE KEYS eliminates the duplicate entries automatically which are not allowed into a table .
Slide 68
Copyright IBM Corporation 2003
IBM Global Services
UPDATING RECORDS
Slide 69
Copyright IBM Corporation 2003
IBM Global Services
Updating Records
Changing Lines Column by Column
To change certain columns in the database table, use the following:
UPDATE <target> SET <col1> = <val1> <coln> = <valn>... [WHERE <cond>].
Changing Individual Lines From a Work Area
To overwrite a single line in a database table with the contents of a work area :
UPDATE <target> FROM <wa> .
Slide 70
Copyright IBM Corporation 2003
IBM Global Services
Updating Records continued
Update A Record From The Table work area :
Update <dbtab> .
Updating Several Lines From Internal Table UPDATE <target> FROM TABLE <itab> . Overwrites the line of the database tables with corresponding lines in internal table <itab> .
Slide 71
Copyright IBM Corporation 2003
IBM Global Services
DELETE
Slide 72
Copyright IBM Corporation 2003
IBM Global Services
Deleting Recordscontinued
Deleting By search Criteria
To delete records from database tables using some criteria :
DELETE FROM <target> WHERE <cond> .
Deleting Individual Lines From a Work Area To delete a single line in a database table from the contents of a work area : DELETE <target> FROM <wa> .
Slide 73
Copyright IBM Corporation 2003
IBM Global Services
Deleting Records continued
Delete A Record From The Table work area : Delete <dbtab> .
Deleting Several Lines From Internal Table Delete <target> FROM TABLE <itab> . Deletes the line of the database tables with corresponding lines in internal table <itab> .
Slide 74
Copyright IBM Corporation 2003
IBM Global Services
UPDATING OR INSERTING RECORDS : MODIFY STATEMENT
MODIFY <target> <lines>. If the database table contains no line with the same primary key as the line to be inserted, MODIFY works like INSERT, that is, the line is added. If the database already contains a line with the same primary key as the line to be inserted, MODIFY works like UPDATE, that is, the line is changed. The syntaxes of the modify statement are similar to the insert statement .
Slide 75
Copyright IBM Corporation 2003
IBM Global Services
Commit/Rollback
Open SQL contains the statements COMMIT WORK. and ROLLBACK WORK. for confirming or undoing database updates. COMMIT WORK always concludes a database LUW and starts a new one. ROLLBACK WORK always undoes all changes back to the start of the database LUW.
Slide 76
Copyright IBM Corporation 2003
IBM Global Services
Using ABAP Native SQL
EXEC SQL. <any SQL statement> ENDEXEC.
In this example, the program creates a table in the Oracle database, which is not known to the ABAP Dictionary.
The newly created database table is populated using an SQL INSERT statement.
Slide 77
Copyright IBM Corporation 2003
IBM Global Services
SELECT Using ABAP Native SQL
Data retrieved using the SELECT statement is transported to the ABAP program using Host Variables.
Used These are Host Variables
A Host Variable is any data item that is defined by the ABAP program and used inside an ABAP Native SQL Statement. They must be preceded by a colon.
Slide 78
Copyright IBM Corporation 2003
IBM Global Services
ABAP Native SQL and the PERFORMING Option
The PERFORMING <form> option creates a looping structure that for every row retrieved, the subroutine is called.
Slide 79
Copyright IBM Corporation 2003
