Friday, March 2, 2018

ABAP from SELECT FROM @ITAB


https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abapselect_itab.htm


SELECT - FROM @itab
Syntax
... @itab
Effect
Specifies an internal table itab, whose name must be prefixed with the @ character, as a data source of a query. The SELECT statement handles the internal table of the application server like a database table on the database. The ABAP types of the columns in the internal table are mapped to suitable built-in data types in ABAP Dictionary. If a column is declared with reference to a type in ABAP Dictionary, this type is used directly.
There are two different cases here:
  • The data in the internal table is not required on the database. 
In this case, the data of the internal table is accessed on the application server and the table is handled like a table in the table buffer. This is possible for all database platforms.
  • The 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. This option is not supported by all databases. If it is known statically that the data is required on the database, a syntax check warning occurs that can be hidden by the pragma ##db_feature_mode[itabs_in_from_clause]. If an attempt is made to pass data from the internal table to a database at runtime and the database does not support this, a handleable exception of the class CX_SY_SQL_UNSUPPORTED_FEATURE is raised.
The data in the internal table must not be passed to the database (or the statement can be executed on the application server) in precisely those cases where the SELECT statement meets the same conditions as when the table buffer is accessed in table buffering.
The ABAP types of the columns in the internal table are mapped to types in ABAP Dictionary. If a column is declared with reference to a type in ABAP Dictionary, this type is used directly.
The following conditions apply:
  • Only one internal table can be specified in an Open SQL statement.
  • An alternative table name must be assigned to the internal table using AS.
  • The row type of the internal table can be elementary or structured. 
  • An elementary row type represents a column that can be addressed in the other clauses of the query using the name table_line or an alternative column name defined in the SELECT list after AS.
  • A structured row type cannot contain any substructures.
  • No deep row types are allowed. An elementary row type cannot be a string or a reference type and a structured row type cannot contain any strings, reference types, or internal tables as components, with the following exception: An elementary row type or a component with the type string is allowed if this is declared using a reference to the built-in dictionary type SSTRING.
  • If the addition ORDER BY PRIMARY KEY is used, the internal table must have a primary table key.
  • The key fields of a primary table key in the internal table must be contiguous columns that appear in the same order at the start of the row type.
  • If the FROM clause is specified statically, the internal table cannot be a generically typed formal parameter or a generically typed field symbol. Objects like this can only be specified in a dynamic FROM clause and must represent a matching internal table at runtime.
  • The internal table cannot contain any columns declared with reference to the obsolete dictionary types DF16_SCL and DF34_SCL.
  • The internal table should have an explicitly defined primary key (which can be empty). Generic primary keys and standard keys are not evaluated when read and a syntax check warning occurs. 
The data in the internal table is handled like data on the database even if it is not transported to the database. In particular, data with the type string declared using a reference to the built-in dictionary type SSTRING is handled like text fields with fixed lengths in which trailing blanks are ignored.
The internal table itab is always regarded as a cross-client table. The data type of the first column is never regarded as a client column, regardless of its data type.
Notes
  • The data in internal tables should only be transported to the database system if it is actually needed there.
  • The use of an internal table as a data source of SELECT is mainly applicable to joins with data sources from the database. In this case, the data of the internal table is transported to the database.
  • An internal table as a data source of SELECT is also, however, a potential alternative to the statements READ TABLE and LOOP AT and hence makes Open SQL syntax possible that cannot otherwise be used. If this solution is used, it should be ensured that the data is evaluated on the application server and that no data is transported to the database system to be evaluated.
  • The fact that, when accessed on the application server without data transport, the data in the internal table is handled like a table in the table buffer does not mean that the data is actually stored here. The appropriate restrictions do, however, apply to the SELECT statement.
  • In an ABAP program, it is possible to use the method USE_FEATURES of the class CL_ABAP_DBFEATURES to check whether the current database system or a database system accessed using a secondary connection supports access to internal tables. This requires the constant ITABS_IN_FROM_CLAUSE of this class to be passed to the method in an internal table.
  • Access to an internal table as a data source of SELECT should not be seen as an alternative to the statements READ TABLE and LOOP AT. More specifically, data should only be transported to the database when it is actually needed there.
  • In internal tables with elementary row types, the name of the pseudo component table_line cannot be the name of a structure in ABAP. An inline declaration can only be made in the INTO clause if the column table_line of the results set is assigned an alternative column name using AS.
  • When an internal table with elementary row type is accessed in a common table expression after WITH, the SELECT list cannot be * or contain data_source~*.
  • If an internal table is used as a data source, the syntax check is performed in strict mode from Release 7.52
Example
Uses a table of random numbers as a data source of a SELECT statement. The data is transported to the database for sorting and the statement can only be executed in database systems where sorting is supported.
TYPES:
  BEGIN OF line,
    id     TYPE c LENGTH 1,
    number TYPE i,
  END OF line.

DATA itab TYPE HASHED TABLE OF line
          WITH UNIQUE KEY id.

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.

DATA(rnd) = cl_abap_random_int=>create(
  seed = CONV i( sy-uzeit ) min = 1 max = 100 ).
itab =  VALUE #(
  FOR i = 1 UNTIL i > 25
  ( id = substring( val = sy-abcde off = i len = 1 )
    number = rnd->get_next( ) ) ).

SELECT *
       FROM @itab AS numbers
       WHERE number > 50
       ORDER BY id
       INTO TABLE @DATA(result)
       ##db_feature_mode[itabs_in_from_clause].

cl_demo_output=>display( result ).
Example
Uses an internal table as the data source of an inner join of a SELECT statement. The data is transported to the database for the join and the statement can only be executed in database systems where joins are supported.
DATA itab TYPE HASHED TABLE OF scarr
          WITH UNIQUE KEY mandt carrid.

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.

itab =  VALUE #( ( carrid = 'LH' carrname = 'L.H.' )
                 ( carrid = 'UA' carrname = 'U.A.' ) ).

SELECT scarr~carrid, scarr~carrname, spfli~connid
       FROM @itab AS scarr
         INNER JOIN spfli ON scarr~carrid = spfli~carrid
       INTO TABLE @DATA(result)
       ##db_feature_mode[itabs_in_from_clause].

cl_demo_output=>display( result ).
Example
Uses a table with an elementary data type as the data source of two SELECT statements. The data is not needed on the database and the SELECT statements meet the requirements for table buffering. This means that the statements can be executed on all database systems. In the second SELECT statement, a syntax error would occur without the alternative column name number, since the inline declaration after INTO cannot create an internal table with the column name table_line. If the statements were modified in a such a way that they no longer met the requirements for table buffering (for example by adding the addition DISTINCT), it would not be possible to execute them on all database systems.
DATA itab TYPE SORTED TABLE OF i WITH UNIQUE KEY table_line.
itab =  VALUE #( ( 1 )
                 ( 2 )
                 ( 3 ) ).

DATA result1 LIKE itab.
SELECT table_line
       FROM @itab AS numbers
       INTO TABLE @result1.
cl_demo_output=>write( result1 ).

SELECT table_line AS number
       FROM @itab AS numbers
       INTO TABLE @DATA(result2).
cl_demo_output=>display( result2 ).




--------------------------------------

SELECT FIELD1, FIELD2, FIELD3 AS FIELD4 INTO TABLE LT_NEWITAB
     FROM ITAB.



No comments:

Post a Comment