topic Re: Best way to create dynamic sql in Application Development and Automation Discussions

Best way to create dynamic sql

Former Member — Thu, 05 Sep 2013 07:54:52 GMT

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!!!

Re: Best way to create dynamic sql

Former Member — Thu, 05 Sep 2013 08:18:34 GMT

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

Re: Best way to create dynamic sql

former_member209120 — Thu, 05 Sep 2013 08:31:31 GMT

This message was moderated.

Re: Best way to create dynamic sql

Former Member — Thu, 05 Sep 2013 14:27:41 GMT

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

Re: Best way to create dynamic sql

Former Member — Thu, 05 Sep 2013 16:44:01 GMT

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

Re: Best way to create dynamic sql

Former Member — Fri, 06 Sep 2013 05:02:42 GMT

This message was moderated.

Re: Best way to create dynamic sql

former_member864989 — Fri, 06 Sep 2013 08:31:39 GMT

This message was moderated.

Re: Best way to create dynamic sql

Former Member — Fri, 06 Sep 2013 13:14:15 GMT

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.

Re: Best way to create dynamic sql

ansonabraham — Fri, 06 Sep 2013 13:20:31 GMT

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

Re: Best way to create dynamic sql

Former Member — Mon, 09 Sep 2013 07:44:32 GMT

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

Re: Best way to create dynamic sql

former_member212264 — Fri, 04 Oct 2013 07:54:38 GMT

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( ).

Re: Best way to create dynamic sql

former_member212264 — Fri, 04 Oct 2013 07:56:31 GMT

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

Re: Best way to create dynamic sql

alejandro_mejias — Fri, 04 Oct 2013 08:27:06 GMT

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

Re: Best way to create dynamic sql

former_member212264 — Fri, 04 Oct 2013 10:19:50 GMT

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.

Re: Best way to create dynamic sql

Former Member — Wed, 23 Oct 2013 11:23:25 GMT

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

Re: Best way to create dynamic sql

egor_malov — Thu, 05 Dec 2013 17:24:18 GMT

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.

Re: Best way to create dynamic sql

Former Member — Thu, 12 Dec 2013 08:24:38 GMT

Thank you Egor

Appreciate it

my friend

i will have alook