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
Before the introduction of the new SQL syntax I would have written something like:
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:
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:
Here the comparism with abap_true really feels redundant.
Furhtermore this resembles a long-time existing expression 🙂
I agree with you. The combination of class and method gives a clear picture.
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 ).
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 🙂
Agreed. 🙂 And I like is_this_the_thing method kind a lot, actually. Very useful and keeps the code clear and short.
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. 🙂
The comments are sometimes better than the blog content 😉
That's a bit harsh. Even if you are the author!
(Revisiting the blog to just check something).
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:
Any idea on how to improve that use case?
Cheers,
Alej
Right away, I do not know the answer. Maybe the answer knows Horst Keller?
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:
and
As a general principle of clean coding, only use count when you really want a count.