IBM Global Services

ABAP Open SQL Extensions

ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global 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

2 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global Services

The SELECT DISTINCT Statement

REPORT YAP00007.

TABLES: Ytabna.

DATA: CNTRY LIKE YTABNA-COUNTRY.

SELECT DISTINCT COUNTRY

FROM Ytabna INTO (CNTRY).

WRITE: / CNTRY. Using SELECT

DISTINCT, it
ENDSELECT. is possible
to eliminate duplicate
rows from the
result set.

3 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global Services

SELECT Using Aggregate Functions

REPORT YAP00008.
TABLES: ytabna.
DATA: TOTAL TYPE I,
MAX_SALES LIKE YTABNA-SALES,
AVG_SALES LIKE YTABNA-SALES,
MIN_SALES LIKE 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’.

4 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global Services

The Dynamic WHERE Clause

The parameters
REPORT YAP00009.
TABLES: ytabna. entered make up the
PARAMETERS: contents of the
WHERECL1(72) DEFAULT ‘COUNTRY = ‘ ‘USA’ ’ ’, WHERE clause. The
WHERECL2(3) DEFAULT ‘OR’, user has the option of
WHERECL3(72) DEFAULT ‘COUNTRY = ‘ ‘GB’ ’ ’. changing the
TYPE: BEGIN OF ITAB_RECORD, TEXT(72), conditions and
END OF ITAB_RECORD. dynamically effecting
DATA: WHERE_ITAB TYPE STANDARD TABLE OF which way the
ITAB_RECORD INITIAL SIZE 3 WITH HEADER LINE. program will execute.

WHERE_ITAB-TEXT = WHERECL1.
An internal table is created
APPEND WHERE_ITAB.
which holds the WHERE
WHERE_ITAB-TEXT = WHERECL2.
APPEND WHERE_ITAB.
clause until it is used in the
WHERE_ITAB-TEXT = WHERECL3. SELECT statement.
APPEND WHERE_ITAB.

SELECT * FROM ytabna WHERE (WHERE_ITAB).

WRITE: / YTABNA-COUNTRY, YTABNA-ID.
ENDSELECT.

5 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global Services

CONCATENATE Statement
REPORT YAP00010.
TABLES: ytabna. CONCATENATE <source field 1>
PARAMETERS: <source field 2>
WHERECL1(72) DEFAULT ‘COUNTRY = ‘ ‘USA’ ’ ’, <source field 3>
WHERECL2(3) DEFAULT ‘OR’, :
WHERECL3(72) DEFAULT ‘COUNTRY = ‘ ‘GB’ ’ ’. :
<source field n>
TYPE: BEGIN OF ITAB_RECORD,
INTO <target field>
SEPARATED BY<constant>.
TEXT(72),
END OF ITAB_RECORD.

DATA: WHERE_ITAB TYPE STANDARD TABLE OF

ITAB_RECORD INITIAL SIZE 3 WITH HEADER LINE.

CONCATENATE WHERECL1 WHERECL2 WHERECL3

INTO WHERE_ITAB-TEXT SEPARATED BY SPACE.

APPEND WHERE_ITAB.

SELECT * FROM ytabna WHERE (WHERE_ITAB).

WRITE: / YTABNA-COUNTRY, YTABNA-ID.
ENDSELECT.
6 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation
 IBM Global Services

Variations on the INTO Clause

SELECT * FROM <dbtable>

REPORT YAP00011. INTO TABLE <internal table>
PACKAGE SIZE <n>.
TABLES: ytabna. ENDSELECT.

DATA: ITAB LIKE STANDARD TABLE OF

ytabna INITIAL SIZE 5 WITH HEADER LINE.

SELECT * FROM ytabna

INTO TABLE ITAB
PACKAGE SIZE 5.

LOOP AT ITAB.
WRITE: / ITAB-COUNTRY, ITAB-ID.
ENDLOOP.

ENDSELECT.

7 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global Services

Specifying the Table Name at Runtime

REPORT YAP00012.

PARAMETERS: TAB_NAME(6) DEFAULT 'MAKT'.

DATA: TABLE_LINE(240).

SELECT * FROM (TAB_NAME) INTO TABLE_LINE.

WRITE: / TABLE_LINE.

ENDSELECT.

8 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global Services

Demonstration

 Using a dynamic select statement in a custom ABAP program.

9 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global Services

Practice

 Using a dynamic select statement in a custom ABAP program.

10 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

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

11 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global Services

Declaring and OPENing a CURSOR

REPORT YAP00013.
TABLES: ytabna. When defining a CURSOR,
DATA: TABCUR TYPE CURSOR, it must be declared with the
BEGIN OF YTABNA_WA, data type of CURSOR.
NAME1 LIKE YTABNA-NAME1,
COUNTRY LIKE YTABNA-COUNTRY,
END OF YTABNA_WA.
When OPENing the
OPEN CURSOR TABCUR FOR CURSOR, the
SELECT NAME1 COUNTRY FROM ytabna. associated SELECT
statement is executed
DO. with the result set
FETCH NEXT CURSOR TABCUR INTO YTABNA_WA.created and stored in
IF SY-SUBRC <> 0. memory.
CLOSE CURSOR TABCUR.
EXIT.
ELSE.
WRITE: / YTABNA_WA-COUNTRY, YTABNA_WA-NAME1.
ENDIF.
ENDDO.

12 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global Services

FETCHing Records from a CURSOR

REPORT YAP00013.
TABLES: ytabna.
DATA: TABCUR TYPE CURSOR,
BEGIN OF YTABNA_WA,
NAME1 LIKE YTABNA-NAME1,
COUNTRY LIKE YTABNA-COUNTRY, The FETCH statement
END OF YTABNA_WA. retrieves the rows from the
CURSOR one by one until
OPEN CURSOR TABCUR FOR there are no more rows left.
SELECT NAME1 COUNTRY FROM ytabna.

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. Four indicates there are no more rows.
WRITE: / YTABNA_WA-COUNTRY, ytabna-NAME1.
ENDIF.
ENDDO.

13 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global Services

CLOSING the CURSOR

REPORT YAP00013.
TABLES: ytabna.
DATA: TABCUR TYPE CURSOR,
BEGIN OF YTABNA_WA,
NAME1 LIKE YTABNA-NAME1, When CURSOR processing is
COUNTRY LIKE YTABNA-COUNTRY, completed, the CURSOR
END OF YTABNA_WA. should be closed with the
CLOSE CURSOR statement.
OPEN CURSOR TABCUR FOR
SELECT NAME1 COUNTRY FROM ytabna.

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 | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global Services

Demonstration

 Using cursor in a program to retrieve data from a database table .

15 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global Services

Practice

 Using cursor in a program to retrieve data from a database table .

16 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 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.

17 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global Services

Inner Joins

SCARR SFLIGHT

18 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 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.

19 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 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.

20 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global Services

Left Outer Joins

SCARR SFLIGHT

21 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 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.

22 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 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.

23 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global Services

Redundancy

LFA1 BSIK

24 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global 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 | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global 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 | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global Services

Subquery

SCARR SFLIGHT

Which airlines are not found in the sflight table?

27 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global 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 | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global Services

Having Clause

List all Luftansa CONNIDs where the sum of

LUGGWEIGHT is < 20,000.

29 ABAP Open SQL Extensions | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global 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 | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global 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 | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global 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 | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global 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.
 CONCATENATE is a string processing statement which enables the programmer
to combine the contents of one or more source fields into a single target field.
 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 | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global 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 | 3.10 March-2005 © 2005 IBM Corporation

 IBM Global 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 | 3.10 March-2005 © 2005 IBM Corporation