Tuesday, July 28, 2015

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.

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.

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.


9 comments:

Anonymous said...

MR. SANDIP ROY,

As I always tell you your blog is excellent.

I have one small doubt regarding to select distinct.
what is the difference between select distinct and delete adjacent duplicates.

cant we use below statement instead of select distinct.

sort it_ekpo.
delete adjacent duplicates from it_ekpo comparing ebeln ebelp.

Regards,
kumar.

Anonymous said...

hello,

it should be

select-options: s_ebeln for wa_ekpo-ebeln.

Unknown said...

Hello,
IF THERE ARE 100 RECORDS IN MY DATABASE TABLE IN THAT 10 DUPLICATE RECORDS ARE THERE ,
when we use
SELECT DISTINCT FROM INTO WHERE XX = P_XX.

WHICH RECORD WILL FETCH FROM DATABASE (I.E... TOP MOST RECORD OR LAST RECORD IN THAT DUPLICATE DATA).

Anonymous said...

SAP Success Factors Real Time Hands on Training in Chennai...

Don't always Depend on Training Institute Alone and so please aware of Best Trainers too..

http://thecreatingexperts.com/sap-successfactors-training-in-chennai/

If You need a Best Trainer over SAP Success Factors Means??? Please ready for an DEMO From the Trainer MR.Karthick
CONTACT:8122241286

Both Classroom/Online Training is Available!!!!!!

Unknown said...


From My search…Creating Experts provides Best SAP Training with real time projects assistance.

Most of the modules are equipped with advance level topics which the student can learn from the basics to the advance level stage.

They also provide placement assistance in leading MNC companies across the globe according to the current requirements.

http://thecreatingexperts.com/sap-successfactors-training-in-chennai/

And these are the Best SAP training institute which provides Real Time Hands on Training…
codedion Technologies-9003085882

creating Experts-8122241286

http://thecreatingexperts.com/sap-mm-training-in-chennai/

kavi kalyan said...

Best SAP Success Factor Training in Chennai

http://thecreatingexperts.com/sap-training-in-chennai/
http://thecreatingexperts.com/sap-successfactors-training-in-chennai/
http://thecreatingexperts.com/sap-mm-training-in-chennai/
http://thecreatingexperts.com/sap-fico-training-in-chennai/

Anonymous said...

Informative Blog...For a long time I was craving for a career growth in programming and then I came to know that THE CREATING EXPERTS is the one who provide training with hands on training and real time scenarios

http://thecreatingexperts.com/sap-abap-training-in-chennai/

contact 8122241286

Arun Maheedhar said...

Here Select DISTINCT Operation will happen in the Database Layer while fetching the data itself it'll take the unique entries (performance LVL- Best)

But Delete Adjacent Duplicates are Internal table operations it'll fetch the records from the Database layer to Application layer and it'll delete the duplicate. Since you'll come to know which is the best (Performance LVL - AVG)

SieveSoftware said...

Great Post. The information provided is of great use as I got to learn new things. Keep Blogging.
SAP ABAP TRAINING IN HYDERABAD