Select for all entries in SAP ABAP

SELECT FOR ALL ENTRIES is the best alternative for SELECT WITH JOINS, this statement is very helpful for reading data from more than 2 tables.
The load on database will be very less.

Syntax : 

SELECT <FIELDS> FROM <DBTABLE1> INTO TABLE <ITAB1>
WHERE <CONDITION>.
SELECT <FIELDS> FROM <DBTABLE2> INTO <ITAB2> FOR ALL ENTRIES IN <ITAB1>
WHERE <FIELD1> = <ITAB1>-FIELD1.
**HERE WE ARE READING DATA 2 DATABASE TABLES, SEE WHERE CONDITIONS OF SECOND SELECT STATEMENT

Ensure before using SELECT FOR ALL ENTRIES

Parent internal table must not be empty ( If it is empty, where condition fails and it will get all records from database).
Remove all duplicate entries in parent internal table.

Here is the example of using SELECT FOR ALL ENTRIES in real-time applications

**DATA DECLERATIONS

DATA : IT_MARA TYPE TABLE OF MARA.

DATA : IT_MAKT TYPE TABLE OF MAKT .

**GET DATA FROM MARA TABLE

SELECT * FROM MARA INTO TABLE IT_MARA

 WHERE MATNR = '0001'.

**SORT MARA TABLE, TO DELETE ADJACENT DUPLICATES THE TABLE MUST BE SORTED IN ASCENDING ORDER

SORT IT_MARA ASCENDING .

**DELETE ADJACENT DUPLICATES FROM IT_MARA COMPARING ALL FIELDS

DELETE ADJACENT DUPLICATES FROM IT_MARA COMPARING ALL FIELDS.



**TO USE SELECT FOR ALL ENTRIES, THE PARENT INTERNAL TABLE MUST NOT BE EMPTY SO CHECK IT

IF IT_MARA IS NOT INITIAL. "CHECK PARENT INTERNAL TABLE 

**SECOND SELECT STATEMENT TO GET DATA FROM MAKT (MATERIAL DESCRIPTIONS), HERE WE ARE GETTING DATA

**FROM MAKT FOR ALL THE RECORDS IN IT_MARA, SEE WHERE CONDITION SHOULD BE PARENT INTERNAL TABLE 

SELECT * FROM MAKT INTO TABLE IT_MAKT

 FOR ALL ENTRIES IN IT_MARA

 WHERE MATNR = IT_MARA-MATNR.

ENDIF.


Select into corresponding fields




Select into corresponding is used to get data from a data base table into a user defined internal table or work area with out specifying the list of fields.
Syntax: SELECT * FROM <DATABASE TABLE>
        INTO CORRESPONDING FIELDS OF TABLE <INTERNAL TABLE> .
As per SAP standards select into corresponding statement is not advisable as it effects the performance of an application because it has to compare each field with database.
Example using select into corresponding

The below example explains how to use select into corresponding statement to get data into a database table with out specifying list of fields in select statement.
REPORT ZSAPN_SELECT_CORRESPONDING.
TYPES : BEGIN OF TY_MARA,
 "USER DEFINED TYPE WITH FOUR FIELDS
        MATNR TYPE MARA-MATNR,
        MTART TYPE MARA-MTART,
        MBRSH TYPE MARA-MBRSH,
        MEINS TYPE MARA-MEINS,
        END OF TY_MARA.
DATA : IT_MARA TYPE TABLE OF TY_MARA . "INTERNAL TABLE FOR USER DEFINED TYPE<br>DATA : WA_MARA TYPE TY_MARA . "WORK AREA FOR USER DEFINED TYPE<br>SELECT * FROM MARA
       INTO CORRESPONDING FIELDS OF TABLE IT_MARA
       UP TO 50 ROWS.<br>LOOP AT IT_MARA INTO WA_MARA.
WRITE :/ WA_MARA-MATNR, WA_MARA-MTART, WA_MARA-MBRSH, WA_MARA-MEINS.  "DISPLAY OUT PUT<br>ENDLOOP.

Data Dictionary

SAP Data Dictionary is a central repository for the development of Objects, SAP Data Dictionary is used to create and maintain meta data ( data definitions ) .

T-Code for Data Dictionary is SE11.

SQL Can be divided into two parts

DML -- Data Manipulation Language
DDL -- Data Definition Language

Where DML consists of SELECT, INSERT, UPDATE, MODIFY, DELETE etc, this will be handled by ABAP Programs and DDL consists of commands like CREATE TABLE, DROP TABLE, ALTER TABLE, CREATE INDEX etc, this will be handled by Data Dictionary, hence Data Dictionary is the central repository for the development of objects .
The Main Functions of Data Dictionary are :

Data Base Tables
Views
Data Elements
Domains
Structures
Search Helps
Lock Objects

An ABAP Consultant may need to work on below areas of data dictionary :

Creating Tables
Creating Data elements and Domains
Creating structures and Table types
Creating search helps
Creating Lock objects
Creating data base views

Data Types-Domains-Data Elements

ABAP uses different types of Data Types for Objects, basically there are three types of data types Elementary Data types, Complex Types, Reference Types .

In Elementary types there are again two types of data types( Categories ) Fixed Length and Variable Length .
Fixed Length:

C Text Field
N Numeric
D Date
T Time
H Hexadecimal
P Parked Number
I Integer
F Float

Variable Length

STRING Character Sequence
XSTRING Byte Sequence

Complex Types includes Structure Types and Table Types.
Reference Types includes Data References and Object References
What is a Date Element in SAP ABAP ?
Data element is an object which specified semantic information of a field, ex: field label , heading.(
We all know that table is a collection of fields, ex: STUDENT is a table with fields STUDENT_ID, STUDENT_NAME ext, data element specifies the field labels and headings for these fields )

What is a Domain in SAP ABAP ?
Domain is an object which specified technical information of a field in a table ex: Data type, length, value range etc

Why we use Data elements and Domains in SAP ?
When ever we are creating a new table or adding a field to existing table, we need to specify field labels and data types for the field, in SAP we maintain these values in the form of Data elements and Domains.

Components of SAP Tables

Before creating a table we must know components of Table.

What is a key field ?

A field which doesn`t accept duplicate data is called as key field.

What is a delivery class ?

It specifies the type of data stored in a data base table ex: master data, Transaction data, system data etc

Options available under delivery class :

A- Application Table (Master and Transaction data ).

C-Customizing table, maintenance only by customer not SAP.

L-Table for storing temporary data.

G, E, S, W - These are used to store system data (Basis data ).

Display and Maintenance

It specifies whether the user can display the data and maintain the data , the following options are available under display and maintenance.

Display/Maintenance Allowed.- User can display and maintain data.
Display/Maintenance allowed with restrictions - Some users can display and maintain data.
Display/Maintenance not allowed - No user can display data.

What is data class ?

It specifies the physical area of a table in the database, the following options are available under data class.

APPL0 - Master data

APPl1 - Transaction data

APPL2 - Organization and Customizing

DDIM, DFACT, DODS etc are used by BI and BW.

What is size category ?

It specifies the number of records that can be stored in a database table, options available under size category are below.

Size Category	No of expected records
0	0 - 7,100
1	7,100 - 28,000
2	28,000 - 1,10,000

The above numbers may vary from company to company .
Size category will be incremented dynamically based on increase in records.

Buffer

It is a temporary memory for storing the data .

It is same as cache memory.

The functionality of buffer is defined below.

Types of database tables in SAP

There are three types of tables available in SAP, explained below

Transparent table

There is a physical table on the database for each transparent table. The names of the physical tables and the logical table definition in the ABAP/4 Dictionary correspond.

All business data and application data are stored in transparent tables.

Pooled table

Pooled tables can be used to store control data (e.g. screen sequences, program parameters or temporary data). Several pooled tables can be combined to form a table pool. The table pool corresponds to a physical table on the database in which all the records of the allocated pooled tables are stored.

Cluster table

Cluster tables contain continuous text, for example, documentation. Several cluster tables can be combined to form a table cluster. Several logical lines of different tables are combined to form a physical record in this table type. This permits object-by-object storage or object-by-object access. In order to combine tables in clusters, at least parts of the keys must agree. Several cluster tables are stored in one corresponding table on the database.

Difference between structure and table in SAP data dictionary

Table and structure have lot of differences in the sense of behaviour and properties, the below are the main differences between table and structure in data dictionary.

Table and Structure Difference

Table	Structure
Table can store data physically and permanently.	Structure can not store data physically, it can hold data at run time only.
Table have primary key.	Structure dosent have any primary key.
Table have technical settings (data class, size category etc), maintanance attributes ( Display maintanance restrictions ).	Structure dosen`t have any technical attributes and maintatance attributes.
Table have primary and secondary indexes.	Structures dosen`t have primary and secondary indexes.

Difference between Transparent, pooled and cluster tables in SAP

The below are the major differences between transparent, pooled and cluster tables in SAP data dictionary .

Transaparent Tables	Pool Tables	Cluster Tables
Contain a single table. Used to store master data	They are used to hold a large number of very small tables(stores system data).	They are used to hold data from a few number of large tables.(stores system data).
It has a one-to-one relationship with a table in the database.	It has a many-to-one relationship with a table in the database.	It has a many-to-one relationship with table in the database.
For each transparent table there is one associated table in the database.	It is stored with other pooled tables in a single table called table pool in the database.	Many cluster tables are stored in a single table in the database called a table cluster .
The database table has the same name, same number of fields and the fields have the same names .	The database table has different name, different number of fields and fields have different names.	The database table has different name, different number of fields and fields have different names.
There is only a single table .	Table pools contain more tables than table clusters .	Contains less tables than table pools .
Secondary indexes can be created .	Secondary indexes cannot be created .	Secondary indexes cannot be created .

Table Validations using check table

What are table validations ?

Table validation is a concept through which we can restrict invalid entries in a table.

Possible methods/concepts for table validation

1. Field Level validations: We can validate entries at field level with the help of check table concept.

2. Domain level validations: We can restrict entries at domain level with the help of fixed values of domain and value table of domain.

What is check table ?

It is a table which contains all valid entries of a field.

What is value table ?

It is a table which contains all valid entries of a domain, this domain can be reused in multiple tables.

What are fixed values for domain ?

These are allowed values for a domain ex: GENDER (domain).

Creating check table for table validations.

Step1: Go to SE11, create a table ZSTUDENT_CHECK as below

Go to SE11, provide name as ZSTUDENT_CHECK and click on create.

Provide delivery class and display maintenance allowed.

Go to fields tab and add field as below

Click on technical settings button and provide data class and size category.

Go to utilities- table contents - create entries add some entries to the table.

Similarly add some more entries.

Step2: Add ZSTUDENT_CHECK as check table for ZSTUDENT_TABLE

Go to SE11, provide table name as ZSTUDENT_TABLE( which we have created already in our previous lessons, if not create a table )

Go to tab Entry help/check, select city fields and click on foreign key icon.

Provide short text, check table name ZSTUDENT_CHECK and click on generate proposal and click on copy.

A check table will be added for CITY field

Save and activate the table(If you get a warning popup when activating just press no).

Now go to utilities - table entries -> create entries

Provide a city name which is not in ZSTUDENT_CHECK table and try to save, it will trough an error.

Fixed Values for domain in SAP

SAP Table screen entries can be restricted using domain fixed values, we can maintain domain fixed values at domain level when creating a domain.

Create domain with fixed values

Go to SE11, select domain, provide name as ZGENDER, click on create.

Provide short description, data type and length as below and click on value range tab.

Provide fixed values.

Save and activate.

Create data element with domain

Go to SE11, select data type, provide name as ZGENDER, click on create .

A popup will come, select data element and press enter.

Provide short description, domain and click on field label.

Provide field labels

Save and activate

Add a field in table

Go to SE11, provide a table name ZSTUDENT_TABLE(we have already created, if not create table ) and click on change

Go to fields tab and add a field as below.