Skip to Content
Author's profile photo Horst Keller

ABAP News for 7.40, SP08 – Open SQL

The most important news for Open SQL in ABAP 7.40, SP08 are as follows:

Inline Declarations after INTO

You might like this one. From 7.40, SP08 on you can place inline declarations with the declaration operator DATA( … ) that was introduced with 7.40, SP02  after INTO.

DATA id TYPE scarr-carrid.
cl_demo_input=>request( CHANGING field = id ).

SELECT SINGLE carrname AS name, carrid AS id
       FROM   scarr
       WHERE  carrid = @id
       INTO @DATA(result).

cl_demo_output=>display( result ).

Or for a table

SELECT carrname AS name, carrid AS id
       FROM   scarr
       INTO TABLE @DATA(result).


cl_demo_output=>display( result ).

Either an elementary data object, a structure, or an internal table is declared depending on the results set defined in the SELECT list. See the documentation for details of the type construction.

SQL Expressions

The SQL expressions introduced with 7.40, SP05 into the SELECT list were enhanced with 7.40, SP08 as follows:

  • You can use SQL expressions after GROUP BY
  • You can use SQL expressions together with aggregates
  • You can use SQL expressions as argument of aggregates
  • You can use a seached CASE expression besides the simple CASE

Example for a searched case:

SELECT num1, num2,

       CASE WHEN col1 <  50 AND col2 <  50 THEN @both_l

            WHEN col1 >= 50 AND col2 >= 50 THEN @both_gt

            ELSE @others

       END AS group

       FROM demo_expressions

       ORDER BY group

       INTO TABLE @DATA(results).

Column Specification

In the SELECT list, you can specify all columns of a data source using the syntax data_source~* from  7.40, SP08 on. This can be handy when working with joins.

SELECT scarr~carrname, spfli~*, scarr~url

       FROM scarr INNER JOIN spfli ON scarr~carrid = spfli~carrid

       INTO TABLE @DATA(result).

Position of INTO

Did you realize the position of INTO in the above examples? I positioned it after the other clauses. This was not possible before. From 7.40, SP08 on, the INTO clause can and should (haha) be used after the other clauses of a SELECT statement. The additions UP TO n ROWS, BYPASSING BUFFER, and CONNECTION that are not treated as clauses must then be placed after the INTO clause.

The rationale behind this change is, that the INTO clause is not part of standard SQL but defines the data interface between SQL and ABAP. In order to enable future enhancements in the SQL part of Open SQL, e.g. UNION, the INTO clause has to be removed from the SQL part.

Removal of Restrictions and New Restrictions

Some restrictions have been removed. E.g. from 7.40, SP08 on you can place a minus sign in front of an operator of an arithmetic expression in the SELECT list, you can carry out a simple CASE for aggregates, you can use LIKE and IN (…) in join conditions after ON, you can specify a subquery after WHERE dynamically.

But if you use any of the new things listed here, as already for SP05, the syntax check for Open SQL is carried out in a strict mode, where stricter syntax rules apply. E.g. you must use comma separated lists and the escape symbol @ in front of host variables. By this, at least in Open SQL ABAP enforces a release dependent  deprecation concept in a downward compatible way. Now what do you think about that?

Assigned Tags

      38 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Now what do you think about that?

      Horst (err, i meant ABAP) is getting cooler with every release 😉

      Author's profile photo Nikolay Evstigneev
      Nikolay Evstigneev

      Horst, it's a tremendous update!!!

      One of my fellows used to work with a non-SAP system which supported such features. And he was looking forward to getting the possibility of writing SQL queries as he had accustomed to.

      And special thanks from me for

      SELECT spfli~*

      You made my day week! 🙂

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

      Not I, but my Open SQL colleagues 😉

      Author's profile photo Nikolay Evstigneev
      Nikolay Evstigneev

      Without you we would still have been in the dark about it.

      Don't be overmodest 🙂

      And of course, many thanks to the guys from Open SQL team.

      New ABAP. M-m-m, I'm loving it (c)

      Author's profile photo Daniel Rothmund
      Daniel Rothmund

      Hi Horst ,

      i tried the :

      SELECT carrname AS name, carrid AS id

             FROM   scarr

             INTO TABLE @DATA(result).

      on my 7.40 SPS7 system . 

      "I get a syntax error : The inline declaration "DATA(RESULT)" is not possible in this position"

      Does  I need SP 8 ?

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

      um, the title of the blog says "ABAP News for 7.40, SP08".

      Author's profile photo Daniel Rothmund
      Daniel Rothmund

      Ok ,

      then I misunderstood the sentence:

      'You might like this one. You can place inline declarations with the declaration operator  DATA( ... ) that was introduced with 7.40, SP02  behind INTO now.'

      sorry

      Author's profile photo Christiano José Beltrão Magalhães
      Christiano José Beltrão Magalhães

      Great blog... 🙂

      Thanks...

      Author's profile photo Uwe Fetzer
      Uwe Fetzer

      Today I already wanted to use the new SQL syntax for a client report, but went into a problem.

      The following two statements work (and no, this is not from the client report):

      SELECT carrid,

             connid,

             fldate,

             'X' AS test

        INTO TABLE @DATA(lt_result)

        FROM sflight

        WHERE carrid = @carrid.

      and

      SELECT carrid,

             connid,

             fldate

        INTO TABLE @DATA(lt_result)

        FROM sflight

        FOR ALL ENTRIES IN @carrids

        WHERE carrid = @carrids-carrid.

      But if I want to combine them, I'm getting a syntax error.

      SELECT carrid,

             connid,

             fldate

             'X' AS test

        INTO TABLE @DATA(lt_result)

        FROM sflight

        FOR ALL ENTRIES IN @carrids

        WHERE carrid = @carrids-carrid.

      Description Resource Path Location Type
      Host variables and literals are not permitted in expressions together with the addition FOR ALL ENTRIES. YSTSTF00 (Program) [A4H] YSTSTF00 line 17 ABAP Syntax Check Problem

      I don't see any (good) reason why this shouldn't be allowed.

      (and maybe the "together" should be placed directly after "literals" to make the message clearer because it seems only the combination of both is not valid -> "Host variables and literals together are not permitted in expressions with the addition FOR ALL ENTRIES.")

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

      Up to now, SQL expressions (literals and host variables in the SELECT list are a special case of SQL expressions) cannot be used together with FOR ALL ENTRIES. Simply a question of workload on the developer. But there are plans to tackle that too.

      Author's profile photo Daniel Rothmund
      Daniel Rothmund

      One question are there any plans for SUBSTR function or LEFT / RIGHT? I don't found any infos about it in ABAP Keyword Documentation .

      f.e.

      select  so_id , delivery_status from snwd_so  WHERE SUBSTR(so_id,1,3)='123'  into table @DATA(lt_simple_case).

      In the past we must select the data from the database and then do a loop and drop out the lines which doesn't mach the abap substr in the loop.

      Regards

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

      There are indeed such plans. In the end, Open SQL should support the same functions and expressions as the ABAP CDS - both are open and have the same code base.

      You will use the advanced view building capabilities of CDS in order to persist your model and Open SQL - well as embedded SQL within ABAP programs.

      As long as Open SQL does not offer the same functions and expressions as ABAP CDS, you can create a CDS view that encapsulates your SELECT with a SUBSTRING function and select it with Open SQL as a workaround.

      Author's profile photo Jacques Nomssi
      Jacques Nomssi

      Hello Horst,

      is this a known issue?

        PERFORM test1.  " OK

        PERFORM test2.  " <-- SHORT DUMP SAPSQL_DATA_LOSS

      FORM test1.

        CONSTANTS c_langu TYPE char10 VALUE 'ENGLISH'.

        DATA ls_t100 TYPE t100.

        SELECT SINGLE * FROM t100 INTO ls_t100

          WHERE sprsl = c_langu

            AND arbgb = '06'

            AND msgnr = '000'.

      ENDFORM.

      FORM test2.

        CONSTANTS c_langu TYPE char10 VALUE 'ENGLISH'.

        SELECT SINGLE * FROM t100

          WHERE sprsl = @c_langu

            AND arbgb = '06'

            AND msgnr = '000'

          INTO @DATA(ls_t100).

      ENDFORM.

      best regards,

      JNN

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

      Sure, and documented too 😉 .

      Strict Mode in Release 7.40, SP08

      If the content of a host variable read in an operand position needs to be converted to the target type, this is done using the rules for lossless assignments. If the assignment cannot be lossless, an exception is raised.

      And that is good, as your example shows ...

      Author's profile photo Sudip Kumar Das
      Sudip Kumar Das

      Hi Horst

      I am driving multiple SoH migration and S/4HANA Conversion project. When we do remediation we change all Select Single ( Which are reported in SCI ) as per requirement and we use inline declarations.

      In few SELECT up to 1 rows , we get dump for the SAPSQL_DATA_LOSS . So we need to rework on those after getting the defects .

      Why Inline declaration not able to declare a compatible target structure for few cases ! How to identify the cases where we should avoid inline declaration in SELECT statement , to avoid the defects or rework .

      One Example given below where I got the same dump and then I use explicit internal table declaration instead of inline declaration .

      Regards

      Sudip

      Author's profile photo Johann Fößleitner
      Johann Fößleitner

      Hello Horst,

      we have Release 7.40 SP11. I wonder of the result of the following SQL Expression:

        SELECT CASE WHEN type = '1' THEN 'A'
                   
      WHEN type = '2' THEN 'BBB'
                   
      ELSE 'CC'
               
      END AS group,
                partner
      ,

                partner_guid
               
      FROM but000
               
      INTO TABLE @DATA(lt_result)
               
      ORDER BY group.


      The first column of lt_result is a CHAR, length 2! (I would expect 3 because of the 'BBB':


      sql expressions.png

      If I remove the "ELSE" of CASE, then the column has the correct length of 3.

      Is that correct?

      Regards, Johann Fößleitner

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

      Hello Johann,

      This is a known bug that came in with 7.40, SP08 (kernel 7.42) and is solved with note 2127182.

      Horst

      Author's profile photo Former Member
      Former Member

      Hello Horst,

      I am finally able to use the SP08 features.  But even though the new position of the INTO-Clause works fine for the syntaxchecker, the editor doesn't really seem to like it and marks it as faulty.

      Unbenannt.png

      Do I need a newer SAPGUI version than 740, Patch 5?

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

      Yes, some new version should do it ...

      Author's profile photo Former Member
      Former Member

      Hmm - the SAPGUI doesn't seem to be the problem.  I installed the very latest GUI-Patch ( 7.40 Patch 6 ) wich was released just in these days and the problem remains.

      Since I get the same coloring using Eclipse maybe it's not a GUI-Problem at all. Any ideas what else might cause this?  It's quite annoying to see correct code being marked incorrect.

      Author's profile photo Michael Calekta
      Michael Calekta

      Do I assume right, that when I code:

      SELECT * FROM t001
                      INTO TABLE @DATA(lt_t001).

      that the resulting table is of the type STANDARD TABLE WITH EMPTY KEY, or does the table inherit the primary keys of the database-table?
      If I used the ORDER BY clause - would I then get a SORTED TABLE?

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

      No need to assume, you can read it here

      "INTO TABLE @DATA(itab) declares a standard table itab ... with an empty table key":

      No exceptions from that rule.

      Author's profile photo Michael Calekta
      Michael Calekta

      Thanks for clearing this point - should have remembered, that the documentation has been updated as well ... silly me.

      Author's profile photo Aasim Khan
      Aasim Khan

      Hello Horst,

      We're on 740/SP13 where the below SELECT statement with GROUP BY..CASE works perfectly fine.

      SEL1.JPG

      However, when I use a string expression in GROUP BY..CASE, it gives me an error for the below SELECT statement.

      SEL2.JPG

      Err.JPG

      Does this mean, GROUP BY..CASE works only for numeric expressions and not for string?

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

      Please, simply have look into the documentation, this restriction is clearly described there:

      • The expressions cannot contain any host variables or ABAP literals as operands, with the exception of statically identifiable constants or literals with the
      • data type i with any content, but not as an argument of the arithmetic functions or of the functioncoalesce.
      • data type c with length 1 and the content "X" or a blank, but not as an argument of the functioncoalesce.
      Author's profile photo Marco Bohmbach
      Marco Bohmbach

      Is there a chance to get a case insensitive open sql search? It would be useful in association with odata.

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

      In fact there is a very good chance for SQL-functions UPPER and LOWER ...

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

      https://blogs.sap.com/2016/10/18/abap-news-release-7.51-case-insensitive-search-sql-new-functions/

      Author's profile photo Former Member
      Former Member

      I wonder - how does the ABAP - compiler distinguish between commas from a comma-separated column list in the select-clause and the commas used with the ":"

      i.E.
      UPDATE table SET : field1 = value1, field 2 = value2 where condition.

      Does the "," belong to the column-list or does it belong to the ":"
       

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

      Since you don't have @ in front of value1, value2, it is old syntax and a comma separated list isn't possible anyhow.

      But in fact, the colon always wins and writing the statements separately must result in correct syntax, either old or new.

      Author's profile photo Martin Krupka
      Martin Krupka

      Is it not possible to use a NUMC column in a CASE comparison? I get an error "Comparisons using < in this position are possible only if GJAHR has a numeric type."

      In my case GJAHR is NUMC4

      Author's profile photo Martin Krupka
      Martin Krupka

      Never mind, found the solution. I tried casting GJAHR before I posted but got a different error. A closer look at the documentation then brought up the idea to reverse the operands and have the casted GJAHR as operand2. This works for me:

      case when @lv_gjahr castgjahr as int2 then...

      Author's profile photo Marc Cawood
      Marc Cawood
      1  DATA(lv_where) = 'equnr IN ( SELECT equnr FROM y00_hyb_equi  )'.
      2  DATA tt TYPE STANDARD TABLE OF equi.
      3  SELECT equnr FROM equi INTO TABLE tt WHERE equnr > 0
      4    AND equnr IN ( SELECT equnr FROM y00_hyb_equi )
      5    "AND (lv_where)
      6   .
      

      This code runs fine. However, when one comments out line 4 and uncomments line 5 we get runtime errors (dumps) CX_SY_DYNAMIC_OSQL_SEMANTICS complaining about not using the "new OpenSQL syntax".

      This is unexpected since both SQL statements are identical. What are we missing? The dynamic WHERE works fine with:

      lv_where = `equnr IN ( '000000000000010090', '000000000000010091' )`

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

      What are we missing?

      http://help.sap.com/abapdocu_751/en/index.htm?file=abenopensql_strict_mode_740_sp08.htm

      ◾Subquery specified in a dynamic WHERE condition 

      Author's profile photo Amy King
      Amy King

      Hi Horst,

      Am I correct that the CASE statement cannot include OR for single operands--I haven't been able to get this to work, e.g.,

      SELECT num1, num2,
             CASE num3
                 WHEN '1' OR '2' THEN num3 " accept field value
                 ELSE '3' " use a default
             END
             ...

      Cheers,

      Amy

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

      Nope, but see https://help.sap.com/http.svc/rc/abapdocu_752_index_htm/7.52/en-US/index.htm?file=abensql_searched_case.htm

      Author's profile photo Alvin Santiago
      Alvin Santiago

      Hi Horst,

      We have 7.4 SP15

      I want to use CAST in select where the field will be referenced to a DATA ELEMENT. Is this already possible? If yes, how to properly code it?

      Currently, I am limited only to referring my cast to built-in datatype. Please see my code below.

      SELECT
          a~matnr,
          a~meins,
          b~maktx,
          c~werks,
          c~lgort,
          c~labst,
          CAST( 0 AS QUAN( 15, 3 ) ) AS qty
        INTO TABLE @DATA(li_raw)

       

      Thanks in advance!

      Author's profile photo Ashutosh Argawal
      Ashutosh Argawal

      Hi Horst,

       

      How can I read just last record in a select query.

      for example ; A simple SQL

       

      Select vbeln from vbak where "some condition"  order by vbeln descending.

      i need to write the same thing in ABAP.

       

      thanks.