Skip to Content
Technical Articles

the ‘X’ in the select statement

Some time ago ABAP surprised me with a nice feature. I saw an interesting SELECT statement during a code review. Here is the SELECT statement. For demonstration purpose I changed the statement a little bit.

       FROM seoclass
       INTO @data(x).

CHECK sy-subrc = 0.

Right away I noticed the “X” behind the term “SINGLE”. I hadn’t seen such a combination before. Or I couldn’t remember it. In any case, I didn’t use this syntax yet.

Out of interest, I asked a colleague. He had the right answer: You can check if there is a row in the database table that meets the WHERE condition. With this hint I remembered that I had read about it someday. There was a blog by Horst Keller (and also the link to the SAP online help). From 7.40 SP05 this feature exists. Here is the example from the SAP Online Help.

SELECT SINGLE @abap_true
       FROM scarr
       WHERE carrid = @carrier
       INTO @DATA(exists).

IF exists = abap_true.
  cl_demo_output=>display( |Carrier { carrier } exists in SCARR| ).
  cl_demo_output=>display( |Carrier { carrier } does not exist in SCARR| ).

By using the variable “exists” it’s much more clear what the statement meant. Because of the challenge to write “easy to understand” source code, I then tried the following example.

CONSTANTS entry_exists TYPE abap_bool VALUE abap_true.

SELECT SINGLE @entry_exists
       FROM seoclass
       INTO @DATA(answer_from_db).

IF answer_from_db = entry_exists.
  WRITE 'Entry does not exist.' COLOR COL_NEGATIVE.

This is valid and works. And a little bit more understandable. However, an additional comment could be useful. According to “Clean ABAP” styleguide, there is another solution so we can omit the comment: Put the source code in a separate method with a suitable name, something like “IS_CLASS_AVAILABLE” in my demo. This could look like this (I omitted some source code parts).


CLASS-METHODS is_class_available
    class_name    TYPE seoclsname
     VALUE(result) TYPE abap_bool. 

METHOD is_class_available.
  CONSTANTS entry_exists TYPE abap_bool VALUE abap_true.

  SELECT SINGLE @entry_exists
         FROM seoclass
         WHERE clsname = @class_name
         INTO @result.

  IF sy-subrc <> 0.


IF lcl_test=>is_class_available( 'CL_GUI_FRONTEND_SERVICES' ) = abap_true.
   WRITE 'Class exists.' COLOR COL_POSITIVE.
  WRITE 'Class does not exist.' COLOR COL_NEGATIVE.

Ok, with this solution I was happy. For now 😉 Perhaps how to handle sy-subrc is worth a discussion.

As a hint: If you are interested in performance discussions about checking if a row exists, you should read the comments on Horst Keller’s blog.


Best regards and thanks for reading



You must be Logged on to comment or reply to a post.
  • Before the introduction of the new SQL syntax I would have written something like:

    DATA: exists TYPE abap_bool.
      FROM seoclass
    exists = boolc( sy-dbcnt = 1 ).

    But I also like the new way better as it feels more "clean".

    I think in this case its OK to simply skip the SY-SUBRC handling part, as it adds nothing to the functionality, nor to the readability .

    Another tiny improvement is to shorten the condition part of the IF statement, as this is now possible with functional methods:

    IF lcl_test=>is_class_available( 'CL_GUI_FRONTEND_SERVICES' ).


    • Nice idea to use sy-dbcnt. And thanks for the hint to shorten the condition part of the IF statement. So we can start a new discussion whether the readability and comprehensibility of the source code is improved or worsened ?

      • In my opinion the short form definitely increases readability if the method has a meaningful name which implies a boolean value is returned like:

        IF cusomter->is_cpd( ).
        IF iterator->has_next( ).
        IF accounting_doc->is_posted( ).

        Here the comparism with abap_true really feels redundant.

        Furhtermore this resembles a long-time existing expression 🙂

        IF customer_list IS INITIAL.



    • Never understand why count * is used, while you are not interested in the amount of lines. Waste of processing..Database will count it whilte it is of no use. When you are interested in existence, use up to 1 rows (or single when primary key is known), not with count:

      DATA: exists TYPE abap_bool.

      SELECT mandt

      into lv_mandt

      FROM seoclass

      up to 1 rows



      exists = boolc( sy-subrc = 0 ).

      • In this special case where the primary key is fully specified and SINGLE addition is used which translates to SELECT TOP 1 I don't think COUNT(*) is worse in performance. I prefer this to avoid the declaration of a variable in which value I am not interested in. In other scenarios I use (or used before 7.40) the way you mentioned.

        I agree in general, that no unnecessary load should be put on the DB.

  • Would be even more readable if SAP finally decided to implement a REAL boolean data type within ABAP, since abap_true and abap_false are nothing else than those type c length 1 things bearing either an ‘X’ or a blank.

  • Maybe it's just me but personally I feel that entry_exists constant doesn't add much value here. Splitting hairs, potato-potahto, etc. 'X' is bad obviously but the example in Help is OK as is IMHO.


    • Good hint. The constant is really not necessary. Much more important is to implement the SELECT statement in its own method with a descriptive name. This means that you only have to concentrate on a few lines of source code 🙂

  • Hey Michael Keller, thank you for your blog! I actually stumbled over that topic in summer and still have an unsolved mystery with this:

    When trying to check existence of a record with dynamic select for table name and where condition (condition uses full qualified primary key with several fields) it is always slow and it does not make a difference when using COUNT( * ) or new syntax with 'X' literal as field list.

    Something like:

      FROM (lv_tablename)
      WHERE (lv_primary_key_cond).

    Any idea on how to improve that use case?


    • Put a trace on and have look at the execution plan.

      According to this discussion here, here and here, SELECT count(*) (even UP TO 1 ROWS) should not be used for existence check at all, as:

      An existence check with SELECT COUNT(*) FROM dbtab … UP TO 1 ROWS can be harmful because some database platforms really count all entries (the UP TO 1 ROWS option just tells them to return only one value, which is the count result),


      ...on DB2 for example (i just ran it), we do 1182 "get pages" for e.g. this statement:

      select count(*) into sy-dbcnt from t100 up to 1 rows where sprsl = 'DE'.

      "get pages" are the database pages that are touched... 1182 clearly indicates that we touch more than one row.

      As a general principle of clean coding, only use count when you really want a count.