difference between select single * and select upto one row

[Former Member]

Hi all,

can any body tell me the difference between select single * ........... and select upto one row......

Thanks,

Sri.....

[Former Member]

Hi all,

can any body tell me the difference between select single * ........... and select upto one row......

Thanks,

Sri.....

[Former Member]

Hi Sriram

Select SINGLE * is used to select the single record. It is normally used when we know that there will be exactly one match for the selection criteria

Select .... upto one row : it is used when we know that there may be more than one record to the selection criteria but we are interested in retrieving only one - first match only.

Regards

Ashish

[Former Member]

Hi, the techinal difference on them is as following:

select single:

will select out the first entry, which match the condition of select. The system will return the first entry only.

select up to N row:

will select out all the entries which match the condition of the select statement, after that, return the first n entries.

So if you want to select out only one entry, the 'select single' is better than 'select up to one row' in performance. Because 'select up to one row' will select out multiple result before return the first entry.

But there is a drawback of 'select single', if the result of select is mutiple(not match full key), the extend check will give a warning to this 'select single'.

Hope it will be hopeful

thanks

[Former Member]

Hi Sriram,

SELECT SINGLE is an option we use only when we know the <b>full key</b> of the table, not when we know that there will be only one record. So if you are selecting from MARA and your WHERE condition has MATNR in it, then you should use SELECT SINGLE. But if your WHERE condition has BISMT(old material number) and even if you know that it will result in one record only, you should not use SELECT SINGLE. It is not that it will give you an error but if you do an extended check, there it will show it as a warning saying that you didn't use the full key and that there is a possibility that there could be more than one record.

SELECT UP TO 1 ROWS is used when you are not passing the key field, but you know 1)there will be only one record or 2)all records will have the same value for the selected field. Let us say you are selecting from MARC and you are interested in the value of the field ABC indicator. You know, based on your business process, that this indicator will have the same value even though, it is extended to 10 plants. Then you can use SELECT ABCIN FROM MARC UP TO 1 ROWS WHERE MATNR = P_MATNR. ENDSELECT. Here, even though you are not supplying the second key field WERKS, since you know there will only be one value(even though there are multiple records fetched with this clause), you are using SELECT UP TO 1 ROWS. In the other example where you select from MARA using BISMT, there it might fetch you just one record and so you will still use SELECT UP TO 1 ROWS.

Remember, SELECT UP TO 1 ROWS introduces a loop to fetch one record from your database, where as SELECT SINGLE doesn't.

All said and done, people use both to serve the same purpose, if they feel there is only one record. But as I said, the intended purpose of the two statements is different.

Please reward and close the post, if answered.

Thanks,

Srinivas

[Former Member]

Hi,

<b>

Knowing when to use SELECT SINGLE or SELECT ... UP TO 1 ROWS</b>

A lot of people use the SELECT SINGLE statement to check for the existence of a value in a database. Other people prefer to use the 'UP TO 1 ROWS' variant of the SELECT statement.

So what's the difference between using 'SELECT SINGLE' statement as against a 'SELECT .... UP TO 1 ROWS' statement ?

If you're considering the statements

SELECT SINGLE field INTO w_field FROM table.

and

SELECT field INTO w_field FROM table UP TO 1 ROWS. ENDSELECT.

then looking at the result, not much apart from the extra ENDSELECT statement. Look at the run time and memory usage and they may be worlds apart.

Why is this ?? The answer is simple.

The 'SELECT SINGLE' statement selects the first row in the database that it finds that fulfils the 'WHERE' clause If this results in multiple records then only the first one will be returned and therefore may not be unique.

The 'SELECT .... UP TO 1 ROWS' statement is subtly different. The database selects all of the relevant records that are defined by the WHERE clause, applies any aggregate, ordering or grouping functions to them and then returns the first record of the result set.

Get the difference ??

If not, here is a good example, credit for this example goes to Richard Harper, a friend of mine on sapfans.com :

Create a Ztable called ZDifference with 2 fields in it, MANDT of type MANDT and POSNR of type POSNR. Make sure both of these are keys. Also create a table maintenance dialog for it (SE11->Utilities->Table Maintenance Generator). Fill the table with ten rows 000001-000010.

Then run the program shown below:

Code:

********************************************************** 
*       Program:     Z_Difference  
*       Purpose:     A program that demonstrates the *difference between SELECT SINGLE and SELECT UP TO n *ROWS. 
*   This program requires the data table Z_DIFFERENCE 
*    to have been created according to the structure 
*   outlined in the text above and populated with 
*   at least 10 records. 
*********************************************************
Report Z_Difference 
       Message-id 38 
       Line-Size  80 
       Line-Count 0 
       No Standard Page Heading. 
* 
Start-Of-Selection. 
					
  Data: w_Single type Posnr, 
        t_Rows   type standard table of Posnr 
                 initial size 0 
                 with header line. 
* 
  Select single Posnr 
    from zDifference 
    into w_Single. 
* 
  Select Posnr 
    into table t_Rows 
    from zDifference 
   up to 1 rows 
   order by Posnr descending. 
* 
   Write :/ 'Select single:', w_Single. 
   Skip 1. 
   Write :/ 'Up to 1 rows :'. 
   Loop at t_Rows. 
        Write t_Rows. 
   EndLoop.

<b>You should see the output:

Select single: 000001

Up to 1 rows : 000010</b>

The first 'SELECT' statement selected the first record in the database according to any selection criterion in the 'WHERE' clause. This is what a 'SELECT SINGLE' does. The second 'SELECT' has asked the database to reverse the order of the records before returning the first row of the result.

In order to be able to do this the database has read the entire table, sort it and then return the first record. If there was no ORDER BY clause then the results would have been identical (ie both '000001') but the second select if given a big enough table to look at would be far slower.

<b>Note</b> that this causes a problem in the Extended Program Check if the full key is not specified in a 'SELECT SINGLE'. Replacing the 'SELECT SINGLE' by an "UP TO 1 ROWS" will give the same exact results without any warning but the program will run slower and consume more memory. This is a good example of a warning that we should ignore... considering you are sure of what you are doing !!

Hope this helsp u.

Kindly reward points and close the thread.

[christian_wohlfahrt]

Hi!

If you want to go deeper into this topic, then follow discussion here:

Regards,

Christian