Preface – This post is part of the ABAP Beginner series.

Inner Join and Outer Join

Inner Join and Outer Join are the two different ways to join two tables. Both of them produce different result. In this article we will learn both of them.

Suppose you have two tables with two fields and following data as shown below:

Table 1

Table 2

Now if you want data that is common in both the table then you will apply Inner Join and get following results:

And if you want all data irrespective of its presence in any table, you will apply outer join and get results as shown below:

Now let see definition of both of the Joins.

Definition

Inner Join

A join that is utilized by Database views which gives the cross-product of the records present in both table.

[For basics of Joins click here]

Outer Join

A join that shows the cross-product of all the records present in different tables. It is utilized by Help Views, Projection Views and Maintenance Views. [For basics of Views click here]

It is of two types:

1. Left Outer Join: It is just like outer join, it includes all the results of Outer Join with all data from the left table.
2. Right Outer Join: It is also just like outer join, it includes all the results of Outer Join with all data from the right table.

 

Preface – This post is part of the ABAP Beginner series.

Joins in SAP ABAP

In View, we need to join two tables; these two tables can have or cannot have Keys in common. In case of View we give Join Condition (e.g. table1-field1 = table2-field1) on basis of which records are merged. Joins in SAP ABAP can also be applied on two tables without a Join condition and in that case it will just give cross product of the two tables.

Definition

Joins in SAP ABAP are cross product of two tables which further uses Join Condition to produce desired output.

Join Condition: When the cross product of two tables doesn’t give desired result, then a condition is written which tells us how the tables are connected. A statement is written where fields of each table are written with table name and equality operator.

e.g. table1-field1 = table2-field1

 

Example:

If we have two table as shown below and we apply

1. Join without any Join Condition

2. Join with Condition

Here the rows who don’t meet the condition are deleted.

 

 

Here the column which comes in right hand side of the Join Condition is also deleted.

 

 

This is the actual View as output.

*NOTE:  It is not necessary to compare two fields of different table to form a condition. A condition can also be like Table-Field1 = 1.

Types of Join

1. Inner Join
2. Outer Join

*Note:

1. Pooled & Cluster tables cannot be joined via Join statement.
2. In a single SELECT statement we must define at max 49 JOINS and specifying each table a namespace using AS.
3. A WHERE condition applied with JOIN statements are actually applied on the results of the JOIN statements.
4. If same column name appears in multiple tables, then its source is specified using column selector i.e. (~).
5. JOIN expressions bypass BUFFERING. We can access table buffer by using FOR ALL ENTRIES.

Preface – This post is part of the ABAP Beginner series.

Views in ABAP

Have you ever seen the views outside your window? Let’s suppose that a beautiful girl leaves just in front of your house with her parents. Now suppose you have a big window in your room from where you can see windows of both the girl and her parents. If you just want to see the girl, then you can use curtains to hide one part of the window, if you want to check her parents’ activity you can move the curtains again. That is Views in ABAP.

Views in ABAP helps us to see what we want to see from one table or many.

 

Definition

A View is a reusable object which doesn’t stores any data physically but is derived from one or more tables.

Mandatory steps to make Views in ABAP

 

Step 01: Open SE11 and choose Views Radio Button. Enter the name and Type of the View

Step 02: Select the tables for view.

Step 03: Link tables using Join Conditions.

Step 04: Select the fields from tables; these will be only visible in the views.

Step 05: Define the selection conditions which will restrict the records in the view.

 

Types of Views

 

VIEWS TYPE	JOIN USED	Description
Database Views	Inner Join	A View that integrate multiple Table into a single View.
Projection Views	N.A	A View that shows only few fields of single Table.
Help Views	Outer Join	A Search Help can show data from Table or Structure. But if you need data from multiple tables then we create a Help View for Search Help.
Maintenance Views	Outer Join	Unlike Database View where you can only View Data, if you want to maintain multiple Table @ a single place using View then we create Maintenance Views.

 

*NOTE: To know more about Joins click here.

 

Example

Requirement: You have three tables of Employee:

1. ZEMP_DETAILS that have Employee Details like Employee ID, USERNAME, PASSWORD, NAME, and Date of Birth.
2. ZEMP_ADDRESS that have Employee Details like Employee ID, Employee Address, Town, State, and Country.
3. ZEMP_SALARY that have Employee Details like Employee ID, Employee Basic Salary, Allowance and Tax.

Now you want to view all the details of an employee altogether. What will you do?

 

Proposed Solution:  We will create a view which will be based on Employee ID.

From above View we can see how we can project multiple tables altogether.

Preface – This post is part of the ABAP Beginner series.

ABAP Table Index

Suppose you have a million unsorted records and you want to perform search on the basis of non primary key. Then it will take a lot of time to process all the records. In this case you will make something called Secondary Indexes or ABAP Table Index.

Definition

An Index is a sorted copy of Database which contains pointer pointing to non-key fields which is used to increase the search speed of a table.

Types of Indexes

Primary Index:

The Primary Index is an Index created by system itself just after the creation of table. It contains only key fields. It has ID 0.

Secondary Index:

When your search criteria are not fulfilled by Primary Index, then in this case we can create our own Indexes called Secondary Indexes. Its ID can be of max 3 digit alphanumeric value. The total length of Index Name can be of 13 digit which includes table name and Index ID.

The naming convention of Secondary Indexes:

<TABLE_NAME> ~ <INDEX_ID>

e.g.  ZBARRY_ALLEN ~ A.

 

Unique Index:

If we define an Index as Unique Index if we want only unique records or unique combination of field in the table.

e.g. If you have defined two field combination as Unique. Then only unique values will be inserted. For more visit ABAP Documentation.

Extension Index:

A secondary Index is temporary index and needs recreation after every upgrade. If we create Extension Index it is upgrade protected. We don’t have to do anything new, just choose Extension Index in step 02 below.

How to Create a Secondary ABAP Table Index:

Step 01: Create a table and in the maintenance screen click on Indexes. [How to Create a table: Click here]

Step 02: Choose New Icon  and click Create.

Step 03: Choose Index from Drop Down box. Enter Index ID and click CONTINUE.

Step 04: Enter an explanatory Short Description.

Step 05: Choose Table Fields, choose desired fields and choose COPY.

*NOTE: The order of the field is important.

Step 06: If the values in the index are always unique then choose Unique radio button else leave it.

Step 07: According to the use of Index choose one of the radio button:

Radio Button	Description
For all database systems	If it will be used across all database systems
For selected database systems	If only for selected database systems. You can select at max 4.
Not at all in the database	If it is an exclusive Index

 

Step 08: Save it, Check for Errors and Activate it.

Disadvantages of Index

More the number of ABAP Table Index, the more load on the system, as it needs to adjust Indexes every time you Insert, Update or Delete a record. Due to which Table which is written frequently must have less indexes.

Preface – This post is part of the ABAP Beginner series.

Table Buffering in SAP ABAP

Before we start with “Table Buffering in SAP ABAP”. Let us take a general example. Have you ever used YouTube? And, have you ever buffered video before watching it?

If YES, then you already have the idea of Buffering.

And If not, go to YouTube and pause your video, it will load it (Buffer it). So in this scenario, the YouTube video is loaded in your Browser/Application and when you play it, it doesn’t load it anymore from YouTube Server but directly plays it from the Buffer of your Application Server.

In this way you save your time and have a better experience. This concept of Buffering is also utilized by ABAP Tables and termed as Table Buffering in SAP ABAP.

Definition

Table Buffering in SAP ABAP is a concept in ABAP Tables to enhance performance (10 to 100 times) and reduce time of processing (accessing) the table.

A Buffer is an interface between Database layer and Application layer. Application Layer communicates with Buffer and the Buffer communicates with Database layer and vice-versa.

*NOTE: Database Interface determines whether the Data is in buffer or not. If not, it takes data from database and also syncs the Buffer.

Using Buffers in Table

Step 01: Choose one radio button from Buffering Permissions.

Permission	Description
Buffering not allowed	This is default value. It is for Transaction table where data changes frequently
Buffering allowed but switched off	Buffer is allowed but it is switched OFF and can be switched ON anytime according to the requirement of customer
Buffering switched on	Buffering is allowed. In this case we need to provide the Buffering type.

 

Step 02: Choose the buffering type: It defines which data will be loaded and when.

Buffering Type	Description
Full Buffering	System loads all the data into Buffer whenever single record of the table is accessed.
Generic Buffering	When a record with a specific Generic key is accessed, all other records of that Generic key are also buffered.
Single-record Buffering	Only the record that was really accessed is buffered.

 

Full Buffering

When Full Buffering is used then either entire table is buffered or none of the records will be buffered. When a single record is accessed, the entire data/record of that table is buffered. By Buffering we mean that all the record is in the buffer table.

When To Use Full Buffering

• Larger table is rarely written and frequently read like Master Table, and then we need to use Full Buffering.
• For small tables like customizing tables that are mainly read.

Generic Buffering

When Generic Buffering is used, all the records with Generic keys are buffered all at once.

*NOTE:

• Generic Keys: The key combination of only certain keys that is used to filter data from table.
• Generic key is a part of Primary key.
• All the Generic keys should be mentioned in WHERE condition of SELECT statement otherwise Buffer is bypassed and data is read from Database.

When To Use Generic Buffering

• When only certain Generic Areas are needed.
• For Client Specific Tables, the Client field is the Generic key.
• For Language Specific Tables, The Generic key includes Language key.

Single-Record Buffering

All the Data that you have accessed will be buffered and nothing apart from them.

When To Use Single-Record Buffering

• Table where only few records are accessed

How to Bypass a Buffer

By using BYPASSING BUFFER we can bypass the buffer and read directly from database.

SELECT * FROM <TABLE_NAME> INTO TABLE <ITAB> BYPASSING BUFFER.

 

 

Preface – This post is part of the ABAP Beginner series.

Delivery Class in ABAP Tables

In Data Class we have specified the Type of Data we will be using for our table, in Size Category we have declared the expected Volume of Data we will be saving. Now, we need to define the way the Data in the table behaves. It means whether data can be moved from one Landscape/system to other or not. It is achieved using Delivery Class in ABAP Tables.

*NOTE: Although the Delivery Class is declared before Data Class and Size Category, still it is advisable to read about it after them.

Definition

A delivery class defines:

• Transport control of Data within Landscapes and customer systems
• Transport control of Data for an installation or upgrade
• Transport control of Data of client copy
• Data can be maintained in Extended Table Maintenance [SM30] or not.

In short we can say a delivery class determines whether data of the table can be maintained & transported or not.

Types of Delivery Class

TYPE	TABLE TYPE	DESCRIPTION	TABLE BEHAVIOUR DURING CLIENT COPY	TABLE BEHAVIOUR During Installation, Upgrade and Language Import
A	Application table (Master and Transaction data)	Master and Transaction data is maintained by SAP	Data is copied only if requested	·         Cross-Client Tables : No Data Imported ·         Client-Dependent Tables: Data Imported into Client 000.
C	Customer table	Data is only maintained only  by Customer	Data is copied to the target client	·         Cross-Client Tables : No Data Imported ·         Client-Dependent Tables: Data Imported into Client 000.
G	Customer table	SAP can only Insert data & cannot modify/Delete	Data is copied to the target client	·         Cross-Client Tables : Imported and cannot be overwritten ·         Client-Dependent Tables: Imported and can be overwritten only for CLIENT 000.
L	Temporary table	Table to story only temporary data	Data are not copied to the target client	·         Cross-Client Tables : No Data Imported ·         Client-Dependent Tables: No Data Imported.
E	System table	Table with own namespace(in table TRESC) for customer entries	Data is copied to the target client	·         Cross-Client Tables: Imported and can be overwritten with same key. ·         Client-Dependent Tables: Imported and can be overwritten.
S	System table	Table with status of Data changes	Data is copied to the target client	·         Cross-Client Tables: Imported and can be overwritten with same key. ·         Client-Dependent Tables: Imported and can be overwritten.
W	System table	Table whose Data is Transported via its own Transport Objects	Data are not copied to the target client	·         Cross-Client Tables: Imported and can be overwritten with same key. ·         Client-Dependent Tables: Imported and can be overwritten.

*NOTE: Client Dependent Tables are tables with MANDT field and any changes in the table will impact only one Client while Client Independent Tables are also called Cross Client Table which does not have MANDT field and hence any changes will impact all the clients in the server.

Preface – This post is part of the ABAP Beginner series.

Size Category of Database Tables

In Data Class we have defined the kind of Data we are going to have for our table. Now, it’s time to decide the expected Volume of Data. We do it via Size Category of Database Tables.

Initially Every Table receives equal amount of allowed space. If the data exceeds the initial space provided then an extent of memory is provided to the table, the amount of extended memory will depend upon the size category we provide.

In this way we actually prevent creation of Large extended memory and thus reduce the wastage of data base space.

Definition

Initially every table have some memory allotted. Size Category tells us the amount of space that will be extended after the initial memory is filled.

What will happen if extended memory created is also filled?

Then a new extend will be created again with the memory equal to the size category provided as shown below:

How Multiple Extends affects the Data base?

A table with multiple extensions affects database in following ways:

• Decreases System Performance: Data will be fetched from two different sources.
• Increases Database management: Database Reorganization is required
• Lowers Systems Availability: For Database reorganization, it is taken offline.

TYPES OF SIZE CATEGORY

 

Size Category	Expected Rows
0	0 to 1,000
1	1,000 to 4,200
2	4,200 to 17,000
3	17,000 to 68,000
4	68,000 to 270,000
5	270,000 to 540,000
6	540,000 to 1,000,000
7	1,000,000 to 2,100,000
8	2,100,000 to 4,300,000
9	4,300,000 to 170,000,000

 

Preface – This post is part of the ABAP Beginner series.

Data Class in ABAP

Data Class in ABAP is assigned to a table in its Technical Settings. SAP was smart while creating properties for tables. With the help of Data Class it divides the Tables according to their type of Data. It keeps all Master Data together, all Transaction Data together, all User Data together and all Organizational Data together.

Definition:

A Data Class represents a physical area in Data Base where all the Data of the table assigned to this Data Class are stored.

TYPES OF DATA CLASS:

 

DATA CLASS	TYPE OF DATA	DESCRIPTION
APPL0	Master Data	Data that doesn’t changes frequently. e.g. Your Name, Your Employee ID, Your Student ID
APPL1	Transaction Data	Data that changes frequently. e.g. Number of Products on Amazon, Value of Bitcoin
APPL2	Organizational Data	Customizing Data which is defined during System Installation and that doesn’t changes frequently. e.g. Country Codes.
USR & USR1	User Data	Data Class only for user Development.

 

*NOTE: Customizing Data is the data that describes the behavior of SAP environment for particular module.

e.g.  Country Codes, types of Reimbursement Expenses, working hours of Employees.

Preface – This post is part of the ABAP Beginner series.

SAP Tables

Unlike other Database where we write SQL commands (DDL) to create table, SAP provides us option to create Tables in ABAP Dictionary. Upon Activation, SAP Tables are actually defined in database.

Creation of SAP Tables:

#Using ABAP Dictionary

Step 01: Open SE11.

Step 02: Click Database table Radio Button and give your Table name starting with Z or Y e.g. “ZBarry_Table”.

Step 03: Click Create, Enter explanatory description for table in Short Description e.g. “Table for Test”.

Step 04: Under Delivery & Maintenance tab maintain the following:

FIELDS	Value	Description
Delivery Class	Select from given drop down A –Application Table C – Customer Table	A Delivery class controls the transport of table data.
Data Browser/ Table View Maint.	Select the Maintenance option from given drop down	If allowed, It allows the table owner to maintain data via SE16.

 

Step 05: Under Fields tab maintain the following:

FIELDS	Value	Description
Field	It can be 16 digit Alphanumeric word starting with a letter and can contain underscore	It is the column name of your table
Key	Tick all the fields whose value will be unique and can define the relationship with other tables	A table must contain at least one key field, else it can’t be activated.
Initial Value Indicator	Tick if the field must have an initial value	All key fields must always be given initial values. By default all fields are assigned initial values irrespective of indicator.
Data Element	Select your Data Element or create one	If you are not using Predefined Type, you need to provide a Data Element
Predefined Type : If you are not using Data Element, click the Predefined Type tab & enter following:
Data type	Any Data type like CHAR,INT	According to requirement specify the data type
Length	Give max length of your field	Length will be dependent upon Data Type
Decimal places	Number of places after decimal point	Mainly for Float variables
Short description	Explanatory statement for your field	What your field means/doing
Include Structure: Whenever we include a structure in table, it might have fields with same name as there are other fields in table. To avoid conflict we address the field with a suffix (3-27 Character with ‘-‘) for that structure. This suffix is given in Group tab.
Group	3-27 Character	To Address Include Structure fields

 

Note: Maintain foreign key relationship by clicking. Also maintain Search Help by clicking Srch Help tab.

Step 06: Under Entry help/check tab maintain the following: It will be maintained automatically after step 05. [You can skip this step]

FIELDS	Value	Description
Field	Same as above	Same as above
Data Element	Same as above	Same as above
Data Type	Same as above	Same as above
Foreign Keys	Foreign Keys for that field	It will be reflected after you maintain Foreign Key relationships
Check Table	Check Table for Field	It will be reflected once you have check table relationship for the field
Origin of input help	Input help with fixed values, with check table, search helps, Static Input Help	It reflects the way your input will be made to the given field
Srch Help	Your Search Help name	To attach Search Helps to field
Defaults	Any Value depending upon the type of field	To assign Default value for the field
Domain	Domain of specified Data Element	If your Data Element has a Domain, it will reflect here

 

Step 07: Under Currency/ Quantity Fields tab maintain the following: [You can skip this step]

FIELDS	Value	Description
Field	Same as above	Same as above
Data Element	Same as above	Same as above
Data Type	Same as above	Same as above
Reference Table	Reference Table name	If your data element refers to a table
Reference Field	Field of Reference table being Referred	It is actually the field being referred by the data element
Short Description	Same as above	Same as above

 

Step 07: Maintain Indexes and Append Structure from respective tab. [You can skip this step]

Step 08: Maintain following @Technical Settings: [Important Step]

FIELDS	Value	Description
Data Class	APPL0 – Master Data APPL1 – Transaction DataAPPL2 – Organizational Data	It describes the type of Data you will be storing
Size category	From 0 to 4	It defines the expected space required for table in database
Buffering	Choose from Radio Button	It tells whether your table will be using buffering or not. If yes, then which type of Buffering.

 

Step 09: Once all the above fields are maintained, save, check for errors and activate your table.

 

Additional Information

 

• All Key fields should be maintained in the beginning.
• Maximum 16 key fields are permitted per table.
• Length of key field can be max 255 characters (and 120 if data is to be transported).
• A table can have total 249 fields.
• A field name must not be longer than 16 characters.
• Fields of type STRING, LRAW, LCHR must be at the end of table.
• Fields without data element cannot have foreign keys, F1 Help and fixed values.

[embedyt] https://www.youtube.com/watch?v=9Ejtbd9SS7Y[/embedyt]

Preface – This post is part of the ABAP Beginner series.

DATA ELEMENTS

We cannot use Domain directly neither in a Report/Program nor as a table field. We need to add some semantic information i.e. Field Label, heading, etc. For this purpose we need data elements.

Definition:

Data Elements are reusable objects that are used to add Field Label, Heading, Documentation (F1 Help) and assign a parameter ID and a Search Help (F4 Help).

Note:  In Domain Level also we have an option to define Search Help which fetches either fixed value defined there or from Value Help Table while the one we assign in Data Element is from Global Search Helps that we create via SE11.

Creation of Data Elements:

#Using ABAP Dictionary

Step 01: Open SE11.

Step 02: Click Data type Radio Button and give your Data Element name starting with Z or Y e.g. “ZBarry_DataElement”.

Step 03: Click Create, Choose Data Element Radio Button and Enter explanatory description for Data Element in Short Description e.g. “Data Element for Test”. This short Text appears as title of Data Element F1 help Documentation.

Step 04: Under Data Type Tab, you will have two options.  Choose any one and provide its corresponding details.

FIELDS	Value	Description
Elementary type:  It is used to define Data Element on the basis of either Domain or predefined Data types.
1. Domain	Enter your Domain Name	It adds technical characteristics in Data Element same as that of Domain.
2. Predefined Type	Enter Data Type & Length	Data Type can be any predefined data types with any length.
Reference Type:  It is used to create a pointer to data objects.
1. Name of Ref. Type	Name of Class, Interface, Object, ANY or Data	The Data Element will point to the given Value.
2. Reference to Predefined Type	Enter Data Type, Length and Decimal Places	Data Type can be any Predefined Data Type like CHAR, INT and Length & Decimal Places can be of any length.

Step 05: Add your Search Help, Parameter ID, Default Component Name and Mark your Data Element as a change document under Further Characteristics tab. For more read ABAP Documentation.

Step 06: Add Short, Medium, Long, Heading field label under Field Label tab. Field Label is used to display the name of fields in Parameters of Report, column Header of Table & structure. The purpose of providing different Labels is that these labels are displayed according to input template size.

[embedyt] https://www.youtube.com/watch?v=MxJsMhgQVeo[/embedyt]