Saturday, July 25, 2015

Select Single and Select up to

Select single statement only selects the first record of any series of records from a database table. That means this statement can read a single record from a database table. It keeps the data into a work area or header line. This work area is generally a line type of internal table.

In below example we are fetching records of PO no, item no, material, plant & storage location from table EKPO where the PO no is 3000000232. The database contains only 3 items for this PO.
















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.

* Select single will fetch only the First record
* from the PO Item table
SELECT SINGLE ebeln ebelp matnr werks lgort
  FROM ekpo INTO wa_ekpo
  WHERE ebeln = '3000000232'.

WRITE:/    'PO No.',
        15 'Item No',
        28 'Material',
        48 'Plant',
        55 'Storage'.
ULINE.
SKIP.

WRITE:/    wa_ekpo-ebeln,
        15 wa_ekpo-ebelp,
        28 wa_ekpo-matnr,
        48 wa_ekpo-werks,
        55 wa_ekpo-lgort.

Output of this:



The database contains 3 records here. But select single statement fetches only single record from database. Here the first record is always fetched by this statement. 








Select up to statement is used to mention the rows need to be selected from the database table. If the database contains that number of rows then it will display accordingly. Here we are declaring an internal table to store the multiple row records and print the output as well.

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 up to n rows fetches n rows
* from the PO Item table
SELECT ebeln ebelp matnr werks lgort
  UP TO 5 ROWS
  FROM ekpo INTO TABLE it_ekpo
  WHERE ebeln = '3000000232'.

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.

Output of this:



Since there are only three records in database, the system shows only 3 records in spite of being mentioned “UP TO 5 ROWS”.

21 comments:

Anonymous said...

MR. SANDIP ROY,
excellent work, appreciate.

I think select single statement can also write like this.

TABLES: EKPO.
DATA: WA_EKPO TYPE EKPO.

WRITE:/ 'PO No.', 15 'Item No', 28 'Plant'.

SELECT SINGLE * FROM EKPO INTO WA_EKPO WHERE EBELN ='3000000012'.

WRITE:/ WA_EKPO-EBELN, 15 WA_EKPO-EBELP, 28 WA_EKPO-WERKS.

Sandip Roy said...

Yes. You are right. But we always mention the fields because of performance issue. Select * means you are picking up all fields which takes more time than selecting fields. Now one report can contain hundreds of select statement. So if we don't mention specific fields then it will take a lot of time for showing the output.

Anonymous said...

thanks for your reply. I agree with you.

Anonymous said...

WHAT IS THE DIFFERENCE BETWEEN SELECT SINGLE AND SELECT UPTO 1 ROWS.AND WHICH ONE IS BETTER

Anonymous said...

SELECT SINGLE IS ALWAYS BETTER, SINCE IT HITS THE DATABASE ONCE.BUT IN CASE OF SELECT UPTO 1 ROWS IT HITS THE DATABASE TWICE, THAT LEADS TO PERFORMANCE ISSUE.

Unknown said...

Hi SANDIP ROY, This is really great Effort,Thank u so much.

Regarding differences between SELECT SINGLE and SELECT UP TO 1 ROW, I have read multiple answers, Still have confusion, Will u please differentiate these from your point of understanding.

Sandip Roy said...

The main difference is that you have to use an internal table while using Select Up To 1 Row but you can use a work area while using Select Single.

Since using an Internal table rather than work area always cuts down the performance, Select Single is always preferable when we need only one record by Select statement.

Unknown said...

Thanks SANDIP, For your reply,

I'm clear on what u differentiate.

But in discussion forums, I have read some restrictions on SELECT SINGLE like,1)we should mention full key of the table in where clause.But without key fields or partially key fields also i'm able to get data by using SELECT SINGLE. What is that restriction actually means. Does really there are any conditions while using SELECT SINGLE.

Regarding SELECT UP TO, Some one suggest that we should use END SELECT.But With out end select also i'm getting the records.So why we need to use END SELECT in this case.

Performance wise which is better SELECT SINGLE OR SELECT UP TO 1 ROW?


Sandip Roy said...

Performance wise Select Single is always better.

There is no restriction is Select Single. If you use all key fields in Where clause then it could be better. Otherwise system will take a bit more time to select. That's it.

Select - End Select is a loop. End Select is a part of Select query. Those are obsolete now. Don't use that.

Unknown said...

Really excellent bro !!! wunderschÖn im deutsche.

praveen said...

Excellent ROY , i just got cleared now.
So we need to use select single when we know/have all primary key fields in WHERE condition , right ?
And we need to use select upto 1 row when we do not have all primary keys in where condition ??

So as per my understanding both are really different .
and both have different purposes .
please clear my understanding .
Select single is always faster than select upto if we have perfect keys in where clause ?

And please tell which is faster ?

Anonymous said...

Learned many things from this blog. Thank you for your effort Sandip Roy.

AskMahe.com said...

I am SAP PP and VC consultant started learn ABAP for changing my career to technofunctional consultant. Your posts are so much worth anybody can easily learn ABAP by your post. Happy to write comments to you. Like your presentation. Keep it up Sandip.

AskMahe.com said...

My suggestion is to could you please mark some color in where particular line has changed for the particular post

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 Trianer MR.Karthick
CONTACT:8122241286

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

Unknown said...

One of the Best Blog I've ever come across. Thank you, Roy, for sharing Knowledge!!!

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

Test My internet Speed said...

nice course. thanks for sharing this post.
SAP ABAP Training in Gurgaon

Unknown said...

Right

Unknown said...

Why it hit db twice? How?