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!
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
(Alright, I confess. That second one is from me. I can guess why, but I'd like to know...!)
"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…"
OK then: “When Table Comprehensions (FOR etc.) are better than LOOP AT…”
Hi Horst,
I am looking for this. Could you please comment?
When Table Comprehensions (FOR etc.) are better than LOOP AT…” ?
Thanks a lot for the lesson!