topic Re: Dynamic SQL Query in Application Development and Automation Discussions

Dynamic SQL Query

Former Member — Tue, 27 Sep 2005 12:16:56 GMT

Hi all,

I have created one transaction which is displaying two table contols in the screen.there are two i/o field also in the same screen, where users can input the name of tables (say MARA & MARC) now on hitting enter, both table control will be populated by respective fieldnames of the tables.

Now if the user select some fields from both table control, and if any common field is there in the selection ( MARA-matnr, MARC-matnr), and click a button (say next) can we create & execute one dynamic sql query , having join on this fields. Please note the table name, and selection of fields for join is dynamic.

Re: Dynamic SQL Query

Former Member — Tue, 27 Sep 2005 12:51:38 GMT

hi,

try this

field1 = 'matnr'

tabl1 = 'mara'

select ( field1 ) from ( tabl1 ).

cheers,

sasi

Re: Dynamic SQL Query

Former Member — Tue, 27 Sep 2005 12:55:25 GMT

hi,

try this

data : c_table(10) type c value 'MARA'.

data : c_field(10) type c value 'MATNR'.

data : c_CONDITION(25) type c value 'MATNR = ''AH0904001EN'''.

data : itab_mara like mara occurs 0 with header line.

select (C_FIELD) from (c_TABLE) into table itab_mara WHERE (C_CONDITION).

write itab_mara-matnr.

Re: Dynamic SQL Query

Former Member — Tue, 27 Sep 2005 14:44:24 GMT

I don't think you can do a dynamic join easily. If it were a report rather than a dialogue program, you could probably generate a subroutine pool, but I don't think that's an option here.

Rob

Re: Dynamic SQL Query

Former Member — Wed, 28 Sep 2005 04:37:57 GMT

Hi Rob,

Even I feel the same. Actually the purpose was creating a dynamic ALV, in which we r not aware of tables and join condition.

However i have solved the problem partially.

What i did-> I have created one screen, havng a Table controls and one I/O field. The user will enter the Table name (Say MARA) there and click one push button.On doing that, i get the fieldnames for that table dynamically from table DD03L


FORM GET_TABLE_METADATA.
 SELECT FIELDNAME INTO CORRESPONDING FIELDS OF TABLE BASETABLE FROM DD03L WHERE TABNAME = TABLE_NAME.

LOOP AT BASETABLE.
IF BASETABLE-fieldname CP '*INCLU*'.
  DELETE BASETABLE WHERE fieldname = BASETABLE-fieldname.
ENDIF.
ENDLOOP.
 ENDFORM.                 " GET_TABLE_METADATA

where basetable is a internal table with single fieldname.

Now ones the fieldnames for that table are retrieved, they will be diplayed in the table control.

The user will select some or all the fields from the control and click another pushbutton "Display ALV".

on doing that, a dynamic structure is created in the following way-->

FORM create_fieldcat.
   CLEAR: is_fieldcat,it_fieldcat,is_fcat,it_fcat.

   CALL FUNCTION 'REUSE_ALV_FIELDCATALOG_MERGE'
        EXPORTING
             i_structure_name = table_name
        CHANGING
             ct_fieldcat      = it_fcat[].

   LOOP AT it_fcat INTO is_fcat.
     MOVE-CORRESPONDING is_fcat TO is_fieldcat.
     is_fieldcat-fieldname = is_fcat-fieldname.
     is_fieldcat-ref_field = is_fcat-fieldname.
     is_fieldcat-ref_table = is_fcat-ref_tabname.
     APPEND is_fieldcat TO it_fieldcat.
   ENDLOOP.

*-------------MODIFYING FIELDCAT----------------
   LOOP AT it_fieldcat INTO is_fieldcat.
   READ  TABLE basetable WITH KEY fieldname = is_fieldcat-fieldname.
     IF basetable-select_flag <> 'X'.
       DELETE it_fieldcat WHERE fieldname = is_fieldcat-fieldname.
     ELSE.                    "SETTING HEDAER
       IS_FIELDCAT-COLTEXT = BASETABLE-HEADER.
       MODIFY IT_FIELDCAT FROM IS_FIELDCAT.
     ENDIF.
   ENDLOOP.

* Create a new Table
   CALL METHOD cl_alv_table_create=>create_dynamic_table
          EXPORTING
           it_fieldcatalog = it_fieldcat
          IMPORTING
           ep_table        = new_table.

* Create a new Line with the same structure of the table.
   ASSIGN new_table->* TO <l_table>.

 ENDFORM.                    " CREATE_FIELDCAT

If you are thinking what r the variables declaration here it is -->

TYPE-POOLS: slis.

DATA: BEGIN OF basetable OCCURS 0,

select_flag(1),

fieldname LIKE dd03l-fieldname,

header(30),

END OF basetable.

DATA: table_name LIKE dd02l-tabname.

DATA: new_table TYPE REF TO data,

new_line TYPE REF TO data.

DATA: it_fcat TYPE slis_t_fieldcat_alv,

is_fcat LIKE LINE OF it_fcat.

DATA: it_fieldcat TYPE lvc_t_fcat,

is_fieldcat LIKE LINE OF it_fieldcat.

FIELD-SYMBOLS: <l_table> TYPE ANY TABLE,

<l_line> TYPE ANY.[/code]

Now the z-transaction works fine, you input any table, it will retrieve the fieldsname, and let you select the fields for ALV report.Then will create and display the ALV.

I wanted to enhance the feature by giving another table control to user, where fields of a second table will be displayed and if user select a common field from both the table control then a runtime join will be performed and corresponding alv will be displayed..any suggestion is welcome.

Re: Dynamic SQL Query

Former Member — Sun, 23 Oct 2005 11:06:10 GMT

Are you still interested in a solution?

I think I have one.

Re: Dynamic SQL Query

Former Member — Mon, 24 Oct 2005 07:27:35 GMT

For more information on 'Using dynamic SELECT statement', check the following link.

https://www.sdn.sap.com/sdn/developerareas/abap.sdn?contenttype=url&content=/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/documents/a1-8-4/abap faqs.faq

Ashish