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:
No comments:
Post a Comment