Skip to Content
author's profile photo Former Member
Former Member

Dynamic SQL Query

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Sep 27, 2005 at 12:51 PM

    hi,

    try this

    field1 = 'matnr'

    tabl1 = 'mara'

    select ( field1 ) from ( tabl1 ).

    cheers,

    sasi

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 27, 2005 at 12:55 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 27, 2005 at 02:44 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      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.

  • author's profile photo Former Member
    Former Member
    Posted on Oct 23, 2005 at 11:06 AM

    Are you still interested in a solution?

    I think I have one.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 24, 2005 at 07:27 AM

    Hi

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.