This one might be trivial, but interestingly there are quiet some discussions about it. Therefore, let’s wrap it up.

Selecting a Fully Specified Row

For selecting a fully specified row with Open SQL SELECT, you specify the full table key in equals conditions combined by AND.

The natural result set of an SQL statement is tabular. And so, the natural way of writing a SELECT statement also in Open SQL is:

SELECT *
       FROM t100
       WHERE sprsl = @sy-langu AND
             arbgb = ‘SABAPDEMOS’ AND
             msgnr = ‘050’
       INTO TABLE @DATA(result).

cl_demo_output=>display( result ).

This is independent from the number of rows expected. Of course, by specifying the full key, the result set contains only one row. Assigning the result set to internal table result of course gives an internal table with one line.

So far so good. In languages dealing with tables only, as.e.g. SAP HANA’s SQLScript, this is also the only way and you always receive a result table with one line. But in ABAP, we additionally have structures. Structures are even older than internal tables. So you can write the result of a single row selection also into a structure:

SELECT *
       FROM t100
       WHERE sprsl = @sy-langu AND
             arbgb = ‘SABAPDEMOS’ AND
             msgnr = ‘050’
       INTO  @DATA(result).
ENDSELECT.

cl_demo_output=>display( result ).

Without specifying TABLE, result is a structure and a SELECT loop is opened that must be closed with ENDSELECT. That is an ABAP speciality of course. Now you might ask why should I open a loop, if I know that I want to read only one row into a structure. And that’s exactly what the ABAP speciality SELECT SINGLE is made for. Nothing more and nothing less.

SELECT SINGLE *
       FROM t100
       WHERE sprsl = @sy-langu AND
             arbgb = ‘SABAPDEMOS’ AND
             msgnr = ‘050’
       INTO  @DATA(result).

cl_demo_output=>display( result ).

Same result as above. The native SQL generated from that syntax and passed to the database is also the same. There is a very small performance improvement with SELECT SINGLE, because no loop has to be opened, but normally that can be neglected. SELECT SINGLE has shorter syntax and  documents the semantics: You know, that you want to read a fully specified row into a structure and you use SELECT SINGLE for that purpose. The syntax documents the semantics of your statement in the program and the extended program check warns you, if you do not specify the full key.

Selecting a Partly Specified Row

If you do not specify the full key, the result set delivered by the database normally contains multiple rows:

SELECT *
       FROM t100
       WHERE sprsl = @sy-langu AND
             arbgb = ‘SABAPDEMOS’
       INTO TABLE @DATA(result).

cl_demo_output=>display( result ).

The internal table result contains mulitple lines now. How can I get one line instead? You know the answer:

SELECT *
       FROM t100
       WHERE sprsl = @sy-langu AND
             arbgb = ‘SABAPDEMOS’
       INTO TABLE @DATA(result)
       UP TO 1 ROWS.

cl_demo_output=>display( result ).

With UP TO 1 ROWS you tell the database to pass only one row in its tabular result set and the internal table contains only one line. But be aware, that the returned row is not defined. It can be any of those specified by the partial key. In order to get a defined row in respect to the sort order, you can add an ORDER BY clause.

SELECT *
       FROM t100
       WHERE sprsl = @sy-langu AND
             arbgb = ‘SABAPDEMOS’
       ORDER BY PRIMARY KEY
       INTO TABLE @DATA(result)
       UP TO 1 ROWS.

cl_demo_output=>display( result ).

Of course, you can also select into a structure:

SELECT *
       FROM t100
       WHERE sprsl = @sy-langu AND
             arbgb = ‘SABAPDEMOS’
       ORDER BY PRIMARY KEY
       INTO @DATA(result)
       UP TO 1 ROWS.
ENDSELECT.

cl_demo_output=>display( result ).

Since UP TO always creates a tabular result set, you must use ENDSELECT.

What about SELECT SINGLE? Yeah, you can use that too:

SELECT SINGLE *
       FROM t100
       WHERE sprsl = @sy-langu AND
             arbgb = ‘SABAPDEMOS’
       INTO @DATA(result).

cl_demo_output=>display( result ).

Again a single row in a structure. But this can be seen as a misuse of  SELECT SINGLE. Especially, you cannot use ORDER BY in connection with SINGLE. Simply, because SELECT SINGLE is not made for this. Therefore, the returned row is always undefined. If you are interested in the contents of the single row, SELECT SINGLE should not be used with partial key specifications.

Checking the Existence of a Row

A widely discussed question is, how to check the existence of a row or rows in a database table. Especially, if you specify a partial key or even non key fields only, you want to restrict the number of lines transferred from the database to the application server as much as possible. Before 7.40, SP05, the minimal numbers of rows to be selected was 1. You can use either SELECT SINGLE or UP TO 1 ROWS in order to restrict the number of rows to that minimum. Since you only check sy-subrc and you are not interested in the contents of the returned row, you don’t have to care about ORDER BY. So both forms can be used:

SELECT SINGLE sprsl
       FROM t100
       WHERE sprsl = @sy-langu AND
             arbgb = ‘SABAPDEMOS’
       INTO @DATA(result).
IF sy-subrc = 0.
  …
ENDIF.

SELECT sprsl      

       FROM t100
       WHERE sprsl = @sy-langu AND
             arbgb = ‘SABAPDEMOS’
       INTO @DATA(result)
       UP TO 1 ROWS.
ENDSELECT.
IF sy-subrc = 0.
  …
ENDIF.

The syntax of SELECT SINGLE is shorter. And performance? On the database both variants take the same time because the same native code is executed there. All in all SELECT SINGLE is even a little bit faster than UP TO 1 ROWS (no loop opened), but that is negligible. Therefore, no problem in “misusing” SELECT SINGLE for merely checking the existence of rows. In that case, you know what you do and you can hide the warning of the extended program check with pragma ##WARN_OK.

With 7.40, SP05, you can use the following syntax:

SELECT SINGLE @abap_true

       FROM t100

       WHERE sprsl = @sy-langu AND

             arbgb = ‘SABAPDEMOS’

       INTO @DATA(result).

IF sy-subrc = 0.

ENDIF.

The constant abap_true is used as a minimal SQL expression. Now, not even a single row must be transported from the database to the application server. You can also check IF result = abap_true.

To report this post you need to login first.

25 Comments

You must be Logged on to comment or reply to a post.

  1. Suhas Saha

    QA guys, please take note –

    Therefore, no problem in “misusing” SELECT SINGLE for merely checking the existence of rows.

    Now don’t bother me with why i used the pragma 😳

    (0) 
  2. Nikolay Evstigneev

    Yep, the last example is the point of the post for me.

    Such small tricks in new ABAP are really cool! Thanks for them! 🙂

    Can’t wait for the weekend when we’re about to end upgrade process and I get the opportunity to use all the bonuses of 7.40!

    (0) 
  3. Jacques Nomssi

    Hello Horst,

    for this method

    METHOD is_global_class.

      CLEAR rv_flag.

      SELECT SINGLE ‘X’ FROM tadir INTO @rv_flag “Single Record buffer

        WHERE pgmid = ‘R3TR’

        AND   object = ‘CLAS’

        AND   obj_name = @iv_name.

    ENDMETHOD.

    the code inspector complains

    SELECT statements that bypass the SAP table buffer


    I can silence the warning with "#EC CI_GENBUFF but I think this check should be updated.


    best regards,


    JNN

    (0) 
    1. Clemens Li

      What did I miss? I expect METHOD is_global_class has a RETURNING parameter rv_flag

      1. why CLEAR it

      2. INTO @rv_flag creates the local variable rv_flag. Is this the same as the returning parameter?

      Regards, Clemens

      Please have mercy as my current customer doesn’t even dream of upgrading…

      (0) 
      1. Christian Guenter

        1. why CLEAR it

        That is superfluous, because the returning parameter is initialized when the method is executed

        2. INTO @rv_flag creates the local variable rv_flag.

        No it doesn’t create a local variable. The @ symbol is just the new way to escape host variables in SQL statements which is enforced by the new SQL syntax (same with @iv_name).

        Is this the same as the returning parameter?

        So it is the returning parameter.

        If you want to declare a new local variable you have to use @data(var_name). But in this case


        INTO @DATA(rv_flag)

        would lead to a syntax error because rv_flag is already declared.



        (0) 
  4. Thomas Zloch

    No wonder, this will be available with or even after ABAP 7.40 SP13. Meanwhile you can use “eclectic” as a workaround, but make sure that the internal table is empty and all buffers are being bypassed.

    (0) 
  5. Michael Fritz

    It would be interesting, too, which coding could be used to select two or more columns from a table using inline declaration.

    Something like this:

    SELECT SINGLE PARVW, ADRNR

    FROM VBPA

    WHERE VBELN = ‘1234567890’

    AND PARVW = ‘AG’

    AND POSNR = ‘000000’

    INTO @DATA(l_parvw), @DATA(l_adrnr)

    Of course this does not work this way, however, what’s the correct usage if there is any at all?

    Michael

    (0) 
    1. Horst Keller Post author

      Simply

      SELECT SINGLE PARVW, ADRNR

      FROM VBPA

      WHERE VBELN = ‘1234567890’

      AND PARVW = ‘AG’

      AND POSNR = ‘000000’

      INTO @DATA(l_result).

      The result is a structure with two components, see Alternative 4 under SELECT – INTO.

      Horst

      (0) 
      1. Horst Keller Post author

        Lo and behold!

        An undocumented feature.

        In my (7.50) System it works:

        SELECT SINGLE carrid, connid

               FROM spfli

               INTO (@DATA(w1),@DATA(w2)).

        Have to ask my development buddy when he introduced it (and why he didn’t tell me) …

        (0) 
        1. Nikolay Evstigneev

          Yep, after reading the doc _carefully_ I finally noticed there was some difference with what I suggested.

          I tested it several months ago on 7.40 SP9, it worked and I started using it. It seemed so evident. Tough guys don’t read the doc 😆

          P.S. Wait, 7.50? Goodness gracious! I don’t want another upgrade for a while 😆

          (0) 
          1. Horst Keller Post author

            It’s available since 7.40, SP08 like Alternative 4, but not documented 🙁 .

            Goodness gracious! I don’t want another upgrade for a while

            Then you’ll miss another set of cool things for a while 😉

            (0) 
            1. Nikolay Evstigneev

              Then you’ll miss another set of cool things for a while

              It’s a joke of course. But the reason of it is that our basis guy got ill right before the upgrade of production system. And the bosses said we must finish it anyway. So I had to support the upgrade both from ABAP and basis sides and those were a couple of awful days (and nights 🙂 ).

              As far as I was away from SCN for several weeks, I’ll check the news for 7.50, you may have already written about it 🙂 And when the time comes we’ll do another step forward, the previous one made me an extreme fan of the new possibilities.

              (0) 
              1. Horst Keller Post author

                I’ll check the news for 7.50, you may have already written


                not yet, not yet …, only in the moment, it will be publicly available

                (0) 
      2. Michael Fritz

        That’s exactly what I’ve tried before – to no avail – ERP 7.40, btw.

        Wait a Minute – just figured it out, works in 7.40, too, however you have to put the INTO clause at the very last line of the SELECT statement.

        Correction of my posting, see some comments later...

        Michael

        (0) 
        1. Nikolay Evstigneev

          As Horst said, such syntax is available since SP02.

          For me (SP09) your example works fine like this:

          SELECT SINGLE parvw, adrnr
          FROM vbpa
          INTO ( @DATA(l_parvw), @DATA(l_adrnr) )
          WHERE vbeln = ‘1234567890’
          AND parvw = ‘AG’
          AND posnr = @space.


          P.S. My syntax checker allows me not to put INTO clause to the last line. Anyway, it’s not a problem at all 🙂

          (0) 
          1. Michael Fritz

            That’s it!

            FROM VBPA must appear first followed by INTO then it works here, too. The syntax check now is more accurate it seems.

            A small highlighting issue occurs however when writing it the way I’ve done it before. Although the statement is syntactically correct, the editor complaints about the INTO statement – see attached image.

            /wp-content/uploads/2015/07/2015_07_20_100732_751306.png

            (0) 
            1. Horst Keller Post author

              Must correct myself.

              SELECT SINGLE carrid, connid

                     FROM spfli

                     INTO (@DATA(w1),@DATA(w2)).

              is available since 7.40, SP08 (kernel 742). Since then the INTO clause can and should be written as the last clause. Using new features like inline declarations enforces that.

              Sorry …

              (0) 
  6. Raju Shrestha

    Horst – In the below snippet, why do we need INTO @DATA(result)? Can we just check SY-SUBRC and get rid of result variable?

    SELECT SINGLE @abap_true

           FROM t100

           WHERE sprsl = @sy-langu AND

                 arbgb = ‘SABAPDEMOS’

           INTO @DATA(result).



    (0) 

Leave a Reply