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