CREATE DATA: The specified type "BELNR,BUKRS,GJAHR,MANDT" is no valid data

Question

At the moment it does something wrong with the primary key when it collects them, some tables have more that one primary key.

The output error is this one for example if running the program with table BKPF, doing a short dump at line CREATE DATA lt_table_line TYPE (lv_keyfields_string).:

Category         CREATE_DATA_UNKNOWN_TYPE
Runtime Errors   CX_SY_CREATE_DATA_ERROR

Short Text
  CREATE DATA: The specified type "BELNR,BUKRS,GJAHR,MANDT" is no valid data.

(The random name in "" are the 4 primary keys.)

REPORT ZDUPLICATE_TEST.

PARAMETERS: p_table TYPE tabname.           " User-provided input parameter for the table name

DATA: lv_tabname           TYPE dd02l-tabname,        " Local variable to hold table name
      lv_primary_index     TYPE dd09l-tabname,        " Local variable to store primary index of the table
      lt_keyfields         TYPE TABLE OF dd03l-fieldname, " Internal table to store the field names of primary keys
      ls_keyfield          TYPE dd03l-fieldname,           " Work area (line type) for the internal table lt_keyfields
      lv_keyfields_string  TYPE string,                   " String to concatenate primary key fields
      lv_query             TYPE string,                   " String to build SQL query for finding duplicates
      lt_table             TYPE REF TO data,              " Data reference for dynamic table creation
      lt_table_line        TYPE REF TO data.              " Data reference for dynamic table line creation

FIELD-SYMBOLS: <lt_table> TYPE STANDARD TABLE,  " Field-symbol as a placeholder for a standard table
               <lt_table_line> TYPE any.        " Field-symbol as a placeholder for any line of the table

START-OF-SELECTION.

  SELECT SINGLE tabname INTO lv_tabname FROM dd02l WHERE tabname = p_table.  " Query to check if the input table exists in the system

  IF sy-subrc = 0.  " If the table exists, sy-subrc will be 0
    WRITE: / 'Table exists'.

    SELECT SINGLE tabname FROM dd09l INTO lv_primary_index WHERE as4local = 'A' AND tabname = p_table.  " Fetch the primary index of the table

    IF sy-subrc = 0.  " If the primary index exists, sy-subrc will be 0
      SELECT fieldname INTO TABLE lt_keyfields FROM dd03l WHERE tabname = p_table AND keyflag = 'X'.  " Fetch all the primary key fields of the table

    ENDIF.

    IF sy-subrc = 0 AND lt_keyfields IS NOT INITIAL.  " Check if the query was successful and there are primary key fields
      LOOP AT lt_keyfields INTO ls_keyfield.  " Loop through each primary key field
        IF lv_keyfields_string IS NOT INITIAL.  " If the string is not initial, concatenate the field name
          CONCATENATE lv_keyfields_string ',' ls_keyfield INTO lv_keyfields_string.
        ELSE.
          lv_keyfields_string = ls_keyfield.  " If it's the first field, just assign it to the string
        ENDIF.
      ENDLOOP.

      " Construct SQL query to find records that have duplicates based on the primary key fields
      lv_query = |SELECT { lv_keyfields_string }, COUNT(*) AS CNT FROM { p_table } GROUP BY { lv_keyfields_string } HAVING COUNT(*) > 1|.

      " Dynamically create a structure to hold the query result
      CREATE DATA lt_table_line TYPE (lv_keyfields_string).
      ASSIGN lt_table_line->* TO <lt_table_line>.

      " Dynamically execute the SQL query
      EXEC SQL.
        EXECUTE STATEMENT :lv_query INTO :<lt_table_line>.
      ENDEXEC.

      " If the query was successful, it means there are duplicates
      IF sy-subrc = 0.
        WRITE: / 'Duplicates found for the keys:', lv_keyfields_string.
        WRITE: / 'Key: ', <lt_table_line>, 'Count: ', sy-dbcnt.  " Output the duplicate key and count
      ELSE.
        WRITE: / 'No duplicates found'.  " Message when no duplicates are found
      ENDIF.

    ELSE.
      WRITE: / 'No primary keys found'.  " Message when no primary keys exist for the table
    ENDIF.

  ELSE.
    WRITE: / 'Can''t find table'.  " Error message when the table does not exist
  ENDIF.

Answer 1

Sorry, but CREATE DATA doesn't work that way. The variant that accepts a character sequences expects the name of a global type defined in the ABAP Dictionary. You can't give it a string representation of a runtime type.

But what you can do, is use the variant of CREATE DATA that accepts a type handle. Which in your case would be an instance of the class CL_ABAP_STRUCTDESCR. This example from the documentation shows you how you can create an instance of the class with arbitrary fields and then use it to create a runtime instance of it.

Note that it's not just enough to know the names. In order to construct the right structure type, you need to know the correct types of those fields as well. So you also need to select the type information from DD031. The simple solution which is going to work for most tables is to use the field DOMNAME:

SELECT fieldname, domname 
    INTO TABLE DATA(lt_keyfields)
    FROM dd03l 
    WHERE tabname = p_table 
      AND keyflag = 'X'.

DATA lt_struct_components TYPE abap_component_tab.
LOOP AT lt_keyfields INDO DATA(ls_keyfields).
    APPEND VALUE#(
          name = ls_keyfields-fieldname
          type = cl_abap_elemdescr=>describe_by_name( ls_keyfields-domname )
    ) TO lt_struct_components.
ENDLOOP.

CREATE DATA ls_table_line TYPE HANDLE cl_abap_structdescr=>get( lt_struct_components ).

Most SAP standard tables will use domain names to define the types of table fields. But in some cases developers might get lazy and use direct types. Should you stumble upon table fields like that in your use-case you have to look at the DD031 fields DATATYPE, LENG and DECIMALS. Which makes things more complicated, but not impossible.

An alternative solution would be to first get the struct description of the complete database table you want to select from and via cl_abap_structdescr=>describe_by_name( p_table ) and then create a second struct description by taking selected fields from it.