Transcript Searching and Selecting Data from R/3

Searching and Selecting
Data from R/3
ABAP/4 class
Gail Corbitt - Fall 1998
CSU
Chico
 SAP AG
Overall Objectives
 Learn how to find things in the Database
 Learn how to select things from R/3
 Manipulate the data in internal tables
 Display data in a report
 Do at least 3 assignments between now and the end of
the term
CSU
Chico
 SAP AG
Finding Data Elements in R/3
 Use ABAP/4 Data Dictionary
 SE16 to look up tables (if you know the name of the
table)
 SE11 to look up everything including tables
 SE15 to look up data elements that may exist in several
tables
 Where used can show you programs, other tables, and
logical databases where the table is used
CSU
Chico
 SAP AG
Types of Tables
 Transparent Table: A real table in the Database = Table
 Pool Table: A real table in the database made up of
several other tables, I.e. Cost centers by company code
within controlling area (They appear to the user as
separate tables, but are stored as one large table.)
 Cluster Table: No longer used -- a few hold overs from
R/2 when some parent child relationships should have
been stored as separate tables but were not, I.e. PO
Header and PO Line Items.
 Internal Table: Structure that contains no data - used
with a program -- can export to external table
CSU
Chico
 SAP AG
Accessing Data in R/3
 Uses 4 SQL commands in relation to tables

SELECT: Reads data from a Table or a View

UPDATE: Changes Data in a Table

INSERT: Adds records (rows) to a Table

DELETE: Removes records (rows) from a Table
 Basic Form

SELECT data elements, * FROM Table-name [WHERE condition].

….

ENDSELECT.
CSU
Chico
 SAP AG
Accessing Data in R/3
 Operators to Use with WHERE clause

EQ (Equals): =

NE (Not equal to): <>, ><

GT (Greater Than): >

LT (Less Than): <

GE (Greater Than or Equal To): >=

LE (Less Than or Equal To): <=
 Exercise:

Write a program to select all fields from T001

Modify the program to select all fields from T001 where the company
code Greater Than or Equal To 3000
CSU
Chico
 SAP AG
Accessing Data in R/3
 More Uses of WHERE Clause

AND: Creates a condition where all conditions must be True

OR: Create a condition where at least one condition must be True

NOT: Creates a condition where condition is everything but the Not
clause-- Examples:



WHERE field [NOT] BETWEEN value 1 AND value 2.
WHERE field NOT value1, value2, …value n.
WHERE field NOT LIKE string.

_ used as wild card for any single character

% used as wild card for any string of characters
CSU
Chico
 SAP AG
Accessing Data in R/3: ORDER BY
 Can add ORDER BY clause to any SELECT statement
 Example: ORDER BY field1, field2,… fieldn
 Example: ORDER BY PRIMARY KEY
CSU
Chico
 SAP AG
Using Logical Databases
 Logical Database: logical model of tables specific to a
single business object, I.e. Purchase Order, Sales
Order, etc.
 Usually written by SAP-- has own selection screen
 Shows relationships among several tables used to
maintain the single business object
 Hides complexity of the real table structure from the
user
 Look at EKKO and EKPO
CSU
Chico
 SAP AG
Using Logical Databases: GET
 GET command is an event that can be used to return
the data from a hierarchical structure in a logical db
 Example: GET MARAV.
 Logical Database:
MARAV
Material Header
Quantity Unit
CSU
Chico
 SAP AG
MARM
View for Logical DB
MBEWV
Material Master: Sales Data
MVKE
View for Logical DB MSM
MARCV
Forecast Parameters
PROPF
MM: Storage Loc/Batch Seg.
MARD
Using Logical Databases: GET
 GET only used with Logical Databases: triggers a new
read
 Logical Database is not mentioned in GET
 Attributed from Logical DB assigned to program when
GET is used
 START-OF-SELECTION can be used to do more than
one GET; also include END-OF-SELECTION.
 GET MARAV. …… GET MARD. Retrieves every node inbetween the 2 events.
 GET … LATE triggers get after rows have been read
CSU
Chico
 SAP AG
Using Logical Databases: GET
 To add a condition onto a GET use CHECK
 CHECK includes a condition statement
 If CHECK fails the program exits the GET on that row
 If CHECK passes the GET continues processing rows.
 EXAMPLE: GET MARAV.

CHECK MARAV-MTART = ‘T001’.

WRITE: /, /, (15) MARAV-MATNR.
 Example checks to see if material number is T001
 Similar to WHERE in SELECT
CSU
Chico
 SAP AG
Programming Assignment
 Using Program 1 in Report Painter as the example
 Find all the cost transactions for 1997, Controlling area
2000, Cost Center Group H3000, Periods 1-12 for All
Cost Elements. Display the Actual Costs in a report.
 Do not use the CCSS Structure
 Find the Logical Database or the Tables you need (you
can use CCSS as a reference)
 Extra Credit to include Planned Costs and Variance so
that the report looks like your first report writer
assignment.
CSU
Chico
 SAP AG