How to Create SAP SQL Query
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