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!