Display table that can be seen only in ST04 sql

[Former Member]

Hi there,

I have a table that can be accessed in SQL in ST04, but doesn't exists in SE16.

I need to bring that table to ALV so I want to create a report but I can not accese that table...

thanks,

Tomas.

[former_member182550]

How did you create this table ?

[Former Member]

Hi Richard,

basically this table serves as a logging table to catch any change done in InfoCube through the Analysis for Office frontend.

"Create a Database Table directly in HANA If you need more than 16 logical key fields and do not want to use a technical key as described above you can create the database table directly in HANA. Here we have to keep in mind that the BW and thus the logging BAdI implementation can only access tables in the BW related database schema. If you start HANA studio then you can only create a table in this database schema if you are logged on as the main database user used by the ABAP server/BW. Thus it is easier to use an ABAP report that generates the database table in the correct schema from the application server. Please go to transaction se38 and create a new program. Use the ABAP coding from the appendix. Do not forget to activate the report. Now you can execute the report. On the initial screen please enter the name of the handshake structure you have created above and press the execute button. The report will read the definition of the structure and generate a database table directly in HANA. The table contains the same fields as the structure. As we do not use a primary key in our table the table can have more than 16 logical keys. How to… Log Changes in Plan Data when using the SAP BW Planning Applications Kit May 2014 10 As stated above the handshake table does not need to have the same structure as the final logging table (though it usually makes sense). If you want the final logging table to have a structure different from the handshake table then you have to create an additional structure matching the logging table and run the report with this structure name. You will also have to adopt the coding of the logging BAdI accordingly."

I have used this report to create that table: (previously I have created a structure for it.)

*&---------------------------------------------------------------------*
*& Report Z_CREATE_DB_TABLE
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT Z_CREATE_DB_TABLE.


DATA: l_r_connection TYPE REF TO cl_sql_connection,
      l_r_statement TYPE REF TO cl_sql_statement,
      l_sql_statement TYPE string,
      l_field_list type string,
      l_r_cx_root TYPE REF TO CX_ROOT,
      l_o_structdesc TYPE REF TO cl_abap_structdescr,
      l_t_components TYPE abap_component_tab,
      l_s_components LIKE LINE OF l_t_components,
      l_target TYPE ADBC_NAME,
      l_schema TYPE ADBC_NAME,
      l_s_dd04l type dd04l,
      l_s_dbdomain type DBDOMAIN,
      l_r_elemdescr type ref to CL_ABAP_ELEMDESCR,
      l_fieldname type string.


PARAMETERS: struct(20) type c.
* l_target = struct.
* the name of the schema consist of the prefix 'SAP' and the system name.
CONCATENATE 'SAP' sy-sysid into l_schema.


concatenate '"' l_schema '"."' struct '"' into l_target.


TRY.
* get the fields of the logging table
  l_o_structdesc ?= cl_abap_typedescr=>describe_by_name( struct ).
  l_t_components = l_o_structdesc->get_components( ).
* open the data abse connection
  l_r_connection = cl_sql_connection=>get_connection( ).
* fill the sql statement
  l_r_statement = l_r_connection->create_statement( ).




* sample statement:
*drop table ZIC_02_LOG;
*create column table ZIC_02_LOG
*(
* MYUSER nvarchar(12),
* MYDATE nvarchar(8),
* MYTIME nvarchar(6),
* CALYEAR nvarchar(4),
* CALMONTH nvarchar(6),
* CURRENCY nvarchar(5),
* UNIT nvarchar(3),
* [...]
* D_NW_QUANT decimal(17,3),
* D_NW_REB decimal(17,3),
* D_NW_TRNSP decimal(17,3),
* SAVEID nvarchar(32)
*
* );


loop at l_t_components into l_s_components.
*name is the name in the structure
* type is a class, help_id in the class contains the data element
l_r_elemdescr ?= l_s_components-type.


* get the ABAP information
select single * from dd04L into l_s_dd04l
  where rollname = l_r_elemdescr->help_id.
* get the BD type
  CALL FUNCTION 'DB_MAP_DDTYPE'
  EXPORTING
    DATATYPE = l_s_dd04l-datatype
* DBSYS = SY-DBSYS
    DECIMALS = l_s_dd04l-decimals
     LENG = l_s_dd04l-leng
* WITH_CHECK = ' '
* WITH_STRING = ' '
IMPORTING
  DBDOMAIN = l_s_dbdomain.
* TYPESTRING =
* EXCEPTIONS
* NOT_MAPPABLE = 1
* OTHERS = 2 .
IF SY-SUBRC <> 0.
* Implement suitable error handling here
  exit.
ENDIF.




clear l_fieldname.
concatenate '"' l_s_components-name '"' into l_fieldname.


if l_s_dbdomain-type = 'DECIMAL'.
  concatenate
  l_field_list
  l_fieldname
  l_s_dbdomain-type '(' l_s_dbdomain-length ',' l_s_dbdomain-decimals '),'
  into l_field_list SEPARATED BY SPACE.


  elseif l_s_dbdomain-type = 'INTEGER' or l_s_dbdomain-type = 'DOUBLE'.
    concatenate
    l_field_list
    l_fieldname
    l_s_dbdomain-type ','
    into
    l_field_list SEPARATED BY SPACE.


    else.
      concatenate
      l_field_list
      l_fieldname
      l_s_dbdomain-type '(' l_s_dbdomain-length '),'
      into
      l_field_list SEPARATED BY SPACE.
      endif.
endloop.


shift l_field_list RIGHT DELETING TRAILING ','.


* make sure we have any fields at all!
      check not l_field_list is INITIAL.


* clear the old table
      concatenate
      'drop table'
      l_target
      into l_sql_statement SEPARATED BY SPACE.


      CATCH cx_root INTO l_r_cx_root.
        MESSAGE E000(00) with 'Table creation failed.'.
        exit.
      ENDTRY.


      try.
* execute the sql statement
        CALL METHOD l_r_statement->execute_query
        EXPORTING
          statement = l_sql_statement.


        CATCH cx_root INTO l_r_cx_root.
* MESSAGE E000(00) with 'Table creation failed.'.
* exit.
* If we have an error here then probably the data table does not exist yet. So just continue.
      ENDTRY.


      try.
* build up the actual statement
        clear l_sql_statement.


        concatenate
        'create column table'
        l_target
        '('
        l_field_list
        ')'


        into l_sql_statement SEPARATED BY SPACE.


* execute the sql statement
        CALL METHOD l_r_statement->execute_query
        EXPORTING
          statement = l_sql_statement.


        l_r_connection->close( ).


        CATCH cx_root INTO l_r_cx_root.
          MESSAGE E000(00) with 'Table creation failed.'.
          exit.
          ENDTRY.


          Write: 'The table ', l_target, ' has been created successfully in schema ',
          l_schema, '.' .

[raymond_giuseppi]

So you already used some ADBC classes, now look for classes dedicated to read database.

[raymond_giuseppi]

Perform some search on native sql, ABDC.

[Former Member]

Hi Raymond,

do you know how can I select 52 columns in native SQL?

When I put it directly into the select statement it took only first 20 columns.

So I put it into the string variable, but it is not working.

concatenate 'MYUSER,MYDATE,MYTIME,ACTTYPE,BUSAREA,CATEGORY,CHRTACCT,COORDER,COSTCNTR,CO_AREA,CUSTOMER,CUST_GRP,DCINDCO,DISTRCHN,DIVISION,FUNCAREA,GL_ACCT,MATERIAL,MATL_GRP,PCOORDER,PCOSTCTR,'
'PFUNAREA,PLANT,PPROFCTR,PPROJECT,PROFTCTR,PROJECT,PWBSELMT,SALESORG,SEGMENT,WBSELMT,CURRENCY,FISCPER,FISCPER3,FISCVARNT,FISCYEAR,MANDT,UNIT,LEDGER,PRODUCT,PACTTYPE,ZVENDOR,AMOUNT,QUANTY,'
'PRICE,ZAMOUNT_N,ZVAT,ZTAXADAPT,ZCOSTRATE,Z_LE,SAVEID'
into lv_col_string SEPARATED BY SPACE.

EXEC SQL PERFORMING APPEND_ITAB.

  select lv_col_string

    into :wa_log

  from ZLS_INFOCUBE_LOG

ENDEXEC.


FORM append_itab.

  APPEND wa_log to it_log.

  CLEAR  wa_log.

ENDFORM.

[Former Member]

IT is easy, it is still possible to use Select * in native SQL...