SQL STATEMENTS IN SAP(ABAP)

SQL STATEMENTS

Under ABAP language we can work with SQL Statements

                     

OPEN SQL statements	NATIVE SQL
Syntax: Select * from emp . . . endselect. /*endselect is must 1)once executed prog(open sql), it can process the interface logic	Syntax: execsql Select * from emp . . . endexecsql. 1.) in case of native sql there is no interface logice b/w application server and database server

• NATIVE SQL doesn’t support cluster and pooled tables

Open SQL: while extracting data  in case of open SQL it can support for transparent cluster and pooled

• In case of Native SQL it can support Transparent tables only
• So OPEN SQL is better bcoz it supports all the tables

                                                 These are prog’s statements

Transparent tables : uses can work with Open Sql and native SQL it is cluster or pooled tables user can work with OPEN SQL only

Application  indicates a transparent tables.

Size category : 1 ………………….9

                            (G1000)          (9x61000)

2- indicated user can maintain max upto 6100 records.

* size category provides the size of the data which table maintains.

Buffering concept :

v     Buffering switched on

v     Buffering not allowed

v     Conditional buffering.

v     Buffering switched on but not allowed

            Buffering switched on means all records stored in application server and database server i.e. when buffering switched on that buffer maintains in application server.

In Real time conditional buffering is used for storing the records.

Maintenance allow : it provides authorization to the tables.

Enable the checkbox allows directly store records in Front-end as well as database.

Disable checkbox allows only front end.

1.) BUFFERING SWITCHED ON

§         Make it while creating database table once I switched on buffering

§         We have SAP R/3 imagine R/3 Application server, having R/3 Database server

§         If u look into Database server having zemp

§         under zemp having fields : eno &ename

§         and having data like eno:1,2,3 and in ename: rajendra, ravi, ajit

§         and emp1 is permanent employee, remaining are not permanent employees

NOTE:

Whenever Buffering is switched on

• respective buffer can maintain in Application server
• which Buffering is maintain in Application server holds a data parallels with R/3 database

DELIVERY CLASS functionality

Delivery class : provides type of date which tables maintains data there are 3 types.

1. master data (Eno. Ename etc)
2. Transactional data (salary data)
3. control data (Dept data)

A :  indicates master & transactional data. 

TRANSPARANT TABLE:

ü      From those list if we look into TRANSPARANT TABLE

TRANSPARANT TABLE:

      Having application server and having db server

      We can find ABAP dictionary in application server

      Database table(eno, ename) is nothing but transparent table

• If it is Transparent table, in that case structure in ABAP dictionary is same as structure in database

ü                                      Having SAP R/3 database and having 5000 transparent tables

ü                                      Working under web based

ü                                      Layer is nothing but clustard layer

Using CLUSTER & POOLED tables performance can be improved in SAP R/3.

§                                         CLUSTER follows binary search

§                                         POOLED follows Linearly search.

§                     Apart from a data class we can work with size category

Size category: it can provide size of the data which respective table maintains

Þ                                      If  I say zero(0) under respective table upto 61000 records

Þ                                     apart from zero(0) even we have the option 1 to 9(size category)

Cluster & pooled tables

Cluster & pooled tables

these are used in HR- ABAP

v     Mainly used for performance

v     Clustering in nothing but group of fields from different from parent tables. And it follows ‘Binary search’

v     Pooled table also some as cluster but it follows linear search

                                      The main difference between clustering table and passed table is binary search and linear search.

ü      Under ABAP dictionary we worked with a data class it can be provide tables which we r created

ü      If u look into SAP R/3 we can classify

1.   Transparent table

2.   Cluster table

3.   Pooled table

ü      From those list if we look into TRANSPARANT TABLE 

DATA CLASS AND TYPES OF TABLES

DATA CLASS

Data class : we are providing it as Application it means transparent tables.

Types of tables.      1. Transparent tables

                                          2. Cluster tables

                                          3. pooled tables.

                      It is transparent table structure in ABAP dictionary is same as structure in database 

Database tables

Introduction to Data Dictionary

A data dictionary is a centralized storage location for information about the data that is stored in a database.  This information is often called “metadata” (data about data).
SAP’s data dictionary is called the ABAP Dictionary.

A data dictionary provides answers to questions such as:

•  What data is contained in the database?

•  What are the attributes of this data: name, length, format, etc.?

•  What relationships exist among different data objects?

The ABAP Dictionary:

• Enforces data integrity

• Manages data definitions without redundancy

• Is tightly integrated with the rest of the ABAP Workbench

When data integrity rules are defined in the ABAP Dictionary, the system automatically prevents the entry of invalid data. Defining the data integrity rules at the dictionary level means they only have to be defined once, rather than in each program that accesses that data.
The following are examples of data lacking integrity:
-> A date field with a month value of 13
-> An order assigned to a customer number that doesn’t exist

Additionally, the system provides easy navigation between development objects and dictionary definitions. For example, if you can double-click on the name of a dictionary object in your program code, the system will take you directly to the definition of that object in the ABAP Dictionary.
When a dictionary object is changed, a program that references the changed object will automatically reference the new version the next time the program runs.  Because ABAP is interpreted, it is not necessary to recompile programs that reference changed dictionary objects.

Objects of Data Dictionary

The basic objects of the ABAP Dictionary are tables, data elements, and domains. These form the active part of the ABAP Dictionary and contain all the field-related metadata of the R/3 System.
A field is not a dictionary object, but rather is a component of a table. A field cannot exist without a table and only has meaning within that particular table.
Data elements and domains are dictionary objects. Therefore, they can be used by many tables. They specify the characteristics of fields.


Tables are the objects that actually hold the information in a database.  They consist of rows (records) and columns (fields).
For example, table KNA1 stores information about customers.  Some of the columns in KNA1 are KUNNR (customer ID number), NAME1 (customer name), and ORT01 (customer city). Each row in KNA1 stores this information for a different customer.
Certain fields in a table are specified as the primary key of that table.  The primary key is that field or combination of fields that uniquely identifies a row in the table.  In table KNA1, the SAP R/3 client (MANDT) and the customer number (KUNNR) form the primary key.


The database utility provides the interface between the ABAP Dictionary and the underlying database management system (DBMS).  It supports the creation of tables and secondary indices in the database both online and in the background.
Whenever you make a change to a dictionary object that affects the underlying database, the database utility is activated.  Usually it works silently behind the scenes, but occasionally the database utility will prompt you for information.  This occurs when an error is encountered or when existing data must be converted.
The database utility provides the interface to the DBMS by automatically generating the Data Definition Language (DDL) that the DBMS understands

Data Elements

The meaning of a field name is not always obvious. For example, KUNNR may not immediately bring the image of a customer to your mind.  Data elements help resolve this problem.
A data element provides a meaningful description for a field. You will hear it called a semantic domain.  The data element description appears beside a field in a table definition. These descriptions are language-dependent
(unlike field names).
Even more importantly, the data element provides field headings for use on screens. When you “paint” a dictionary field on a screen, you can automatically have the data element field headings appear. This permits the end user to see meaningful field descriptions.
The advantage of using a data element to describe fields and provide field headers is that the data element can be used more than once.
For example, SAP R/3 contains many tables that have the field KUNNR. In cases like this, it is not necessary to enter the description for KUNNR many times.  Each instance of KUNNR can be assigned to the same data element, and the field description only needs to be specified once. Additionally, if the description needs to be changed, it must be changed only once, and all fields referring to that data element automatically use the new description.

SAP R/3 comes delivered with many pre-defined data elements. Whenever the semantic description of a field you are creating matches with an existing SAP R/3- supplied data element, use the one that SAP R/3 provides. Otherwise, you must create your own data element.
When using a SAP R/3-supplied data element, however, you must also use the corresponding SAP R/3-supplied domain. If you wish to use a different domain, you must create your own data element.

In addition to providing field descriptions and headers, data elements can provide detailed field documentation. This documentation is maintained at the data element level and is available to a user who hits F1 when the cursor is in a field assigned to the data element.
To maintain this documentation, navigate to the Change Data Element screen within the ABAP Dictionary and click the Documentation push-button.

Domains

The definition of domain in ABAP is same as that in Mathematics.
In the world of mathematics, a domain is defined as the set of input values that are valid for a given function. The same holds true for domains in ABAP. To understand this, lets first see what is the role of domains in ABAP data dictionary.
Domains -> Data Elements -> (Structures, DB tables, program references etc.)
Domains are attached to data elements and data elements are inturn attached to structures or tables. Lets say we have a field called 'Gender'. The valid gender types will be 'Male' and 'Female'. Thus, for domain 'Gender' the values will be 'Male' and 'Female'. Now this domain can be attached to a data element which in turn can be used in may structures / tables.
Take an example where you want to store the employee master data in a table. This table has a gender field also. Now every time you enter a data record in this table you would like to validate the gender field (should only be either male or female). To do this validation everytime in program could be tedious job. Instead define the domain values appropriately and let the ABAP kernel handle it.
Now let's say you create another table to store the data of passengers. This table will also have a gender field. So instead of creating duplicate data types, the same data element can be used. Here again the validation will happen automatically because the domain used is same.
Now one can imagine that there need not be always a set of fixed values for a domain. Yes, a domain can have a range of values also. It could also point to a value table. Thus any entry will then be validated against a table.

Foreign Keys

Relationships between tables are represented in the ABAP Dictionary by foreign keys. A foreign key is a field (or combination of fields) that represents the primary key of another table.
For example, if table YORDERS has a field CUSTID indicating which customer placed the order, that field could be established as a foreign key (assuming that CUSTID was the primary key of the YCUSTOMERS table).  Note that CUSTID is not necessarily part of the primary key of the table YORDERS.

• The table that is referenced by the foreign key (in our example, YCUSTOMERS) is called the check table. The check table is also known as the “referenced” or “parent” table.

• The table that contains the foreign key fields (in our example, YORDERS) is called the foreign key table. The foreign key table is also known as the “dependent” or “child” table.

Foreign keys are used for:

•  Maintaining data integrity

•  Providing additional texts in the online help system

•  Creating other dictionary objects that are defined over multiple tables (such as views)

Maintaining data integrity is probably the most important reason to use foreign keys. With foreign keys, it is possible to prevent values from being entered into one table that do not already exist in another table. For example, it becomes impossible to enter orders that do not have valid customer numbers. In this way, foreign keys are similar to domain value tables.

Let’s review some of the key terminology relating to foreign keys.
Value table: The table containing the set of allowed values attached to a domain.
Check table: The table that is referenced by a foreign key. A check table is either identical to a value table, or is another table containing a subset of the records in a
  value table.
Foreign key table: The table containing fields that are the primary key of the other table. The foreign key table is also known own as the “dependent” or “child”
  table.
Using Foreign Keys, you can :  Create value checks for input fields and  Link several tables in a view or in a lock object.
To see a full list of the allowed values for a screen field, place the cursor inside the field and hit the F4 key.

Cardinality

When creating foreign key relationships, you should always specify the cardinality of that relationship. Here is a reminder of the possible values for each side of the n : m  notation that SAP uses to specify cardinality.
For the left side:
 n = 1    Each record in the foreign key table refers to exactly one record in the check table.
 n = C    Each record in the foreign key table refers to zero or one records in the check
 table.
For the right side:
 m = 1     Each record in the check table has exactly one dependent record.
 m = C     Each record in the check table has a zero or one dependent records.
 m = N     Each record in the check table has at least one dependent record.
 m = CN   Each record in the check table has zero, one, or many dependent entities.

If the primary key of a check table has multiple fields (i.e. it has a composite primary key), some type of assignment must be made for each field when creating a foreign key relationship.
The options include:

• Creating a field-by-field assignment.  Every primary key field in the check table is matched with a field in the foreign key table.

• Using a partial foreign key.  Some fields will not be a determining factor in deciding what check table records provide acceptable values for the foreign key field being checked.

• Using a constant foreign key. Only check table records with a particular constant (literal) value in a particular field provide acceptable values for the foreign key field being checked.

Tables in Relational database

There are 5 different table types in the SAP ABAP Dictionary:

• Transparent tables

• Structures

• Pool tables

• Cluster tables

• Views

Data in the SAP R/3 system is stored in a relational database management system (RDBMS).  All tables in the SAP R/3 system are created using the ABAP Dictionary and are stored in the relational database.  All structures and certain views are also created in the ABAP Dictionary but are not stored in the RDBMS. The ABAP Dictionary is SAP’s tool for developers to maintain any type of table that exist in the physical database.
SAP uses the term master data to refer to control tables and the traditional files that are necessary to run a business such as personnel files, general ledger accounts, customer files, etc. There is a 1:1 correlation between the master data tables  in the Dictionary and the tables in the physical database. For each master data table in the Dictionary, the same master data table exists in the physical database. Commercial data or transaction data is another SAP term used to refer to data created as a result of performing SAP business transactions such as creating: invoices, orders, production schedules, purchase requisitions, stock transfers, etc.
SAP stores both master data and transaction data exclusively in transparent tables (TRANSP).
Transparent Tables
Transparent (TRANSP) tables have a 1:1 correlation between the ABAP Dictionary and the physical database. For each TRANSP table in the Dictionary, the same table name exists in the physical database.
Since TRANSP tables exist in the physical database, you can use either Open SQL or Native SQL to access them. Transparent tables are created automatically in the database after you specify the table’s technical settings and activate the table using the Database Utility. The Database Utility gets invoked automatically when you activate the table
INTTAB tables are field strings (structures) which do not hold data. Therefore, they are not mapped to the database and have no underlying database tables.
INTTAB tables must be activated just like TRANSP tables. However, no technical settings are required and the Database Utility is not automatically invoked when you click the activate icon since INTTAB tables do not exist in the database.
Structures can be used in multiple tables. They help avoid redundant field definitions throughout the system. Structures can be nested up to nine levels and can contain a maximum of one table. They are similar to the copybook function of other programming languages.
Pool and Cluster tables
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 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.
Technical Settings
Technical settings allow you to optimize the storage requirements and table access behavior of database tables.
Data class - Designates the table to an area in the physical database where similar tables are grouped (in ORACLEä and INFORMIXä only).
Size Category - Identifies the amount of disk space that will be required to hold the data records for a table in the database (in ORACLEä and INFORMIXä only).
Buffering - Determines whether table records will be accessed directly from the database server or from global memory.
Logging - Creates before and after images of changes to the table of contents. Logging must be activated by the profile when the system is started.
Data Class
Tables in the ABAP Dictionary must be assigned to one of the following classes of data:
Master Data: Large amounts of data which do not change often.  It is often read, but rarely updated. An example of master data is the data contained in an address file, such as the name, address and telephone number.  (APPL0)
Transaction Data: Data with temporary lifecycle, not stored long. Frequently
 updated. An example of transaction data is the goods in a warehouse, which change
 after each purchase order.  (APPL1)
Organization and Customizing Data: Specified when the system is configured and then not often changed. An example is the table with country codes.  (APPL2)
Two further data classes, USER and USER1, are provided for the customer. These are used to define user developments. Defining a data class has the effect of storing the table in a defined area of the database when the table is created.
Size Categories
A table’s size category identifies the amount of disk space on the database that is allocated to the table. 
This amount is translated to a number of data records if you hit F4 for help, depending on the underlying database. If the number of records in a table exceeds the original size category, then more space will automatically be allocated in the database. The storage space will be incremented by the amount of the original size category.
Buffering type
Buffering is only recommended for tables with data that typically does not change or get updated. Buffering types:
Single record: Only records actually being processed are moved into
the buffer. This type of buffering preserves buffer space but requires
more database hits in order to load the table. Recommended for large
tables when only a few records need to be accessed.
Generic: A subset of the table records is loaded based on part of the primary key. Recommended if only certain “generic” areas of the table
will be needed.
Full: Results in either all of the table or none of it being loaded into the buffer. Recommended for a) tables up to 30 K in size, b) larger tables where access is needed to many records, and c) tables against which attempts to access data will frequently yield a “no record found” result.
Logging
On the technical settings screen you can specify “Log data changes” to automatically generate a log file of details on the structure of a table, a list of the data changes made during a certain period, and statistics.
In addition to clicking the checkbox “Log data changes” on the technical settings screen, you must ensure that the System Administrator has  also specified the switch or entry to allow “Table Logging On”.
Log files do not rely on successful database updates to be completed before they are written.
Database Utility and Indexes

Database Utility

The Database (DB) Utility is a tool used in SAP to serve as a interface between the database management software (i.e., ORACLE, INFORMIX, DB2, INGRES, etc.) and the ABAP Dictionary.  It is used to:

•  Convert data (i.e., change field lengths and data types, etc.)

•  Activate objects in the ABAP Dictionary

•  Create tables and indexes

•  Perform all standard table operations in the database that were entered in the ABAP Dictionary

The DB Utility automatically writes the SQL commands necessary to create, change and delete tables and indexes in the physical database, and records the tables in the ABAP Dictionary.
As the DB Utility is operating, a log file gets created which contains information on whether or not the conversion was successful and the point of failure during the conversion if it was not successful. The  DB Utility can be run either online or in the background. You can also manually run the DB Utility from any ABAP Dictionary screen under the UTILITIES  menu or transaction SE14.
Indexes
To improve performance, SAP automatically creates a primary index (id 0) for transparent tables based on the primary key. You can also define your own secondary indexes for transparent tables.
Indexes accelerate the reading of tables when the system looks for records satisfying specific search criteria. The system determines the most efficient index by which to select data for the specific request. An index serves as a sorted copy of the table reduced to specific fields, with a pointer to the remaining fields. 
Database indexes are defined ABAP Dictionary and stored in the physical database. From the ABAP table maintenance screen use the menu path GoTo->Indexes.
A pop-up window appears. Assign a 3-character id to your index. Provide a short text and select the field(s) by which the table needs to be indexed.
Sometimes the presence of an index causes a performance problem. You can indicate the optionality of the index with different databases. Creating an index on an SAP table requires a repair, but it will not get overwritten with an upgrade.

In the cases where database accesses are necessary and appropriate, it is imperative to perform those accesses as efficiently as possible. The single most important method of optimizing a database access is by using an index.
An index is a set of fields from a table that is sorted and then stored in a location separate from the table itself. Each record in the index contains a pointer to matching record(s) in the actual database table.
In contrast, if each index record matches exactly one record in the table, and if all the fields of the index are specified in the query, a unique index scan can be performed.  Queries based on a table’s full primary key always fulfil this criterion - such as by client (implicit if using Open SQL) and customer number in table KNA1.
In this case, once the DBMS finds the matching record in the index, its work is almost done.  All it must do is follow the pointer from the index to the solitary table record that it knows will satisfy the query.

In general, indexed reads are much quicker than normal table reads, and some types of indexed reads are quicker than others.  A unique index scan is generally faster than an index range scan, because it has less data to sort through and retrieve.
The smaller the amount of data being processed by a query, the faster it will run.
Here are some guidelines:

•  Always make your queries as selective as possible.

•  Use indexed reads over full table scans.

•  Use unique index scans when possible.

Database tables : under database tables we have following

1.      Date class

2.      size category

3.      Buffering concept

4.      maintenance allow

5.      delivery class.

Flat File

Flat File : is nothing but a text file or excess sheet after completion of logic flat file transfer to Application server

STRUCTURE CREATION STEPS (Up gradation or Adding a fields)

Up gradation or Adding a fields

* In SAP R/3 it is not possible for adding the fields directly to SAP tables. With database structures uses can add a fields for SAP tables.

            The previous program

Using 3.1F SAO version ad have 100 fields vendor table and vendor application. Up gradation takes place when new version coming into market then it will upgraded by using conversion presentation is applicable server to new version i.e. 4.7 and put it database servers as it is in any type of technology. If we wand to add some fields to already existing database table i.e. vendor table we can add it by using structure and following keywords.

INCLUDE (or) APPEND or the keywords for adding a structure to the tales.
*******************************************************************************

STRUCTURE CREATION STEPS  : -

§                                             Go to ABAP dictionary in which

§                                             Select (1) Data type and give name of YSTR  structure

§                                             Go for CREATE it will display a option then

§                                             select (1) Structure then   enter  it display a window in which provide shorttext for structure  Structure for Yemp

§                                             Provide component (fields in a structure ) Address what we want to add

§                                             provide component type (Data element) : ZADDRESSDATA

§                                             Ctrl +s for saving

§                                             Leave Development class  as    Blank

§                                             Go for Local object then

§                                             Double click on dataelement i.e. ZADDRESSDATA what we gave component type previous step

§                                             Select Dataelement 

§                                             Enter

§                                             Provide shorttext for dataelement   Dataelement for ADD

§                                             Provide domain ZADDRESS DOMAIN

§                                             This domain is not yet created we create it in corning steps so we create by using to domain name

So, whatever domain name given in this box we remember for to create domain in coming steps.

         Then  select   field label option in that window then provide field label length i.e.

Medium 20 EMPLOYEE ADDRESS fields label name

§               Ctrl +s

§               leave Development class as blank

§               Go with Local object then

§               Select Definition option in that window.

§               Double click in domain i.e. ZADDRESSDOMAIN   - enter

                                                            (Just we created in above)

§               Provide short text for domain Domain for ADD

§               Provide datatype : CHAR

Length : 30

§               Ctrl+S

§               Ctrl + F3 then F3 for back

§               Activate Data element then F3 for back

§               F3 for back

§               Activate structure

§               Go with ABAP Dictionary

§               Select database table YEMP which we created in last class i.e. for which table we want to add a structure.

§               Go to change mode

§               Go with New rows

§               Apply logic for including a field i.e.

INCLUDE                                    YSSTR   

§               Enter

§               Ctrl+S

§               Ctrl+f3

§               Go to utilities from menu

§               Choose Table contents

§               Go for Display

§               Then press F8 for execute then it will the inserted recorder of that fields.

v                 Once we add a structure to the table it can holds data different b/w . include and. Append. :

Here we have 3 tables and we can add a field DNO, ENO ENO, End respectively. Here we can use two structure for add that field bec ENO fields is reusable so

With – APPEND structure will restrict for specific tables only

With – include structure can relies different tables and include is a keyword

Þ     In one table we can add 9 structures

We can add structures with in structure also.

How to maintain previous data properly.

 

Download the file to update from database to presentation server

         We have to maintain all records in proper way after add a field through structure we can follow a above logical for that

v      First we download the records which are we going to update

v      Sap provide some program for that logic in presentation server

v      Download record will store in flat file or text file

v      Flat file can be transferred to application server by using BDE

v      Then we can update a record and transfer to database.

Flat File :

 is nothing but a text file or excess sheet after completion of logic flat file transfer to Application server