Create SAP SQL Query


How to Create SAP SQL Query

SAP ABAP Course for 2020

SAP ABAP Fundamentals

SAP ABAP Interview Questions – Real Time

 

SELECT STATEMENT AND IT’S TYPES:

The select statement is used to read data from one or more database tables.
There are different types of SELECT statements:

– SELECT SINGLE

– SELECT UPTO n ROWS

– SELECT with JOINS

– SELECT FOR ALL ENTERIES

– SELECT INTO CORRESPONDING FIELDS

– SELECT DISTINCT

– SELECT with BYPASSING BUFFER

– SELECT APPENDING

SELECT SINGLE:

SELECT SINGLE statement is used to read exactly the same data from a database table.

To read exact data using this statement we need to specify the key field in the WHERE condition.

There is no need to keep data in order by using the ORDER BY clause.

SYNTAX: SELECT SINGLE fields FROM TABLENAME INTO WA WHERE ALL KEY FIELDS.

SELECT UP TO n ROWS:

This statement limits the number of records to be fetched from the database table up to n rows.

This statement can only be specified after an ORDER BY clause.

Without using the ORDER BY clause, SELECT UPTO 1 ROWS will give the same result as SELECT SINGLE.

In this statement, we don’t need to specify the key field in the WHERE condition.

SYNTAX: SELECT fields FROM TABLENAME INTO WA UPTO 1 ROWS WHERE ALL KEY FIELDS/SOME KEY FIELDS.

ENDSELECT.

 

SELECT with JOINS:

This statement is used to read data and joins the columns of two or more database tables or views.

A JOIN statement joins a left side with a right side using either (INNER JOIN or LEF/RIGHT OUTER JOIN). Every JOIN statement must contain the join condition [ON syntax].

INNER JOIN:

INNER JOIN reads and joins the column of the rows and stores the data in the internal table of the left side columns with the right side only when columns meet the condition, otherwise shows no result.

OUTER JOIN:

OUTER JOIN gives the same result as the INNER JOIN. But the difference is that it does show data regardless of the join condition. This statement is like a UNION it fetches the data of either one side of the data and common data from the other side.

There are two types of OUTER JOIN:

 LEFT OUTER JOIN:

This syntax fetches every data from the left-hand side table and common data from the right-hand side table.

RIGHT OUTER JOIN:

This syntax fetches every data from the right-hand side table and common data from the left-hand side table.

Syntax : SELECT fields FROM tablename AS LHS_tablename LEFT OUTER JOIN RHS_tablename ON join_condition.

Use of  FOR ALL ENTRIES:

SELECT FOR ALL ENTRIES is the best alternative for SELECT WITH JOINS.

Things to keep in mind before using SELECT FOR ALL ENTRIES:

The parent table must not be empty, otherwise where the condition will fail.

Remove all duplicate entries from the parent internal table.

Syntax:          

SELECT fields FROM tablename1 INTO TABLE internaltable1

WHERE condition.

SELECT fields FROM tablename2 INTO internaltable2 FOR ALL ENTRIES IN internaltable1

WHERE fields1 = internaltable1-fields1.

…INTO CORRESPONDING FIELDS:

Select into corresponding fields is used to fetch data from a database table into a user-defined internal table or work area without specifying the list of fields.

SYNTAX:           SELECT fields FROM table name

                        INTO CORRESPONDING FIELDS OF TABLE internal table name.

SELECT DISTINCT:

SELECT DISTINCT is a SQL Select query, which is used to fetch the distinct values of a column from the table.

SELECT DISTINCT eliminates duplicate records of a column from a table.

SYNTAX: SELECT DISTINCT column FROM table name INTO TABLE internal table WHERE condition.

SELECT WITH BYPASSING BUFFER:

When we use SQL statements for fetching data these statements fetch it from Buffer memory for better performance. But when there is a situation where the data of the table is updated very frequently, in that case, we may need to go through the buffer to direct the table. In this scenario, we use this statement.

SYNTAX:

            SELECT fields FROM table name INTO TABLE internal table BYPASSING BUFFER.

SELECT APPENDING:

This statement is used to append SELECT query results directly into an internal table.

SYNTAX: SELECT fields FROM table name APPENDING TABLE internal table name WHERE field = some conditional value.

SQL SELECT STATEMENT CLAUSES:

There are different clauses that can be used with SELECT statement to fetch correct data.

where

group by

having

order by

into|appending

up to, offset

 

WHERE CLAUSE:

Where clause limits the number of rows fetched by the SELECT statement by using a logical condition. A row is fetched only if the logical condition is true.

SYNTAX: SELECT fields FROM table name

                        WHERE fields = logical condition

INTO TABLE internaltable.

 

GROUP BY:

This statement combines groups of the row that have the same content in columns or that have the same result in the SQL statements.

SYNTAX: SELECT fields FROM table name

            GROUP BY column name

INTO TABLE internaltable.

HAVING Clause:

This statement limits the number of rows in groups by using the logical condition on the rows.

SYNTAX: SELECT fields FROM table name

            GROUP BY column name

            HAVING column name = conditional value

INTO TABLE internaltable.

ORDER BY:

This statement sorts the data by the values specified in the column.

SYNTAX: SELECT fields FROM table name

            WHERE fields = logical condition

            ORDER BY PRIMARY KEY

INTO TABLE internaltable.

INTO/APPENDING:

This statement is used to append SELECT query results directly into an internal table.

SYNTAX: SELECT fields FROM table name INTO/APPENDING TABLE internal table name WHERE field = some conditional value.

TABLE SORTING:

In ABAP, values of tables can be sorted using SORT Syntax.

There are different types of sorting:

STABLE

ASCENDING/DESCENDING

STABLE:

This syntax is used to perform stable sorts, which means it will sort only provided columns, and others will remain unchanged.

 

SELECT carrid, connid, cityfrom, cityto 
FROM spfli
ORDER BY carrid, connid
INTO TABLE itab.

SORT itab STABLE BY cityfrom cityto.

 

ASCENDING/DESCENDING

This syntax is used to perform sorting in ascending or descending order. Here the whole table is sorted.

 

SELECT carrid, connid, cityfrom, cityto 
FROM spfli
ORDER BY carrid, connid
INTO TABLE itab.

SORT itab DESCENDING.

INSERT OPERATION:

It allows us to insert one or more lines into the database table.

SYNTAX: INSERT INTO target lines

We can insert lines or data into:

DATABASE

SYNTAX: INSERT INTO dbtab.

SINGLE LINE

SYNTAX: INSERT INTO target VALUES wa.

SEVERAL LINES

SYNTAX: INSERT target FROM TABLE itab