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.

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

 

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

     

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

         

         

  • 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 🙂

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

  • 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

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