Best way to create dynamic sql

[former_member196098]

Hello all,

İ need to create dynamic queries, these queries will be based on the z table entries.

For example: User will be able to choose which fields to be selected and returned which i will record them in z tables.

where i called them conditions and returns.

so i need to build dynamic sql queries.

i thought of this way!!

  METHOD DYNAMIC_SQL_QUERY.

    DATA: TABLENAME  TYPE TABNAME,

          FNAME      TYPE FIELDNAME,

          FIELDNAMES TYPE  TABLE OF FIELDNAME WITH DEFAULT KEY,

          LT_MARA TYPE STANDARD TABLE OF MARA,

          LO_DREF TYPE REF TO DATA

          .

    FIELD-SYMBOLS : <FS_TABLE> TYPE STANDARD TABLE,

                    <FS_FIELDVAL>  TYPE ANY,

                    <LFS_FIELDNAME> LIKE LINE OF FIELDNAMES.

    DEFINE CREATE_TABLE.

      TABLENAME = &1.

      CREATE DATA &2 TYPE STANDARD TABLE OF Tablename.

    END-OF-DEFINITION.

    CREATE_TABLE 'MARA' lo_dref.

    assign lo_dref->* to <fs_table>.

    FNAME = 'MATNR'.

    APPEND INITIAL LINE TO FIELDNAMES ASSIGNING <LFS_FIELDNAME>.

    <LFS_FIELDNAME> = FNAME.

    FNAME = 'MTART'.

    APPEND INITIAL LINE TO FIELDNAMES ASSIGNING <LFS_FIELDNAME>.

    <LFS_FIELDNAME> = FNAME.

  **Dynamic query here!!

    SELECT (FIELDNAMES)

      FROM (TABLENAME)

      UP TO 20 ROWS

      INTO CORRESPONDING FIELDS OF TABLE <FS_TABLE>.

  ENDMETHOD.                    "dynamic_sql_query

Do you think this is the right approach!!!

[Former Member]

Hi Solen,

There is a very nice blog from

Susmitha Susan Thomas

Instead of entire table you can create only for the fieldset the internal table...

Well the select goes good.

Regards

[former_member209120]

This message was moderated.

[former_member196098]

Thanks guys for your answers!!

İ appreciate it

But my focus was dynamic sql , i already know how to create dynamic tables!!

Some of the links quite good and

i just needed to see other ideas and ways of creating a dynamic sql

[ansonabraham]

Hi solen dogan

You can pass the fields you required into the standard funtion module 'RH_DYNAMIC_WHERE_BUILD' which will return a string that can be used along with the 'WHERE' clause of Select Query. Please see the details here http://scn.sap.com/community/abap/blog/2013/04/16/writing-dynamic-where-clause-in-abap-select-query

[former_member196098]

Thank you Anson,

I didnt know about that FM, thats good to know

its useful to know another way of doing things for sure

Cheers mate

[Former Member]

The dynamic sql query is pretty good, despite the INTO CORRESPONDING FIELDS is unnecessary if you create the dynamic table with exactly the same fields of the query.

You can create a WHERE clause dynamic too, appending the clause into a type string table.

[]'s

Heber

[former_member196098]

Yes Habio

Dynamic sql i thought of a string table and append to the table.

method build_dynamic_selection.

   data: lt_component_table type  cl_abap_structdescr=>component_table,

         lo_struct_descr type ref to cl_abap_structdescr

         .

   field-symbols: <lfs_component_table> like line of lt_component_table

                  ,<lfs_selection_query> like line of rt_selection_list

                  .

   lo_struct_descr = zcl_aksa_general=>get_components_descr( iv_tabname ).

   lt_component_table = lo_struct_descr->get_components( ).

*here

   loop at lt_component_table assigning <lfs_component_table>.

     check cl_abap_classdescr=>get_class_name( p_object = <lfs_component_table>-type ) eq c_element_type.

     append initial line to rt_selection_list assigning <lfs_selection_query>.

     if iv_alias is not initial.

       concatenate iv_alias '~' <lfs_component_table>-name into  <lfs_selection_query>.

     else.

       concatenate iv_tabname '~' <lfs_component_table>-name into  <lfs_selection_query>.

     endif.

   endloop.

endmethod.

[former_member221367]

This message was moderated.

[former_member864989]

This message was moderated.

[former_member212264]

Hi Solen, take a look to this example program here, it offers Data Elem selection, RTTI/RTTC etc. I creatd this program during my many years as SAP Netweaver development teacher @ SAP.

Best regards, any remarks/questions welcome, Robin Fillerup.

*&---------------------------------------------------------------------*

*& Report  ZBC401_D_DYN_ITAB_BY_ELEM_SEL

*&

*&---------------------------------------------------------------------*

*& Program created By Robin Fillerup

*& Feel free to contact me: Robin.Fillerup@xs4all.nl

*& or to ask for any ABAP/WDA/FPM demo

*&---------------------------------------------------------------------*

REPORT  zbc401_d_dyn_itab_by_elem_sel.

PARAMETERS table TYPE tabname16  DEFAULT 'SCARR'.

DATA go_structdesc TYPE REF TO cl_abap_structdescr.

DATA go_error TYPE REF TO cx_root.

DATA gv_error TYPE string.

DATA gt_field TYPE ddfields.

DATA gt_select TYPE TABLE OF char72.

DATA gs_field  TYPE dfies.

DATA: lt_components TYPE abap_component_tab,    "Selected Fields

      ls_component  LIKE LINE OF lt_components,

      lo_datadescr TYPE REF TO cl_abap_datadescr,

      lo_structdescr TYPE REF TO cl_abap_structdescr,

      lo_tabledescr  TYPE REF TO cl_abap_tabledescr,

      lr_table TYPE REF TO data.

DATA: gr_alv TYPE REF TO cl_salv_table.

FIELD-SYMBOLS <gt_table> TYPE STANDARD TABLE.  "Better than Any because Alv Displays Std TAb Only

SELECTION-SCREEN BEGIN OF SCREEN 1200.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 1(20) st1 FOR FIELD dataelem .

SELECTION-SCREEN POSITION POS_LOW.

SELECT-OPTIONS dataelem FOR gs_field-rollname NO INTERVALS.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN END OF SCREEN 1200.

INITIALIZATION.

  st1 = 'Data Element'(st1).

AT SELECTION-SCREEN  ON VALUE-REQUEST FOR dataelem-low.

  CALL FUNCTION 'F4IF_INT_TABLE_VALUE_REQUEST'

    EXPORTING

      retfield        = 'ROLLNAME'

      pvalkey         = 'ROLLNAME'

      dynpprog        = sy-cprog

      dynpnr          = '1200'

      dynprofield     = 'DATAELEM-LOW'

      window_title    = 'Select Field'

      value_org       = 'S'

      multiple_choice = abap_false

    TABLES

      value_tab       = gt_field.

START-OF-SELECTION.

  TRY.

      go_structdesc ?= cl_abap_structdescr=>describe_by_name( table    ).

      gt_field = go_structdesc->get_ddic_field_list(  ).

    CATCH cx_root INTO go_error.

      gv_error = go_error->get_text( ).

      MESSAGE gv_error TYPE 'I'.

  ENDTRY.

* Field Selection, On Gt_Field,

  CALL SELECTION-SCREEN 1200 STARTING AT 10 10

                             ENDING AT 200 15

                              .

* Build op Components

  LOOP AT gt_field INTO gs_field.

*    CHECK  dataelem.    ".. Is current Field Required? (Short version)

    IF NOT gs_field-rollname IN dataelem.   "Longer but better readable..

      CONTINUE.

    ENDIF.

    lo_datadescr ?=  cl_abap_datadescr=>describe_by_name( gs_field-rollname ).

    ls_component-name = gs_field-fieldname.

    ls_component-type = lo_datadescr.

    APPEND ls_component TO lt_components.

  ENDLOOP.

* Create structdescr

  lo_structdescr = cl_abap_structdescr=>create( lt_components ).

* Create table descr

  lo_tabledescr =  cl_abap_tabledescr=>create( lo_structdescr ).  "Create has more param's that are interesting..

* Create Internal table mbv Our dynamically Created Table Type

  CREATE DATA lr_table TYPE HANDLE lo_tabledescr.

* Assign Field Symbol

  ASSIGN lr_table->* TO <gt_table>.

  LOOP AT lt_components INTO ls_component.

    APPEND ls_component-name TO gt_select.

  ENDLOOP.

  SELECT (gt_select) FROM (table) INTO CORRESPONDING FIELDS OF TABLE <gt_table>.

*     TRY.

  cl_salv_table=>factory(

    IMPORTING

      r_salv_table   = gr_alv

    CHANGING

      t_table        = <gt_table>

         ).

  gr_alv->display( ).

[former_member212264]

And Please don't pay attention to programming style, I keep examples like these as simple as possible, and in 1 source.

[alejandro_mejias]

Appart from INTO CORRESPONDING FIELD comment, the issue of a dynamic query is the problem of performance. If you access a very huge table, but no index is used in the selection, probably you will obtain the worst reponse time. Try, if you can, to set al least one field used into an index. Standard programs work in this way and with this restriction. Or move to Hana

Best regards

[former_member212264]

The INTO CORRESPONDING FIELDS OF TABLE is not a problem, will not be noticable on large dataset. The opensql statement is 'prepared' only once, after that it is opened and fetched like you can see in an Sqltrace.

Of course this is just an (one of many)example of dynamic programming/SQL, there is not a 'best' way.

Yes moving to Hana is a good idea, since it appears that that will solve all problems.

Rgrds, Robin.

[former_member196098]

Thanks Robin for your reply

I appreciate it

Dynamic creation of the table yes is powerful

Generic approach!!

There is another way too I found let me post that

[egor_malov]

Hi, Solen,

Please have a look at FMs noted here: http://scn.sap.com/thread/495994

I haven't tried using these, so I can't recommend it, but it looks close to what you are asking.

[former_member196098]

Thank you Egor

Appreciate it

my friend

i will have  alook