Select Distinct
Select distinct only selects the unique entries of the fields in the select statement. It will not allow any duplicate entry into the internal table. In the below example we are having a selection screen where we are defining a selection range of PO number by select option. At first we are fetching the records with normal select statement and we find six records from the database.
REPORT zabap_gui.
TABLES: ekpo.
* Creating a custom structure of Item Table
TYPES:
BEGIN OF ty_ekpo,
ebeln TYPE ekpo-ebeln,
ebelp TYPE ekpo-ebelp,
matnr TYPE ekpo-matnr,
werks TYPE ekpo-werks,
lgort TYPE ekpo-lgort,
END OF ty_ekpo.
* Creating a line type of predefined structure
DATA:
wa_ekpo TYPE ty_ekpo,
it_ekpo TYPE STANDARD TABLE OF ty_ekpo.
SELECT-OPTIONS: s_ebeln FOR ekpo-ebeln.
SELECT ebeln ebelp matnr werks lgort
FROM ekpo INTO TABLE it_ekpo
WHERE ebeln IN s_ebeln.
WRITE:/ 'PO No.',
15 'Item No',
28 'Material',
48 'Plant',
55 'Storage'.
ULINE.
SKIP.
LOOP AT it_ekpo INTO wa_ekpo.
WRITE:/ wa_ekpo-ebeln,
15 wa_ekpo-ebelp,
28 wa_ekpo-matnr,
48 wa_ekpo-werks,
55 wa_ekpo-lgort.
ENDLOOP.
TABLES: ekpo.
* Creating a custom structure of Item Table
TYPES:
BEGIN OF ty_ekpo,
ebeln TYPE ekpo-ebeln,
ebelp TYPE ekpo-ebelp,
matnr TYPE ekpo-matnr,
werks TYPE ekpo-werks,
lgort TYPE ekpo-lgort,
END OF ty_ekpo.
* Creating a line type of predefined structure
DATA:
wa_ekpo TYPE ty_ekpo,
it_ekpo TYPE STANDARD TABLE OF ty_ekpo.
SELECT-OPTIONS: s_ebeln FOR ekpo-ebeln.
SELECT ebeln ebelp matnr werks lgort
FROM ekpo INTO TABLE it_ekpo
WHERE ebeln IN s_ebeln.
WRITE:/ 'PO No.',
15 'Item No',
28 'Material',
48 'Plant',
55 'Storage'.
ULINE.
SKIP.
LOOP AT it_ekpo INTO wa_ekpo.
WRITE:/ wa_ekpo-ebeln,
15 wa_ekpo-ebelp,
28 wa_ekpo-matnr,
48 wa_ekpo-werks,
55 wa_ekpo-lgort.
ENDLOOP.
Selection Range with select option:
The output is:
Now with the similar selection range we use select distinct statement and we are getting only three records. This is because we have selected only the PO number in select statement with distinct clause. Now distinct will not allow any duplicate entry of PO number.
REPORT zabap_gui.
TABLES: ekpo.
* Creating a custom structure of Item Table
TYPES:
BEGIN OF ty_ekpo,
ebeln TYPE ekpo-ebeln,
END OF ty_ekpo.
* Creating a line type of predefined structure
DATA:
wa_ekpo TYPE ty_ekpo,
it_ekpo TYPE STANDARD TABLE OF ty_ekpo.
SELECT-OPTIONS: s_ebeln FOR ekpo-ebeln.
SELECT DISTINCT ebeln
FROM ekpo INTO TABLE it_ekpo
WHERE ebeln IN s_ebeln.
WRITE:/ 'PO No.'.
ULINE.
SKIP.
LOOP AT it_ekpo INTO wa_ekpo.
WRITE:/ wa_ekpo-ebeln.
ENDLOOP.
TABLES: ekpo.
* Creating a custom structure of Item Table
TYPES:
BEGIN OF ty_ekpo,
ebeln TYPE ekpo-ebeln,
END OF ty_ekpo.
* Creating a line type of predefined structure
DATA:
wa_ekpo TYPE ty_ekpo,
it_ekpo TYPE STANDARD TABLE OF ty_ekpo.
SELECT-OPTIONS: s_ebeln FOR ekpo-ebeln.
SELECT DISTINCT ebeln
FROM ekpo INTO TABLE it_ekpo
WHERE ebeln IN s_ebeln.
WRITE:/ 'PO No.'.
ULINE.
SKIP.
LOOP AT it_ekpo INTO wa_ekpo.
WRITE:/ wa_ekpo-ebeln.
ENDLOOP.
Hence the output is as follows.
Here we know that one PO can have multiple items. Hence in database table EKPO the PO entries are having duplicate entries for different items. But selecting the PO number with distinct clause will fetch only the unique PO number from the database. If we select here the item also with the distinct clause the SAP system will treat both of those fields as unique. In that case the system will recognize PO number and corresponding item number is the unique. In this way if we increase the fields in selection the system will give uniqueness according to the combination of all those selected fields.