BC170_2.11.1
ABAP Open SQL Extensions
BC170_2.11.1
ABAP Open SQL Extensions
BC170_2.11.2
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
BC170_2.11.3
The SELECT DISTINCT Statement
Using SELECT
DISTINCT, it
is possible
to eliminate
duplicate rows
from the
result set.
Expected
Results
BC170_2.11.4
SELECT Using Aggregate Functions
Expected
Results
BC170_2.11.5
The Dynamic WHERE Clause
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.
BC170_2.11.6
CONCATENATE Statement
CONCATENATE <source field 1>
<source field 2>
<source field 3>
:
:
<source field n>
INTO
<target field>
SEPARATED BY <constant>.
BC170_2.11.7
Variations on the INTO Clause
SELECT <FIELD> FROM <dbtable>
INTO TABLE <internal table>
PACKAGE SIZE <n>.
ENDSELECT.
BC170_2.11.8
Specifying the Table Name at
Runtime
BC170_2.11.9
Joins: Why We Should Use Them
•
Joins are more efficient than logical databases and nested selects.
•
They access multiple tables with one select statement.
BC170_2.11.10
Inner Joins
SFLIGHT
SCARR
BC170_2.11.11
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.
BC170_2.11.12
The Driving Table
SELECT
scarr~carrname sflight~carrid
sflight~connid sflight~fldate
INTO (
carrname, carrid, connid, date)
FROM
scarr
INNER JOIN
sflight
ON
scarr~carrid
=
sflight~carrid
.
WRITE: /
carrid, connid, date,
carrname
.
ENDSELECT.
BC170_2.11.13
Left Outer Joins
SFLIGHT
SCARR
BC170_2.11.14
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.
BC170_2.11.15
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.
BC170_2.11.16
Redundancy
LFA1
BSIK
BC170_2.11.17
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.
BC170_2.11.18
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.
BC170_2.11.19
Subquery
SFLIGHT
SCARR
Which airlines are not found in the sflight
table?
BC170_2.11.20
Subquery Syntax/Example
SELECT
carrid carrname
FROM
scarr
INTO (
scarr-carrid, scarr-carrname
)
WHERE NOT
carrid
IN
( SELECT
carrid
FROM
sflight
).
WRITE:/
scarr-carrid, scarr-carrname
.
ENDSELECT.
BC170_2.11.21
Having Clause
List all Luftansa CONNIDs where the sum of
LUGGWEIGHT is > 20,000.
BC170_2.11.22
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
) >
20000
.
WRITE:/
carrid, connid, count, sum_weight.
ENDSELECT.
BC170_2.11.23
Summary
• The participants should 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
BC170_2.11.24