SELECT Statement in ABAP

SELECT Statement

Basic SELECT Statement

SELECT *
FROM TABLE MARA
WHERE MATNR = 'ABC'
ENDSELECT.

SELECT WERKS MATKL
FROM TABLE MARA
WHERE MATNR = 'ABC'
ENDSELECT.

Using the SINGLE keyword to select only one row

SELECT SINGLE MARA-WERKS MATKL

FROM TABLE MARA

WHERE MARA-MATNR = 'ABC'.

• Use - between the table name and field name.
• No ENDSELECT statement when using the SINGLE keyword.

Using the UP TO ROWS clause to limit the number of rows returned

SELECT M~WERKS M~MATKL
FROM TABLE MARA AS M
UP TO 5 ROWS
WHERE M~MATNR = 'ABC'.
ENDSELECT.

• Use ~ instead of - when using a table alias to specify column names.

Using the UP TO ROWS clause to limit the number of rows returned

SELECT WERKS MATKL INTO (lv1, lv2)
FROM TABLE MARA
UP TO 1 ROWS
WHERE MATNR = 'ABC'.
ENDSELECT.

• Use a space to separate the column names in the SELECT clause.
• Use , to separate the variable names in the INTO clause. The variable names must be defined in the DATA section.

Using Aggregates in the SELECT clause

SELECT SUM( GSMNG ) MAX( PEDTR )
INTO (ORDERQTY, ORDERFIN)

FROM PLAF
WHERE MATNR = 'ABC' AND PEDTR  <> '00000000'.
ENDSELECT.

• Possible aggregate functions:
  
  
  • MAX
  • MIN
  • AVG
  • SUM
  • COUNT
• The column name within the aggregate function's brackets must be separated from the brackets by a space. Eg: SUM( COL )
  
• Use , to separate the variable names in the INTO clause. The variable names must be defined in the DATA section.

Date and Time System Fields

SY-DATLO
User’s local date.
For example, 19981129, 19990628.

SY-DATUM
Current application server date.
For example, 19981130, 19990627.

SY-DAYST
X during summertime, otherwise space.
SY-FDAYW
Factory calendar day of the week: Monday = 1 … Friday = 5.

SY-TIMLO
User’s local time.
For example, 154353, 225312.

SY-TZONE
Time difference in seconds between local time and Greenwich Mean Time (UTC).
For example, 360, 10800.

SY-UZEIT
Current application server time.
For example, 164353, 215312.

SY-ZONLO
User’s time zone.
For example, EST, UTC.

Date and Time Manipulation

Get The Date/Time 

GET TIME [FIELD <f>].

Synchronizes the date/time on the application server with the time on the database server and refreshes the system fields:

• SY-UZEIT - Current application server time,
• SY-DATUM - Current application server date,
• SY-TIMLO - User’s local time,
• SY-DATLO - User’s local date,
• SY-ZONLO - User’s time zone.

If you use the FIELD addition, the variable
is filled with the current time.


Add/Subtract Months To A Date

If you want to add months to a date you can use the function module:

• MONTH_PLUS_DETERMINE

Example code:

In the DATA section of the code:

DATA: lv_newdate LIKE SY-DATUM,
lv_months TYPE I.


Code using MONTH_PLUS_DETERMINE:

*Synchronize the date/time and refresh the SY-DATUM field.
GET TIME.

*Set the value of lv_months (can be positive or negative).
lv_months = 3.

*Add the months to the date.
CALL FUNCTION 'MONTH_PLUS_DETERMINE'
EXPORTING
MONTHS = lv_months
OLDDATE = SY-DATUM
IMPORTING
NEWDATE = lv_newdate.

WRITE:/ 'Old Date was ', SY-DATUM.
WRITE:/ 'New Date is ', lv_newdate.