Posted by Bryan
SQL in ABAP, is different from other languages, e.g.: Ax.
SELECT Syntax
SELECT <selected_result>
INTO <target_area>
FROM <source_database_tab>
[WHERE <where_condition>]
[GROUP BY <fields>]
[HAVING <having_condition>]
[ORDER BY <fields>].
Example 1: Select all column
TABLE: KNA1.
SELECT *
FROM KNA1
WRITE:/ KNA1-KUNNR, KNA1-NAME1.
ENDSELECT.
This is similar to Ax Code:
while select CustTable {
info(CustTable.AccountNum)
}
If want to get single record, we can use this.
SELECT SINGLE *
FROM KNA1.
WRITE:/ KNA1-KUNNR, KNA1-NAME1.
Example 2: Select particular columns
In the select field list, we have to use ~ to point to the table & fields, e.g.: KNA1~KUNNR.
For the list of the fields, traditionally, we are using comma “,” as the separator, but in ABAP, we just use space.
We need to assign the result to a variable by INTO.
TABLES: KNA1.
DATA: BEGIN OF dsCT,
AcNum TYPE KNA1-KUNNR,
Name TYPE KNA1-NAME1,
City TYPE KNA1- ORT01,
END OF dsCT.
SELECT ct~KUNNR ct~NAME1 ct~ORT01 INTO dsCT
FROM KNA1 AS ct.
WRITE:/ dsCT-AcNum, dsCT-Name, dsCT-City.
ENDSELECT.
WHERE Clause
In the where clause, we use ~ to point to the field of the table.
The WHERE clause conditions, we can use the following
- Relational Operations
- Equal: EQ or =
- Not Equal: NE or <> or ><
- Less than: LT or <
- Less than or equal: LE or <=
- Greater than: GT or >
- Greater than or equal: GE or >=
- Range Operators
- String Pattern
- [NOT] LIKE pattern1 [ESCAPE pattern2]
- Wildcard:
- % for any chars
- _ for a single char
- Specifying a list
- Work with subqueries:
- [NOT] IN (subquery)
- [NOT] EXISTS (subquery)
Example 3:
SELECT SINGLE *
FROM KNA1.
WHERE KNA1~ORT01 LIKE '%LUMPUR'
AND KNA1~UMSA1 <= 50000.
" we also can use -- KNA1~UMSA1 LE 50000
WRITE:/ KNA1-KUNNR, KNA1-NAME1, KNA1- ORT01, KNA1-UMSA1.
GROUP BY
This is similar with standard SQL query. The syntax will be
SELECT * INTO var FROM table
GROUP BY field1 field2 …
Field list, are separated by space.
HAVING
The HAVING clause is to restrict the output to selected groups based on specified conditions. This must used only in conjunction with GROUP BY. The syntax is same as standard SQL.
Example 4:
SELECT ZEILE
INTO fZAILE
FROM MARI
GROUP BY ZEILE
HAVING SUM(MENGE) > 10.
ORDER BY
This ORDER BY is same with standard SQL, but the field list is separated with space
ORDER BY field1 field2 …
JOIN
For this, we have to create the data type for the result set to store the values return from the SQL.
Example 5:
(Here using Ax table as example)
DATA: BEGIN OF rsCT,
AcNum TYPE CustTable-AccountNum,
Name TYPE CustTable-FullName,
Address TYPE Address-Address,
END OF rsCT.
SELECT ct~AccountNum ct~FullName addr~Address
INTO rsCT
FROM CustTable AS ct JOIN Address AS addr
ON ct~recId = addr~AddrRecId
WHERE addr~AddrTableId = '77'
" This is the table ID for CustTable (in Ax)
WRITE:/ rsCT-AcNum, rsCT-Name, rsCT-Address.
ENDSELECT.
Aggregate
Syntax
... { MAX( [DISTINCT] col )
| MIN( [DISTINCT] col )
| AVG( [DISTINCT] col )
| SUM( [DISTINCT] col )
| COUNT( DISTINCT col )
| COUNT( * )
| COUNT(*) } ... .
Example 6:
DATA count TYPE i.
SELECT COUNT( DISTINCT ORT01 )
FROM KNA1
INTO count
WHERE ORT01 = 'NEW YORK'.
System variables
- SY-SUBRC –> it will return the status of the SQL execution, if success, 0 (zero) will return.
- SY-DBCNT –> it will return the number of records fetched from database.
Others example of SELECT Statement
Example 7: Select all fields and single record into Corresponding Fields of Table
DATA cust TYPE KNA1.
SELECT SINGLE *
INTO CORRESPONDING FIELDS OF cust
FROM KNA1.
Example 8: Select particular fields and single record into Corresponding Fields of Table
DATA cust TYPE KNA1.
SELECT SINGLE ct~KUNNR ct~NAME1
INTO CORRESPONDING FIELDS OF cust
FROM KNA1 AS ct.
Example 9: Select all fields and multiple records into Corresponding Fields of Table and by looping
DATA cust TYPE KNA1.
SELECT *
INTO CORRESPONDING FIELDS OF cust
FROM KNA1.
...
ENDSELECT.
Example 10: Select particular fields and multiple records into Corresponding Fields of Table and by looping
DATA cust TYPE KNA1.
SELECT ct~KUNNR ct~NAME1
INTO CORRESPONDING FIELDS OF cust
FROM KNA1 AS ct.
...
ENDSELECT.
Example 11: Select records into Corresponding Fields of Table and records store in variable
This example is showing about how to create the variables to store the data set and single line data.
DATA: rsCT TYPE STANDARD TABLE OF KNA1,
lnCT LIKE LINE OF rsCT.
SELECT ct~KUNNR ct~NAME1
INTO CORRESPONDING FIELDS OF TABLE rsCT
" Notice that, we have keyword of TABLE here,
" which is different from previous example
FROM KNA1 AS ct.
IF sy-subrc EQ 0.
LOOP AT rsCT INTO lnCT.
" This statement is similar to PHP code:
" foreach ($grid_arrray as $single_row)
" to split out every row into other variable
WRITE:/ lnCT-KNA1, lnCT-NAME1.
ENDLOOP.
ENDIF.
Subquery
The subquery are same as regular SQL subquery statement, but just to take note on the “( )”. After we type “(“, we must left a space before write SELECT.
Correct Syntax:
( SELECT ... ORDER BY ABC )
Wrong Syntax:
(SELECT ... ORDER BY ABC)
Example 12: Non-scalar subquery
DATA: Emp TYPE TABLE OF zbemployee WITH HEADER LINE.
SELECT zbemployee~Name zbemployee~Place
INTO TABLE Emp
FROM zbemployee
WHERE zbemployee~Dept_ID IN
( SELECT DEPARTMENT_ID
FROM ZBDEPARTMENT
WHERE DEPARTMENT_NAME = 'COMPUTERS' ).
LOOP AT Emp.
WRITE:/ Emp-ID, Emp-Name, Emp-Place.
ENDLOOP.
Example 13: Scalar subquery
DATA: Emp TYPE TABLE OF zbemployee WITH HEADER LINE.
SELECT zbemployee~Name zbemployee~Place
INTO TABLE Emp
FROM zbemployee
WHERE zbemployee~Dept_ID IN
( SELECT DEPARTMENT_ID
FROM ZBDEPARTMENT
WHERE DEPARTMENT_AREA = zbemployee~AREA ).
LOOP AT Emp.
WRITE:/ Emp-ID, Emp-Name, Emp-Place.
ENDLOOP.
Fibonacci (1 to 21) 1
------------------------------------------------------------------------------------------------
F(n): n=15 F=610
dynAmics AX vs SAP ag 