BC170_2.10.1

SELECT Statements

BC170_2.10.2

Objectives

• The participants will be able to:

–

Select data from a database using a restrictive

WHERE clause

–

Describe how the following ABAP commands effect

a Select Statement: SELECT SINGLE, LIKE, IN,

ORDER BY, BYPASSING BUFFER

–

Describe when to use a WHERE clause vs. a

CHECK statement in an ABAP Program

–

Describe when to use SELECT <field list> vs.

SELECT * in an ABAP Program

–

Use Aggregate Functions in an ABAP Program

BC170_2.10.3

REPORT: ZRACER01.
TABLES: LFA1, LFB1,
LFC3.

SELECT * FROM LFA1.

WRITE: LFA1-LIFNR.

ENDSELECT.

Database Access I

Logical

Database

ABAP Native SQL

ABAP Native SQL

ABAP Open SQL

ABAP Open SQL

No
Authorization
Checking

Physical

Database

BC170_2.10.4

SQL (SELECT)

ABAP Programs

REPORT: YNEWAPPL.

REPORT: YNEWAPPL.

TABLES...

TABLES...

SELECT <f1 f2 fn> FROM LFA1.

SELECT <f1 f2 fn> FROM LFA1.

ENDSELECT.

ENDSELECT.

ABAP

Reports

Data

Database

LFA1

LFA1

LFB1

LFB1

LFC1

LFC1

BC170_2.10.5

WHERE Clause: Single Access

In the return-code field

In the return-code field

(SY-SUBRC),

(SY-SUBRC),

0 = successful and

0 = successful and

4 = entry does not exist.

4 = entry does not exist.

Expected
Results

The SELECT SINGLE *

The SELECT SINGLE *

statement requires the full

statement requires the full

primary key of the table in

primary key of the table in

the WHERE clause.

the WHERE clause.

BC170_2.10.6

Syntax: Restricted Loop

Processing

SELECT * FROM <table>

WHERE <table field 1> <relational operator> <field 1>

AND <table field 2> <relational operator> <field 2>

OR <table field 3> <relational operator> <field 3>

AND <table field 4> <relational operator> <field 4>

:

:

OR <table field n> <relational operator> <field n>.

ENDSELECT.

EQ

EQ

=

=

GE

GE

>=

>=

=>

=>

LE

LE

<=

<=

=<

=<

NE

NE

<>

<>

><

><

GT

GT

>

>

LT

LT

<

<

Relational
Operators

BC170_2.10.7

SELECT * FROM <table> WHERE <table field>...

SELECT * FROM <table> WHERE <table field>...

BETWEEN <field 1> AND <field 2>

BETWEEN <field 1> AND <field 2>

LIKE <with ‘%’ and ‘_’ masked literal>

LIKE <with ‘%’ and ‘_’ masked literal>

IN (<field 1, field 2,......field n>)

IN (<field 1, field 2,......field n>)

Syntax: Between Values, Templates

and Lists

BC170_2.10.8

The result set is

The result set is

limited by the

limited by the

conditions in the

conditions in the

WHERE clause.

WHERE clause.

WHERE Clause: Loop

Processing with Restricted

Selection

Expected
Results

BC170_2.10.9

LIKE: Example of a Template (%_)

Expected
Results

The ‘_’ is an any character
positional parameter.

The ‘%’ allows for any
string of any length.

BC170_2.10.10

IN: Example of a Comparison

Value List

Expected
Results

BC170_2.10.11

IN: Example of a Range

This internal table is the

same one that is created

automatically when a

selection screen is processed.
The RANGES statement builds

the structure of this internal

table automatically.

RANGES <name> FOR

<field>.

BC170_2.10.12

The ORDER BY Clause

SELECT * FROM <table>

WHERE <condition>

ORDER BY <table field 1>

<table field 2>
<table field 3>

:
:

<table field n>.

ENDSELECT.

BC170_2.10.13

BYPASSING BUFFER

SELECT * FROM

<table>

BYPASSING BUFFER

WHERE

<condition>.

:

:

:

ENDSELECT.

BC170_2.10.14

INTO TABLE <itab>

Array Operations

ID NAME1

COUNTRY

00000001 Baker Distributors

USA

00000002 Diversified Indust... USA

EMPLOYEE

EMPLOYEE

COUNTRY ID FORMA NAME1 SORTL . .

.

Header Line

1

2

3

10

.

.

.

.

.

.

1

Table Work Area

BC170_2.10.15

WHERE Vs CHECK in a SELECT

Statement

BC170_2.10.16

SELECT <field list> vs.. SELECT *

MANDT

MATNR

ERSDA

ERNAM

LAEDA

. . . . .

Which

ones?

BC170_2.10.17

Aggregate Functions

12

15

4

9

7

14

SUM = 61

BC170_2.10.18

Summary

• The participants should be able to:

–

Select data from a database using a restrictive

WHERE clause

–

Describe how the following ABAP commands effect

a Select Statement: SELECT SINGLE, LIKE, IN,

ORDER BY, BYPASSING BUFFER

–

Describe when to use a WHERE clause vs. a

CHECK statement in an ABAP Program

–

Describe when to use SELECT <field list> vs.

SELECT * in an ABAP Program

–

Use Aggregate Functions in an ABAP Program

BC170_2.10.19