SQL

SQL: Structure Query Language.

Whenever we want to store the data into database tables, we need to use the respective database language statements.

E.g. ORACLE database understands oracle statements.

SQL can be divided into following 2 parts.

DML – Data Manipulation Language

DDL – Data Definition Language

DML part consists of query and update commands like SELECT, UPDATE, DELETE, INSERT etc. ABAP programs handle DML part of SQL.

DDL part consist of commands like CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX etc. ABAP Dictionary handles DDL part of SQL.

Open sql statements:

The statements which is converted by r/3 interface layer from abap to respective database statements are called open sql statements. E.g. Select, Delete, Update, Modify, Insert.

Native sql statements:

These statements directly communicate with database without interacting with r/3 intrface layer are called native sql statements.

R/3 interface layer: Is used to convert the abap language statements into respective database statements.

All open SQL statements are passed to the database interface. The DB interface converts the open SQL to native SQL and passes it on to the database.

List of Open SQL statements

OPEN SQL	DESCRIPTION
SELECT	Reads data from database
INSERT	Inserts lines to database
UPDATE	Changes the contents of lines in database
MODIFY	Inserts lines into database or changes the contents of existing lines
DELETE	Deletes lines from database

All Open SQL statements fill the following two system fields:

SY-SUBRC – After every Open SQL statement, the system field SY-SUBRC contains the value 0 if the operation was successful, a value other than 0 if not.

SY-DBCNT – After an open SQL statement, the system field SY-DBCNT contains the number of database lines processed.

SELECT is the open SQL statement to read the data from the database. The general syntax for SELECT statement is as follows.

SELECT    
   INTO    
  FROM    
[WHERE    ]

CLAUSE	DESCRIPTION
SELECT	Specifies which columns you want to read, whether one line or many lines needs to selected, and whether duplicate entries are allowed
INTO	Determines the target area into which the selected data is to be placed
FROM	Specifies the database table from which the data is to be selected
WHERE	specifies which lines are to be read by specifying conditions for the selection

Select with joins:

This can we used to fetch the data from multiple database tables too programmatically into one internal table using select statement.

We have two types of joins.

1.      Inner join:

It will retrieve the common entries from different data base tables which are used in joint condition.

E.g.

MARA                                        MARD

100                                               100

101                                               103

102                                               104

After using Inner join statement output is

100

2.      Outer join:

It will use very rarely. Left outer join retrieve all entries from left side table & its match only from right side table entries.

E.g.

MARA                                        MARD

100                                               100

101                                               103

102                                               104

After using left outer join statement output is

100

101

102

103

104.

Select for all entries:

Is used to replace the select with joins.

Because joins statement can’t use for more than three tables.

1.      Parameter: is used to create a single input, checkbox, radio button.

2.      Select-options: is used to create two input fields for entering a range of values. Or is a selection screen which will allows user to give inputs more than one value dynamically.

Select-option create internal table with header line. The four fields are

a.       Sign: I-including, E-excluding

b.      Option: BT, NB, EQ, NE, LT, LE, GT, GE.

c.       Low

d.      high