topic Re: Select from database dynamically in Application Development and Automation Discussions

Select from database dynamically

Former Member — Tue, 27 Jul 2010 08:32:43 GMT

Hello colleague,

i am trying to read a database whose name is dynamically identified. There are several fields but i am interested in one. I want all the entries for that field in the table. The field "trace_fct_id" is type SYSUUID_x(16). I want it to be populated in the CHAR32 field ie GUID.

LOOP AT lt_tabname INTO ls_tablename.

CREATE DATA dref TYPE STANDARD TABLE OF (ls_tablename-name).

ASSIGN dref->* TO <itab>.

SELECT DISTINCT trace_fct_id FROM (ls_tablename-name) INTO CORRESPONDING FIELDS OF TABLE <itab>.

MOVE <itab> TO et_func_ids.

ENDLOOP.

I get all the entries from the table but i just want to return the ID, and the move does not copy it in the internal table as the column name is different.

Could you please help?

Thanks in advance.

Best Regards,

Piyush

Re: Select from database dynamically

anup_deshmukh4 — Tue, 27 Jul 2010 09:08:01 GMT

Hello,

you can have

TYPES : BEGIN OF STRUCT,

REF_DATA TYPE REF TO DATA,

END OF STRUCT.

DATA : ITAB TYPE TABLE OF STRUCT, " this will give you a internal table of referances you will be storing...

WA LIKE LINE OF ITAB.

then you can manipulate however you want.

Re: Select from database dynamically

andreas_mann3 — Tue, 27 Jul 2010 09:22:53 GMT

1) before your loop select all possible tables from table DD03l:

select * from dd03l appending corresponding fileds of table it_tabname

where fieldname = 'TRACE_FCT_ID'...

2)use this table

data: begin of itab occurs 0,

tabn type dd03l-tabname,

TRACE_FCT_ID type FDT_TRACE_0000-TRACE_FCT_ID,

end of itab.

3) try sth like this:

 
LOOP AT lt_tabname INTO ls_tablename.
 clear itab.
 itab-tabn = ls_tablename-name.

 SELECT DISTINCT trace_fct_id FROM (ls_tablename-name) INTO itab-TRACE_FCT_ID
             group by TRACE_FCT_ID.

 collect itab.
 endselect.

ENDLOOP.

hope that helps

Andrras

Edited by: Andreas Mann on Jul 27, 2010 11:24 AM

Re: Select from database dynamically

Subhankar — Tue, 27 Jul 2010 09:27:20 GMT

Hi,

If you sure that all the database table have the field trace_fct_id then you can directly select that field only.

Then you can the below code.

data: itab1 type standard table of trace_fct_id,

itab2 type standard table of trace_fct_id.

LOOP AT lt_tabname INTO ls_tablename.

SELECT DISTINCT trace_fct_id FROM (ls_tablename-name) INTO CORRESPONDING FIELDS OF TABLE itab1.

append lines of Itab1 to Itab2.

ENDLOOP.

Then ITAB2 will contain all the required IDs.

Thanks

Subhankar

Re: Select from database dynamically

SuhaSaha — Tue, 27 Jul 2010 10:12:52 GMT

Check if this helps:

DATA: dref TYPE REF TO data,
      lt_tabname TYPE STANDARD TABLE OF tabname,
      ls_tablename TYPE tabname,
      et_func_ids TYPE STANDARD TABLE OF sysuuid_x,
      es_func_ids TYPE sysuuid_x.

FIELD-SYMBOLS: <itab> TYPE STANDARD TABLE,
               <wa> TYPE ANY ,
               <val> TYPE ANY.

DATA: v_err TYPE string,
      lcx_root TYPE REF TO cx_root.

LOOP AT lt_tabname INTO ls_tablename.
  CREATE DATA dref TYPE STANDARD TABLE OF (ls_tablename).
  ASSIGN dref->* TO <itab>.
  TRY .
      SELECT DISTINCT trace_fct_id FROM (ls_tablename)
      INTO CORRESPONDING FIELDS OF TABLE <itab>.
      CHECK sy-subrc = 0.
      LOOP AT <itab> ASSIGNING <wa>.
        ASSIGN COMPONENT 'TRACE_FCT_ID' OF STRUCTURE <wa> TO <val>.
        CHECK sy-subrc = 0.
        es_func_ids = <val>.
        APPEND es_func_ids TO et_func_ids.
        CLEAR et_func_ids.
      ENDLOOP.
    CATCH cx_sy_open_sql_error INTO lcx_root."Catch block for SQL errors
      v_err = lcx_root->get_text( ).
      WRITE: / v_err.
  ENDTRY.

  REFRESH <itab>.

ENDLOOP.

BR,

Suhas