Skip to Content
Technical Articles
Author's profile photo Horst Keller

SELECT * EXCEPT col1, col2, … in ABAP

A syntax

SELECT * EXCEPT col1, col2, ...

to select all columns but those listed behind EXCEPT is not supported in most SQL variants, see e.g. discussions in stackoverflow.

Alas, it is not supported in ABAP SQL either.

But instead of working wit GTTs and ALTER TABLE as proposed in variuos discussions, we have another way, namely our dynamic tokens. Let’s look at an example.

Assume we want to select all columns from good old demonstration table SCARR except two of them.

DATA(dbtab)  = 'scarr'.
DATA(except) = 'carrid, url'.

We place the name of the database table in a variable dbtab and the exception list in a variable except.

Now we use RTTS in order to get a list of all columns except those from the exception list:

DATA(components) = CAST cl_abap_structdescr(
  cl_abap_typedescr=>describe_by_name( to_upper( dbtab ) )
    )->get_components( ).

SPLIT except AT `,` INTO TABLE DATA(columns).
LOOP AT columns ASSIGNING FIELD-SYMBOL(<column>).
  DELETE components WHERE name = to_upper( condense( <column> ) ).
ENDLOOP.

That should be self-explaining.

From the components table we can derive two things now.

A token string for the SELECT list:

DATA(token) =
  REDUCE string( INIT s = ``
                 FOR <wa> IN components
                 NEXT s &&=  COND #( WHEN s = ``  THEN <wa>-name
                                     ELSE  `, ` && <wa>-name ) ).

An appropriate target table:

DATA(target_type) =
 cl_abap_tabledescr=>get(
         p_line_type  = cl_abap_structdescr=>get(
                           p_components = components )
         p_table_kind = cl_abap_tabledescr=>tablekind_std ).
DATA target TYPE REF TO data.
CREATE DATA target TYPE HANDLE target_type.

And that’s that. Now we can simply write the ABAP SQL SELECT as  follows:

SELECT (token)
       FROM (dbtab)
       INTO TABLE @target->*.

And the result as shown by a recent version of CL_DEMO_OUTPUT

cl_demo_output=>display( target->* ).

is:

Note that we made no “automatic client handing” here, but you can introduce that easily.

After putting all the recurring stuff into helper methods, a final program might look as follows:

  DATA(sel_list) = NEW select_list( dbtab  = 'scarr'
                                    except = 'carrid, url' ).

  DATA(token)  = sel_list->get_token( ).
  DATA(target) = sel_list->get_target( ).

  SELECT (token)
         FROM scarr
         INTO TABLE @target->*.

  cl_demo_output=>display( target->* ).

A helper class might look as that:

CLASS select_list DEFINITION.
  PUBLIC SECTION.
    METHODS:
      constructor IMPORTING dbtab  TYPE string
                            except TYPE string,
      get_token   RETURNING VALUE(token)  TYPE string,
      get_target  RETURNING VALUE(target) TYPE REF TO data.
  PRIVATE SECTION.
    DATA
      components TYPE cl_abap_structdescr=>component_table.
ENDCLASS.

CLASS select_list IMPLEMENTATION.
  METHOD constructor.
    components = CAST cl_abap_structdescr(
      cl_abap_typedescr=>describe_by_name( to_upper( dbtab ) )
        )->get_components( ).

    SPLIT except AT `,` INTO TABLE DATA(columns).
    LOOP AT columns ASSIGNING FIELD-SYMBOL(<column>).
       DELETE components WHERE name = to_upper( condense( <column> ) ).
    ENDLOOP.
  ENDMETHOD.
  METHOD get_token.
    token =
      REDUCE string( INIT s = ``
                     FOR <wa> IN components
                     NEXT s &&=  COND #( WHEN s = ``  THEN <wa>-name
                                         ELSE  `, ` && <wa>-name ) ).
  ENDMETHOD.
  METHOD get_target.
    DATA(itab_type) =
     cl_abap_tabledescr=>get(
             p_line_type  = cl_abap_structdescr=>get(
                               p_components = components )
             p_table_kind = cl_abap_tabledescr=>tablekind_std ).
    CREATE DATA target TYPE HANDLE itab_type.
  ENDMETHOD.
ENDCLASS.

For the simple demonstration, we omitted all exception handling and did not exclude the client column, which can easily done by an additional flag.

PS: Note that we use most modern syntax for dereferencing data references here. In older releases you still need the well-known workaround using an intermediate field symbol.

Assigned Tags

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

      Note that we made no “automatic client handing” here, but you can introduce that easily.

      I hope that means "we didn't remove the client column from the output table" and it does not mean "a dynamic from clause causes the implicit where and on conditions on the client key field to not be added even without CLIENT SPECIFIED being used".

       

       

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

      Since the client column is part of the columns in the COLUMNS table, it is explicitly read in the example above. If you don't want to see it, you must remove it additionnally.

      Author's profile photo Fabian Lupa
      Fabian Lupa

      Ok, that's the good ending.

      I associated the filter on the logon client with "automatic client handling" (like how that is not the case in AMDP / native SQL) so I got a bit scared that a dynamic from clause could somehow disable it in ABAP SQL without client specified.

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      Thanks for sharing, Horst! There is some interesting discussion on Twitter on this, here is the link: https://twitter.com/JelenaAtLarge/status/1514688663652904964

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

      If the discussion were here, I would answer that INTO CORRESPONDING is not sufficient for that task and that dynamic tokens also use the table buffer ...