SELECT * EXCEPT col1, col2, ... in ABAP

[horst_keller]

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.