Skip to Content
Technical Articles
Author's profile photo Michael Keller

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.

SELECT SINGLE 'X'
       FROM seoclass
       WHERE clsname = 'CL_GUI_FRONTEND_SERVICES'
       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| ).
ELSE.
  cl_demo_output=>display( |Carrier { carrier } does not exist in SCARR| ).
ENDIF.

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
       WHERE clsname = 'CL_GUI_FRONTEND_SERVICES'
       INTO @DATA(answer_from_db).

IF answer_from_db = entry_exists.
  WRITE 'Entry exists.' COLOR COL_POSITIVE.
ELSE.
  WRITE 'Entry does not exist.' COLOR COL_NEGATIVE.
ENDIF.

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
  IMPORTING
    class_name    TYPE seoclsname
   RETURNING
     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.
  ENDIF.
ENDMETHOD.

[..]

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

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

Michael

 

Assigned Tags

      16 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Gábor Márián
      Gábor Márián

      Before the introduction of the new SQL syntax I would have written something like:

      DATA: exists TYPE abap_bool.
      
      SELECT SINGLE COUNT(*)
        FROM seoclass
        WHERE clsname = 'CL_GUI_FRONTEND_SERVICES'.
      
      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' ).
       ..
      ELSE.
       ..
      ENDIF.

       

      Author's profile photo Michael Keller
      Michael Keller
      Blog Post Author

      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 ?

      Author's profile photo Gábor Márián
      Gábor Márián

      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.
      ...

       

       

      Author's profile photo Michael Keller
      Michael Keller
      Blog Post Author

      I agree with you. The combination of class and method gives a clear picture.

      Author's profile photo Leon van Niekerk
      Leon van Niekerk

      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

      WHERE clsname = 'CL_GUI_FRONTEND_SERVICES'.

      ENDSELECT.

      exists = boolc( sy-subrc = 0 ).

      Author's profile photo Gábor Márián
      Gábor Márián

      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.

      Author's profile photo Klaus Hummel
      Klaus Hummel

      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.

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      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.

      ¯\_(ツ)_/¯

      Author's profile photo Michael Keller
      Michael Keller
      Blog Post Author

      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 🙂

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      Agreed. 🙂 And I like is_this_the_thing method kind a lot, actually. Very useful and keeps the code clear and short.

      Author's profile photo Michelle Crapo
      Michelle Crapo

      Love the comment discussions.  I never really knew what the 'X' meant either.  I just thought it was a very strange thing like 1 = 2 in the code.  🙂

      Author's profile photo Michael Keller
      Michael Keller
      Blog Post Author

      The comments are sometimes better than the blog content 😉

      Author's profile photo Matthew Billingham
      Matthew Billingham

      That's a bit harsh. Even if you are the author!

      (Revisiting the blog to just check something).

      Author's profile photo Alejandro Sensejl
      Alejandro Sensejl

      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:

      SELECT COUNT(*)
        FROM (lv_tablename)
        WHERE (lv_primary_key_cond).

      Any idea on how to improve that use case?

      Cheers,
      Alej

      Author's profile photo Michael Keller
      Michael Keller
      Blog Post Author

      Right away, I do not know the answer. Maybe the answer knows Horst Keller?

      Author's profile photo Matthew Billingham
      Matthew Billingham

      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),

      and

      ...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.