Dynamic Queries in ABAP?

[Former Member]

Hi,

I'm currently trying to develop an RFC FM that can create a dynamic query. I want to use the FM to query different tables without having to write n number of different cases.

For example a particular application might need to query table xyz with three fields defined in the where clause. Another app might need to query the same table with only two fields defined in the where clause. In the same way I would like to be able to use the very same FM to query another table abc with x number of parameters.

I'd like to send the where clause in a table.

Is that possible?

[Former Member]

Try Function Module RFC_GET_TABLE_ENTRIES

[Former Member]

Yes it is possible, but you need dynamic internal table to hold the data. See the code below for an idea, but this will dump because the target table (target_tab ) is not compatable with the fields selected. You need to dynamically create or pass the target internal table that can hold the fields you are selecting.

DATA: ztab LIKE dd02l-tabname VALUE 'MARA'.

DATA: BEGIN OF target_tab OCCURS 0,

fieldvals(1000).

DATA: END OF target_tab.

DATA: BEGIN OF wheretab OCCURS 0,

whereclause(30).

DATA: END OF wheretab.

DATA: fieldnames(100).

CONCATENATE 'MATNR'

'='

'''XYZ'''

'AND'

INTO wheretab-whereclause SEPARATED BY space.

*wheretab-whereclause = 'MATNR = ''''XYZ'''' AND'.

APPEND wheretab.

CLEAR wheretab.

CONCATENATE 'MTART'

'='

'''FG'''

INTO wheretab-whereclause SEPARATED BY space.

APPEND wheretab.

fieldnames = 'MATNR'.

CONCATENATE fieldnames 'MTART'

INTO fieldnames SEPARATED BY space.

SELECT (fieldnames) FROM (ztab) INTO TABLE target_tab WHERE (wheretab).

[mnicolai_77]

hi Roberto,

try with this function.

{

FUNCTION ztest.

*"----

---

""Local Interface:

*" IMPORTING

*" REFERENCE(EXTRACT_TABLE) TYPE DD02L-TABNAME

*" REFERENCE(WHERE_TAB) TYPE WHERECONDS

*" REFERENCE(FIELD_LIST) TYPE WHERECONDS

*" EXPORTING

*" REFERENCE(RETURN_TABLE) TYPE TABLE

*"----

---

TYPE-POOLS: abap.

FIELD-SYMBOLS: <dyn_table> TYPE STANDARD TABLE,

<dyn_wa> TYPE ANY.

DATA: dy_table TYPE REF TO data,

dy_line TYPE REF TO data,

xfc TYPE lvc_s_fcat,

ifc TYPE lvc_t_fcat,

field_line TYPE wherecond.

data: fields(1000).

DATA : idetails TYPE abap_compdescr_tab,

xdetails TYPE abap_compdescr.

DATA : ref_table_des TYPE REF TO cl_abap_structdescr.

• Get the structure of the table.

ref_table_des ?= cl_abap_typedescr=>describe_by_name( extract_table ).

idetails[] = ref_table_des->components[].

LOOP AT idetails INTO xdetails.

READ TABLE field_list WITH KEY text = xdetails-name TRANSPORTING NO FIELDS.

check sy-subrc = 0.

CLEAR xfc.

xfc-fieldname = xdetails-name .

xfc-datatype = xdetails-type_kind.

xfc-inttype = xdetails-type_kind.

xfc-intlen = xdetails-length.

xfc-decimals = xdetails-decimals.

APPEND xfc TO ifc.

ENDLOOP.

• Create dynamic internal table and assign to FS

CALL METHOD cl_alv_table_create=>create_dynamic_table

EXPORTING

it_fieldcatalog = ifc

IMPORTING

ep_table = dy_table.

ASSIGN dy_table->* TO <dyn_table>.

LOOP AT field_list INTO field_line.

CONCATENATE fields field_line-text INTO fields SEPARATED BY space.

ENDLOOP.

• Select Data from table.

SELECT (fields) FROM (extract_table) INTO TABLE <dyn_table> WHERE (WHERE_TAB).

return_table[] = <dyn_table>.

ENDFUNCTION.

}

where

EXTRACT_TABLE --> name of the table

WHERE_TAB --> internal table contains the where conditions

FIELD_LIST --> list of fields that u want

RETURN_TABLE --> dynamic table returns the selected values

[mnicolai_77]

oops ,

this is a sample program for running the function.

REPORT zciccio.

DATA: lt TYPE dd02l OCCURS 0,

where_tab type WHERECONDS,

field_list1 type WHERECONDS.

append 'TABNAME = ''DD02L'' ' to where_tab.

append 'TABNAME' TO FIELD_LIST1.

append 'AS4LOCAL' TO FIELD_LIST1.

append 'AS4VERS' TO FIELD_LIST1.

append 'TABCLASS' TO FIELD_LIST1.

append 'SQLTAB' TO FIELD_LIST1.

append 'DATMIN' TO FIELD_LIST1.

append 'DATMAX' TO FIELD_LIST1.

append 'DATAVG' TO FIELD_LIST1.

CALL FUNCTION 'ZTEST'

EXPORTING

extract_table = 'DD02L'

where_tab = where_tab[]

field_list = field_list1[]

IMPORTING

return_table = lt[].

sorry....

[Former Member]

Hi Roberto,

have you tried [Remote Open SQL|http://remoteopensql.codeplex.com/] ?

Filippo.

Edited by: Filippo Bottega on Mar 1, 2012 3:01 PM