Personal Insights
Select From @internal table
We all are using subqueries or the FOR ALL ENTRIES to fulfill business requirements. Sometimes a few of them are result in long or inefficient codes.
Few of the scenarios are –
- FOR ALL ENTRIES cannot be combined with the SQL Expressions like aggregate expressions.
- GROUP BY clause is not allowed to use with FOR ALL ENTRIES statement.
ABAP NW 7.52 has come up with new syntax to select the data directly from the internal table as a data source. There is no need to use FOR ALL ENTRIES or split up into multiple select statements.
These can be achieved through new syntax SELECT FROM @ITAB
New syntax will select the data from an internal table rather than a database table as a data source.
There are 2 uses cases for the scenario
- Data in the internal table is not required in the database
In this case, data of the internal table is accessed on the AS ABAP and the table is handled like a table in the table buffer. This is possible for all database platforms.
In the below code, method USE_FEATURES of class CL_ABAP_DBFEATURES being used to validate if the system supports an internal table as the data source.
TYPES: BEGIN OF ts_table,
id TYPE i,
name TYPE char5,
END OF ts_table.
DATA itab TYPE SORTED TABLE OF ts_table WITH UNIQUE KEY id.
itab = VALUE #( ( id = 1 name = 'one' )
( id = 2 name = 'two')
( id = 3 name = 'three' ) ).
DATA result1 LIKE itab.
IF NOT cl_abap_dbfeatures=>use_features(
EXPORTING
requested_features =
VALUE #( ( cl_abap_dbfeatures=>itabs_in_from_clause ) ) ).
cl_demo_output=>display(
`System does not support internal tables as data source` ).
RETURN.
ENDIF.
"First Query with Where clause
SELECT id , name
FROM @itab AS numbers
WHERE id = 2
INTO TABLE @result1.
cl_demo_output=>write( result1 ).
"Selection of few columns
SELECT id AS number
FROM @itab AS numbers
INTO TABLE @DATA(result2).
cl_demo_output=>display( result2 ).
- Data in the internal table is required on the database
In this case, the data must be passed to temporary tables in the database before the query is actually executed.
Here, only required/accessed columns are transported to the database.
This option does not support all databases. A syntax check warning occurs which can be hidden by pragma ##itab_db_select , if data is required at the database.
In below code, we need total seat occupied by Airline with connection ( cityfrom -> cityto)
IF NOT cl_abap_dbfeatures=>use_features(
EXPORTING
requested_features =
VALUE #( ( cl_abap_dbfeatures=>itabs_in_from_clause ) ) ).
cl_demo_output=>display(
`System does not support internal tables as data source` ).
RETURN.
ENDIF.
"Get total seat occupied for current year
SELECT carrid ,connid , SUM( seatsocc ) AS seats_occ
FROM sflight
INTO TABLE @DATA(lt_sflight)
WHERE fldate BETWEEN '20190101' AND @sy-datum
GROUP BY carrid, connid.
"Get result data with city from and city to and occupied seat.
SELECT sflight~carrid, sflight~connid, spfli~cityfrom ,spfli~cityto , sflight~seats_occ
FROM @lt_sflight AS sflight
INNER JOIN spfli
ON sflight~carrid = spfli~carrid
AND sflight~connid = spfli~connid
INTO TABLE @DATA(result)
##db_feature_mode[itabs_in_from_clause] ##itab_db_select.
cl_demo_output=>display( result )
Output
Following conditions are applied to the new syntax in an Internal table.
- The select statement must be prefixed with the @ character
- Only one internal table as a source can be specified in the select statement
- There must have an alias using AS Statement
- It should not have deep structures or contains type string
- It should not contain any obsolete data type
Conclusions
- Data in the internal table should only be transported to the database system if it actually needed there. It is mainly applicable to join conditions with other data sources from the database. There are other alternatives available like GTT or CTE if the internal table does not need any data manipulation before using in a SELECT statement
- There are other alternatives like READ TABLE and LOOP_AT to fetch from an internal table where it is not required in a data source. Access using new syntax is relatively slower than the available alternative and should be used in cases which are not covered by them
Reference – Abapselect_itab
https://blogs.sap.com/2018/12/26/for-all-entries-alternatives/
same blog posted already what else new here?
Regards,
Sri
Hi Srinivasa,
The blog you have mentioned gives us information about the new syntax.
The blog I have written gives more detailed information -
these are personal insight with more in details.
I searched 'select from internal table' on google, and this blog post was on top of the list.
It means this article is more popular on this specific topic than yours.
And at least 18 people like this post, more than 34k people have seen this article.
Agree with the previous comment. Going forward, please search for existing blogs before posting the same information.
Hi Jelena,
I am not totally agree with your views. previous blog gives alternative for all entries without much details and scenarios and their performance outcomes.
there are so many details that is added as per personal insight.
The old blog wasn't perfect either but, to be honest, there were less problems with it. It was just informing that new features are available and referencing other blogs or SAP Help.
This blog seems to concentrate on "using internal table as a source", which is item 3 of the old blog. "FOR ALL ENTRIES and subqueries" that are mentioned in the opening paragraph are used when selecting data from DB only. The first scenario, while demonstrating the use of "internal table as a source"concept has nothing to do with FAE since no DB is involved. In this case new syntax is sort of an alternative for LOOP AT...
Second scenario is OK, not much different from the example from the old blog IMHO. I suspect any ABAPer with more than a month experience has no problem visualizing a result of SELECT statement. But that's fine.
"The select statement must be prefixed with the @ character" - this is actually incorrect. It's the table name, not SELECT statement that is prefixed. (It's already clear from the title, anyway.)
"Many details" pretty much looks like ABAP documentation with worse verbiage choices.
Blog: "Access using new syntax is relatively slower than the available alternative and should be used in cases which are not covered by them"
ABAP Help: "The use of SELECT to access an internal table is usually slower than the statements for internal tables and should only be used in cases not covered by these statements."
If you wanted to create a better blog you could've created more complex examples (e.g. 1st scenario can be easily done without new syntax, so why even use it?), offered some actual performance evaluation (e.g. ABAP Help says "slower" but by how much?), etc. Lots of opportunities.
Going further down the blog RSS feed also found this blog which actually has some performance analysis already: https://blogs.sap.com/2019/03/31/compare-performance-between-select-for-all-entries-and-amdp/
That's the type of content that is much more useful. Again - going forward, please research what's already posted on SCN and add links to the existing blogs instead of repetition.
Why are these people so toxic? I found out this article is more useful than blog you mentioned.
And SAP Help document contains almost every topic on sap blogs. but usually blogs like this has much refined information than any other official document you are refering to.
I hate that "don't post same thing again" kind of comment. It's not the same thing.
If you know already about it, just pass away please.
Is possible create dynamic itab using @DATA(itab) and passing itab how parameter in perform?
Hi Gercho,
sorry your question is not clear. are you looking to pass the @data(itab) as a parameter to subroutine ?
Please can somebody explain why is it obligatory to have an alias using AS Clause ?
Sorry AS is not a statement itself. I always found the AS used by many SAP developers in SELECT JOIN statements more confusing than clarifying.
Thanks, Clemens
Hi Clemens! Just noticed your comment and "gratuitous aliasing" is also my pet peeve. I might be able to shed some light on its origins. Back in the early 2000s, ABAP documentation examples all had aliases when showing SELECT... JOIN. From there, I'm guessing many developers assumed that it's how it's done and bad practice just spread out because no one questioned it much.
Personally, I came to realize within the first year of working as ABAPer that aliases are (a) not required, (b) when they are actually needed (e.g. joining same table twice) they should be more descriptive. And I believe not many developers ever question that or are curious what is that "AS" thingy. Which, sadly, goes for many other times in ABAP development world. We just don't question things enough.