Skip to Content
Author's profile photo Horst Keller

Why the new Open SQL Syntax is Better

In a recent but meanwhile deleted discussion, the question was how to compare fields of one and the same database table in a SELECT statement.

For experienced ABAPers or those who have a look into the documentation it is clear that the solution can be something like:

SELECT carrid connid fldate seatsocc seatsmax
       FROM sflight
       INTO TABLE sflight_tab
       WHERE seatsmax < sflight~seatsocc.

But a rookie might tend to write:

SELECT carrid connid fldate seatsocc seatsmax
       FROM sflight
       INTO TABLE sflight_tab
       WHERE seatsmax < sflight-seatsocc.

Missing the difference between – and ~.

Of course the second example is only possible, if you have something like the following in front of the SELECT statement:

DATA:
  BEGIN OF sflight,
    carrid   TYPE sflight-carrid,
    connid   TYPE sflight-connid,
    fldate   TYPE sflight-fldate,
    seatsocc TYPE sflight-seatsocc,
    seatsmax TYPE sflight-seatsmax,
  END OF sflight,
  sflight_tab LIKE STANDARD TABLE OF sflight WITH EMPTY KEY.

Or even a TABLES statement, arrgh …

It should be clear that in the second example, the comparison takes place between a database field on the LHS and an ABAP data object on the RHS.

But that can be made much more clear by using contemporary Open SQL syntax with comma separated lists and a @ in front of host variables.

The following gives a syntax error!

SELECT carrid, connid, fldate, seatsocc, seatsmax
       FROM sflight
       WHERE seatsmax < sflight-seatsocc
       INTO TABLE @sflight_tab.

You have to decide deliberately, whether you really want to compare with an ABAP field:

SELECT carrid, connid, fldate, seatsocc, seatsmax
       FROM sflight
       WHERE seatsmax < @sflight-seatsocc
       INTO TABLE @sflight_tab.

or with a database field:

SELECT carrid, connid, fldate, seatsocc, seatsmax
       FROM sflight
       WHERE seatsmax < sflight~seatsocc
       INTO TABLE @sflight_tab.

 

Cheers!

Assigned tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Matthew Billingham
      Matthew Billingham

      Can this be the beginning of a series? One of the issues my colleagues have with the new stuff in ABAP is “why?”. So to that end, may I suggest

      • Why Methods are better than Forms
      • Why Table Comprehensions (FOR etc.) are better than LOOP AT…

      (Alright, I confess. That second one is from me. I can guess why, but I'd like to know...!)

       

       

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

      "Why Methods are better than Forms?"

      We’ve written books about that …

      "Why Table Comprehensions (FOR etc.) are better than LOOP AT…"

      Who says so? Better:

      "When Table Comprehensions (FOR etc.) are better than LOOP AT…"

      Author's profile photo Matthew Billingham
      Matthew Billingham

      OK then: “When Table Comprehensions (FOR etc.) are better than LOOP AT…”

      Author's profile photo Jitendra Soni
      Jitendra Soni

      Hi Horst,

      I am looking for this. Could you please comment?

      When Table Comprehensions (FOR etc.) are better than LOOP AT…” ?

      Author's profile photo Ruthiel Trevisan
      Ruthiel Trevisan

      Thanks a lot for the lesson!