IBM Global Business Services
ABAP Open SQL Extensions
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Objectives
The participants will be able to:
Describe how to use the following in an ABAP Program:
SELECT DISTINCT Statement
Dynamic WHERE Clause
Concatenate Statement
Join (Inner vs. Left Outer)
Aliases
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
The SELECT DISTINCT Statement
REPORT YAP00007.
DATA: CNTRY TYPE YTABNA-COUNTRY.
SELECT DISTINCT COUNTRY
FROM Ytabna INTO (CNTRY).
WRITE: / CNTRY.
ENDSELECT.
ABAP Open SQL Extensions |
Using SELECT
DISTINCT, it
is possible
to eliminate duplicate
rows from the
result set.
Dec-2008
IBM Corporation 2013
IBM Global Business Services
SELECT Using Aggregate Functions
REPORT YAP00008.
DATA: TOTAL TYPE I,
MAX_SALES TYPE YTABNA-SALES,
AVG_SALES TYPE YTABNA-SALES,
MIN_SALES TYPE YTABNA-SALES.
SELECT
COUNT( DISTINCT COUNTRY )
MAX( SALES )
AVG( SALES )
MIN( SALES )
FROM ytabna
INTO (TOTAL, MAX_SALES, AVG_SALES, MIN_SALES)
WHERE COUNTRY <> SPACE.
WRITE: / Table ytabna Statistics,
/ TOTAL, Different Countries,
/8 MIN_SALES, Lowest Sales,
/8 AVG_SALES, Average Sales,
/8 MAX_SALES, Highest Sales.
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
The Dynamic WHERE Clause
REPORT YAP00009.
PARAMETERS:
WHERECL1(72) DEFAULT COUNTRY = USA ,
WHERECL2(3) DEFAULT OR,
WHERECL3(72) DEFAULT COUNTRY = GB .
TYPE: BEGIN OF ITAB_RECORD,
TEXT(72) TYPE C,
END OF ITAB_RECORD.
DATA: WHERE_ITAB TYPE STANDARD TABLE OF
ITAB_RECORD INITIAL SIZE 3,
WA_WHERE_ITAB TYPE ITAB_RECORD,
WA_YTABNA TYPE YTABNA.
WA_WHERE_ITAB-TEXT = WHERECL1.
APPEND WA_WHERE_ITAB TO WHERE_ITAB.
CLEAR WA_WHERE_ITAB.
WA_WHERE_ITAB-TEXT = WHERECL2.
APPEND WA_WHERE_TAB TO WHERE_ITAB.
CLEAR WA_WHERE_TAB.
WA_WHERE_ITAB-TEXT = WHERECL3.
APPEND WA_WHERE_TAB TO WHERE_ITAB.
SELECT * FROM YTABNA INTO WA_YTABNA WHERE
(WHERE_ITAB).
WRITE: / WA_TABNA-COUNTRY, WA_TABNA-ID.
ENDSELECT.
5
ABAP Open SQL Extensions |
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.
An internal table is created
which holds the WHERE
clause until it is used in the
SELECT statement.
Dec-2008
IBM Corporation 2013
IBM Global Business Services
CONCATENATE Statement
REPORT YAP00010.
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.
CONCATENATE <source field 1>
<source field 2>
<source field 3>
:
:
<source field n>
INTO <target field>
SEPARATED BY <constant>.
DATA: WHERE_ITAB TYPE STANDARD TABLE OF ITAB_RECORD
INITIAL SIZE 3,
WA_WHERE_ITAB TYPE ITAB_RECORD,
WA_YTABNA TYPE YTABNA,
IT_YTABNA TYPE STANDARD TABLE OF YTABNA.
CONCATENATE WHERECL1 WHERECL2 WHERECL3
INTO WA_WHERE_ITAB-TEXT SEPARATED BY SPACE.
APPEND WA_WHERE_ITAB TO WHERE_ITAB.
SELECT * FROM ytabna INTO TABLE IT_YTABNA WHERE
(WHERE_ITAB).
LOOP AT IT_YTABNA INTO WA_YTABNA.
WRITE: / WA_YTABNA-COUNTRY, WA_YTABNA-ID
ENDLOOP.
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Variations on the INTO Clause
SELECT * FROM <dbtable>
REPORT YAP00011.
INTO TABLE <internal table>
PACKAGE SIZE <n>.
DATA: ITAB TYPE STANDARD TABLE
OF
ENDSELECT.
ytabna INITIAL SIZE 5,
WA_ITAB TYPE ytabna.
SELECT * FROM ytabna
INTO TABLE ITAB
PACKAGE SIZE 5.
LOOP AT ITAB INTO WA_ITAB.
WRITE: / WA_ITAB-COUNTRY, WA_ITAB-ID.
ENDLOOP.
ENDSELECT.
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Specifying the Table Name at Runtime
REPORT YAP00012.
PARAMETERS: TAB_NAME(6) DEFAULT TABNA'.
DATA: TABLE_LINE(240).
SELECT * FROM (TAB_NAME) INTO TABLE_LINE.
WRITE: / TABLE_LINE.
ENDSELECT.
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Demonstration
Using a dynamic select statement in a custom ABAP program.
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Practice
Using a dynamic select statement in a custom ABAP program.
10
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
CURSOR Processing
OPEN CURSOR [WITH HOLD] <cursor name>
FOR <SELECT statement>.
FETCH NEXT CURSOR <cursor name>
INTO <work area>.
CLOSE CURSOR <cursor name>.
11
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Declaring and OPENing a CURSOR
REPORT YAP00013.
When defining a CURSOR,
DATA: TABCUR TYPE CURSOR,
it must be declared with the
BEGIN OF YTABNA_WA,
data type of CURSOR.
NAME1
TYPE YTABNA-NAME1,
COUNTRY TYPE YTABNA-COUNTRY,
END OF YTABNA_WA.
When OPENing the
CURSOR, the
associated SELECT
statement is executed
DO.
FETCH NEXT CURSOR TABCUR INTO YTABNA_WA. with the result set
created and stored in
IF SY-SUBRC <> 0.
memory.
CLOSE CURSOR TABCUR.
EXIT.
ELSE.
WRITE: / YTABNA_WA-COUNTRY, YTABNA_WA-NAME1.
ENDIF.
ENDDO.
OPEN CURSOR TABCUR FOR
SELECT NAME1 COUNTRY FROM ytabna.
12
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
FETCHing Records from a CURSOR
REPORT YAP00013.
DATA: TABCUR TYPE CURSOR,
BEGIN OF YTABNA_WA,
NAME1
TYPE YTABNA-NAME1,
COUNTRY TYPE YTABNA-COUNTRY,
END OF YTABNA_WA.
OPEN CURSOR TABCUR FOR
SELECT NAME1 COUNTRY FROM ytabna.
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 YTABNA_WA.
IF SY-SUBRC <> 0.
CLOSE CURSOR TABCUR.
After the FETCH is executed, the SY-SUBRC is
EXIT.
tested. Zero indicates a successful retrieval;
ELSE.
indicates there are no more rows.
WRITE: / YTABNA_WA-COUNTRY, Four
ytabna-NAME1.
ENDIF.
ENDDO.
13
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
CLOSING the CURSOR
REPORT YAP00013.
DATA: TABCUR TYPE CURSOR,
BEGIN OF YTABNA_WA,
NAME1
TYPE YTABNA-NAME1,
COUNTRY TYPE YTABNA-COUNTRY,
END OF YTABNA_WA.
OPEN CURSOR TABCUR FOR
SELECT NAME1 COUNTRY FROM ytabna.
When CURSOR processing is
completed, the CURSOR
should be closed with the
CLOSE CURSOR statement.
DO.
FETCH NEXT CURSOR TABCUR INTO YTABNA_WA.
IF SY-SUBRC <> 0.
CLOSE CURSOR TABCUR.
EXIT.
ELSE.
WRITE: / YTABNA_WA-COUNTRY, ytabna-NAME1.
ENDIF.
ENDDO.
14
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Demonstration
Using cursor in a program to retrieve data from a database table .
15
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Practice
Using cursor in a program to retrieve data from a database table .
16
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business 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.
17
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Inner Joins
SCARR
18
ABAP Open SQL Extensions |
SFLIGHT
Dec-2008
IBM Corporation 2013
IBM Global Business 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.
19
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business 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.
20
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Left Outer Joins
SCARR
21
ABAP Open SQL Extensions |
SFLIGHT
Dec-2008
IBM Corporation 2013
IBM Global Business 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.
22
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Open SQL Syntax Restrictions
The syntax for joins have been given certain restrictions in
order to ensure that they produce the same results for all SAP
supported databases.
23
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Redundancy
LFA1
24
BSIK
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Joins Accessing More than Two Tables
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 . . .)
INNER JOIN <table3 >
ON <table1~keyfield > = <table3~keyfield >
AND . . .
WHERE . . .
ENDSELECT.
25
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Aliases
SELECT A~carrname
B~carrid
B~connid
B~fldate
INTO (carrid, connid, date, carrname)
FROM scarr AS A INNER JOIN sflight AS B
ON scarr~carrid = sflight~carrid.
WRITE: / carrid, connid, date, carrname.
ENDSELECT.
26
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Subquery
SFLIGHT
SCARR
Which airlines are not found in the sflight table?
27
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Subquery Syntax/Example
SELECT * FROM scarr
WHERE NOT carrid IN
( SELECT carrid FROM sflight ).
WRITE:/ scarr-carrid, scarr-carrname.
ENDSELECT.
28
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Having Clause
List all Luftansa CONNIDs where the sum of
LUGGWEIGHT is < 20,000.
29
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Having Clause Syntax/Example
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.
30
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Demonstration
Using JOIN in select statement of a custom ABAP program retrieve data from
two or more related database tables.
31
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Practice
Using JOIN in select statement of a custom ABAP program retrieve data from
two or more related database tables.
32
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Summary
SELECT DISTINCT option is used to return only one record for each unique
occurrence of data in a column based on a field name.
Aggregate functions MIN, MAX, AVG, SUM, and COUNT are a valuable tool for
accumulating values across the entire table or a subset of the table rows based
on the conditions in the WHERE clause.
WHERE clause can be created at runtime and can be created by the user using
parameters.
It is possible to use the ORDER BY PRIMARY KEY, but not ORDER BY any other
field.
CURSOR processing enables the programmer to execute SELECT statements
that produce multiple row result sets then read/process each row of the result set
sequentially.
33
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Summary (Contd.)
The CURSOR data type is then associated with a SELECT from a database table
in the OPEN statement.
Individual rows of the result set are processed using a FETCH statement
sequentially until the end of the table is reached.
CURSOR processing is terminated using the CLOSE CURSOR statement. At
that point the result set is destroyed.
Joins have the advantage of accessing multiple tables with one Select statement,
thereby reducing the amount of server overhead.
Sub queries are a more efficient method of performing complex select
statements.
HAVING must be used with aggregate expressions.
34
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
IBM Global Business Services
Questions
What are the aggregate functions? And why these are used in the select
statement?
What is the purpose of using CONCATENATE statement?
What are the different steps of cursor processing?
How data can be retrieved from two or multiple related database table in single
select?
35
ABAP Open SQL Extensions |
Dec-2008
IBM Corporation 2013
