BC170_2.10.1
SELECT Statements
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