Skip to Content
author's profile photo Former Member
Former Member

Select Endselect or Select single

Consider the following code:

SELECT * FROM hrp1000

UP TO 1 ROWS

WHERE plvar = ls_i1001-plvar

AND otype = ls_i1001-otype

AND objid = ls_i1001-sobid

AND istat = '1'

AND begda =< lv_begda

AND endda => lv_endda

AND langu = sy-langu

ORDER BY PRIMARY KEY.

ENDSELECT.

Could this be replaced by a SELECT SINGLE?

We have a QA process which forbids SELECT...ENDSELECTs. In this instance I believe a SELECT SINGLE to be erroneous.

Any thoughts anyone?

Thanks in advance

Ian

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    Posted on Feb 23, 2005 at 03:23 PM

    Hi Ian,

    The result of both select single and select-end-select will be the same.

    As others said, it might be a question of performance as select single is a construct designed to read database records with primary key. In the absence of the primary key, it might end up doing a sequential search, whereas the select up to 1 rows may assume that there is no primary key supplied and will try to find most suitable index.

    The best way to find out is through sql trace or runtime analysis.

    Since your coding standards prohibit the use of select-endselect and with a reason, you might try the solution suggested in a reply here to use 'select into table' option and then read the internal table for the first record available.

    But, please remember that you will do a "select up to 1 rows" only if you are sure that all the records returned will have the same value for the field(s) you are interested in. If not, you will be reading only the first record which matches the criteria, but may be the second or the third record has the value you are looking for.

    Regards,

    Srinivas

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Christian,

      boring Sunday, too?

      The part of incorrect results should be solved with

       
      SELECT * FROM hrp1000
      UP TO 1 ROWS
      WHERE plvar = ls_i1001-plvar
      AND otype = ls_i1001-otype
      AND objid = ls_i1001-sobid
      AND istat = '1'
      AND begda =< lv_begda
      AND endda => lv_endda
      AND langu = sy-langu
      <b>ORDER BY begda descending.</b>  
          "first part of key is equal fixed value!
      ENDSELECT.

      And this is not possible to translate into select single - otherwise results are correct in both cases, as already discussed in detail.

      Question: can entries exist, which need to be selected with descending valid from sorting? (Or am I totally wrong with my analysis? - Be carefull, here are two validity dates <b>and</b> a counter (seqnr) in key fields!)

      I'm looking for further comments!

      Christian

  • author's profile photo Former Member
    Former Member
    Posted on Feb 23, 2005 at 02:02 PM

    You can use SELECT INTO TABLE and avoid the SELECT...ENDSELECT issue.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      When you consider a situation where more than one record exist, it automatically mean that the keys arent fully qualified. Hence Select SINGLE is not advisable (though can be used with warning).

      Further,as i mentioned in the previous post, Incase the table has buferring allowed, select upto 1 rows will be a better option, as the repeated use of the statement can very well fetch data from the buffer instead of looking into the database everytime, which happens in case of SELECT SINGLE.

      Hope this helps

      Regards,

      Vijay

  • author's profile photo Former Member
    Former Member
    Posted on Feb 23, 2005 at 01:59 PM

    Hello Ian,

    You can as well use a SELECT SINGLE in this case, but there's a small glitch. You would get the result you desire, but there will be a warning in the EPC (Extended Program Check)which says that you have not specified the complete primary key. As you might have noticed, the table has just one more key field , SEQNR.

    If the EPC Warning is okay, I suppose you can go ahead with SELECT SINGLE, as long as you are just checking for the existence of some entries.

    Regards,

    Anand Mandalika.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 23, 2005 at 02:14 PM

    Hello Ian,

    As you properly said SELECT SINGLE in that case is erroneous.

    The funny point is according to SAP Performance course, in that case the SELECT UP TO 1 ROWS is faster than SELECT SINGLE because you are not using all the primary key fields.

    I belive your customer should restructure his QA process, because sometimes SELECT...ENDSELECT statements are the right option to be used.

    Regards,

    Mauricio

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.