Technical Articles
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.
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".
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.
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.
Thanks for sharing, Horst! There is some interesting discussion on Twitter on this, here is the link: https://twitter.com/JelenaAtLarge/status/1514688663652904964
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 ...