topic Join Table & Dynamic Selection in Application Development and Automation Discussions

Join Table & Dynamic Selection

Former Member — Mon, 09 Jul 2007 04:41:06 GMT

Hi Gurus,

I need to do a performance tuning on a bad response time program.

Therefore i use inner join to improve the response time, it works.

But my problem is the original program was using dynamic selection(with LDB), so i need to retain the same feature(i.e. dynamic selection) in new program.

I use FM 'FREE_SELECTIONS_INIT' & FM 'FREE_SELECTIONS_DIALOG' to let user entering dynamic selections.

How can the SELECT with JOIN be done with dynamic selection?

Thanks in advance.

Regards,

Hikaruno

Re: Join Table & Dynamic Selection

former_member239282 — Tue, 10 Jul 2007 08:31:55 GMT

Examples of Dynamic conditions

DATA: COND(72) TYPE C,

ITAB LIKE TABLE OF COND.

PARAMETERS: CITY1(10) TYPE C, CITY2(10) TYPE C.

DATA WA TYPE SPFLI-CITYFROM.

CONCATENATE 'CITYFROM = ''' CITY1 '''' INTO COND.

APPEND COND TO ITAB.

CONCATENATE 'OR CITYFROM = ''' CITY2 '''' INTO COND.

APPEND COND TO ITAB.

CONCATENATE 'OR CITYFROM = ''' 'BERLIN' '''' INTO COND.

APPEND COND TO ITAB.

LOOP AT ITAB INTO COND.

WRITE COND.

ENDLOOP.

SKIP.

SELECT CITYFROM

INTO WA

FROM SPFLI

WHERE (ITAB).

WRITE / WA.

ENDSELECT.

If this may be useful reward points are appreciated.

Re: Join Table & Dynamic Selection

Former Member — Tue, 10 Jul 2007 10:04:43 GMT

Hi,

the joins are doing well with dynamic condition too, but i guess the join condition you have to code.

Just remeber that table names and field names in dynamic condition have to be all caps. e.g. you have to build something like

str = 'A_{MATNR = ABC AND B}MATKL = Z1'.

and then WHERE (str).

I have extended the field names with 'A_{' and 'B}' before calling FM from SAP and it worked well. dont rember exactly FM function name it was something like RANGE_TO_WHERE.

BR, Artem

remeber points

Best Regards, Artem

Re: Join Table & Dynamic Selection

Former Member — Tue, 10 Jul 2007 14:17:30 GMT

Hai ... here is the program for using the dynamic selection FM used .

REPORT zmodtab NO STANDARD PAGE HEADING.

TYPE-POOLS: rsds.

DATA: is_x030l  TYPE x030l,
      it_dfies  TYPE TABLE OF dfies,
      is_dfies  TYPE dfies,
      it_fdiff  TYPE TABLE OF field_dif,
      is_fdiff  TYPE field_dif.

DATA: w_selid   TYPE rsdynsel-selid,
      it_tables TYPE TABLE OF rsdstabs,
      is_tables TYPE rsdstabs,
      it_fields TYPE TABLE OF rsdsfields,
      it_expr   TYPE rsds_texpr,
      it_ranges TYPE rsds_trange,
      it_where  TYPE rsds_twhere,
      is_where  TYPE rsds_where,
      w_active  TYPE i.

DATA: it_content TYPE REF TO data,
      it_modif   TYPE REF TO data,
      it_fcat    TYPE lvc_t_fcat.

DATA: w_okcode   TYPE sy-ucomm.


FIELD-SYMBOLS: <itab> TYPE STANDARD TABLE,
               <ntab> TYPE STANDARD TABLE.


* Macros
DEFINE table_error.
  message e398(00) with 'Table' p_table &1.
END-OF-DEFINITION.

DEFINE fixed_val.
  is_fdiff-fieldname = is_dfies-fieldname.
  is_fdiff-fixed_val = &1.
  is_fdiff-no_input  = 'X'.
  append is_fdiff to it_fdiff.
END-OF-DEFINITION.


* Selection screen
SELECTION-SCREEN: BEGIN OF BLOCK b01 WITH FRAME.
PARAMETERS: p_table TYPE tabname OBLIGATORY                    "table
                                 MEMORY ID dtb
                                 MATCHCODE OBJECT dd_dbtb_16.
SELECTION-SCREEN: BEGIN OF LINE,
                  PUSHBUTTON 33(20) selopt USER-COMMAND sel,
                  COMMENT    55(15) selcnt,
                  END OF LINE.
SELECTION-SCREEN: SKIP.
PARAMETERS: p_rows  TYPE i.                                    "rows
SELECTION-SCREEN: END OF BLOCK b01,
                  SKIP,
                  BEGIN OF BLOCK b02 WITH FRAME.
PARAMETERS: p_displ TYPE c AS CHECKBOX.                        "display
SELECTION-SCREEN: END OF BLOCK b02.

* Initialization
INITIALIZATION.
  MOVE '@4G@ Filter records' TO selopt.

* PBO
AT SELECTION-SCREEN OUTPUT.
  IF w_active IS INITIAL.
    CLEAR: selcnt.
  ELSE.
    WRITE w_active TO selcnt LEFT-JUSTIFIED.
  ENDIF.

* PAI
AT SELECTION-SCREEN.
  IF p_table NE is_x030l-tabname.
    CALL FUNCTION 'DDIF_NAMETAB_GET'
         EXPORTING
              tabname   = p_table
         IMPORTING
              x030l_wa  = is_x030l
         TABLES
              dfies_tab = it_dfies
         EXCEPTIONS
              OTHERS    = 1.
    IF is_x030l IS INITIAL.
      table_error 'does not exist or is not active'.
    ELSEIF is_x030l-tabtype NE 'T'.
      table_error 'is not selectable'.
    ELSEIF is_x030l-align NE 0.
      table_error 'has alignment - cannot continue'.
    ENDIF.

*   Default values for system fields
    REFRESH: it_fdiff.
    is_fdiff-tabname = p_table.
    LOOP AT it_dfies INTO is_dfies.
      IF is_dfies-datatype = 'CLNT'.
        fixed_val sy-mandt.
      ELSEIF is_dfies-rollname = 'ERDAT'
          OR is_dfies-rollname = 'ERSDA'
          OR is_dfies-rollname = 'AEDAT'
          OR is_dfies-rollname = 'LAEDA'.
        fixed_val sy-datum.
      ELSEIF is_dfies-rollname = 'ERTIM'
          OR is_dfies-rollname = 'AETIM'.
        fixed_val sy-uzeit.
      ELSEIF is_dfies-rollname = 'ERNAM'
          OR is_dfies-rollname = 'AENAM'.
        fixed_val sy-uname.
      ENDIF.
    ENDLOOP.

*   Prepare free selection on table
    REFRESH it_tables.
    is_tables-prim_tab = p_table.
    APPEND is_tables TO it_tables.

    CLEAR: w_selid.
  ENDIF.

  IF sy-ucomm = 'SEL'.
    IF w_selid IS INITIAL.
*     Init free selection dialog
      CALL FUNCTION 'FREE_SELECTIONS_INIT'
           EXPORTING
                expressions  = it_expr
           IMPORTING
                selection_id = w_selid
                expressions  = it_expr
           TABLES
                tables_tab   = it_tables
           EXCEPTIONS
                OTHERS       = 1.
    ENDIF.

*   Display free selection dialog
    CALL FUNCTION 'FREE_SELECTIONS_DIALOG'
         EXPORTING
              selection_id            = w_selid
              title                   = 'Selection'
              status                  = 1
              as_window               = 'X'
         IMPORTING
              expressions             = it_expr
              field_ranges            = it_ranges
              number_of_active_fields = w_active
         TABLES
              fields_tab              = it_fields
         EXCEPTIONS
              OTHERS                  = 1.
  ENDIF.


* Start of processing
START-OF-SELECTION.

  PERFORM f_create_table USING p_table.

  PERFORM f_select_table.

  PERFORM f_display_table.


*---------------------------------------------------------------------*
*       FORM f_create_table                                           *
*---------------------------------------------------------------------*
FORM f_create_table USING in_tabname.

  FIELD-SYMBOLS: <fcat> TYPE lvc_s_fcat.

  CALL FUNCTION 'LVC_FIELDCATALOG_MERGE'
       EXPORTING
            i_structure_name = in_tabname
       CHANGING
            ct_fieldcat      = it_fcat
       EXCEPTIONS
            OTHERS           = 1.
  IF sy-subrc = 0.
*   Complete field catalog
    LOOP AT it_fcat ASSIGNING <fcat>.
      <fcat>-tabname = in_tabname.
    ENDLOOP.
    CALL FUNCTION 'LVC_FIELDCAT_COMPLETE'
         CHANGING
              ct_fieldcat = it_fcat
         EXCEPTIONS
              OTHERS      = 1.
  ELSE.
    WRITE: 'Error building field catalog'.
    STOP.
  ENDIF.

* Create dynamic table for data
  CALL METHOD cl_alv_table_create=>create_dynamic_table
    EXPORTING
      it_fieldcatalog = it_fcat
    IMPORTING
      ep_table        = it_content.
  IF sy-subrc = 0.
    ASSIGN it_content->* TO <itab>.
  ELSE.
    WRITE: 'Error creating internal table'.
    STOP.
  ENDIF.

* Create dynamic table for modif
  CALL METHOD cl_alv_table_create=>create_dynamic_table
    EXPORTING
      it_fieldcatalog = it_fcat
    IMPORTING
      ep_table        = it_modif.
  IF sy-subrc = 0.
    ASSIGN it_modif->* TO <ntab>.
  ELSE.
    WRITE: 'Error creating internal table'.
    STOP.
  ENDIF.

ENDFORM.


*---------------------------------------------------------------------*
*       FORM f_select_table                                           *
*---------------------------------------------------------------------*
FORM f_select_table.

  IF w_active = 0.
    SELECT * FROM (p_table)
             INTO CORRESPONDING FIELDS OF TABLE <itab>
            UP TO p_rows ROWS.
  ELSE.
*   Selection with parameters
    CALL FUNCTION 'FREE_SELECTIONS_RANGE_2_WHERE'
         EXPORTING
              field_ranges  = it_ranges
         IMPORTING
              where_clauses = it_where.
    READ TABLE it_where INTO is_where WITH KEY tablename = p_table.

    SELECT * FROM (p_table)
             INTO CORRESPONDING FIELDS OF TABLE <itab>
            UP TO p_rows ROWS
            WHERE (is_where-where_tab).
  ENDIF.

  IF sy-dbcnt = 0.
    WRITE: 'No record selected'.
    STOP.
  ENDIF.
ENDFORM.


*---------------------------------------------------------------------*
*       FORM f_display_table                                          *
*---------------------------------------------------------------------*
FORM f_display_table.
  DATA: l_answer TYPE c,
        l_eflag  TYPE c.

  CLEAR: w_okcode.
  REFRESH: <ntab>.
* Display table contents
  CALL FUNCTION 'STC1_FULLSCREEN_TABLE_CONTROL'
       EXPORTING
            header       = p_table
            tabname      = p_table
            display_only = p_displ
            endless      = 'X'
            no_button    = space
       IMPORTING
            okcode       = w_okcode
       TABLES
            nametab      = it_dfies
            table        = <itab>
            fielddif     = it_fdiff
            modif_table  = <ntab>
       EXCEPTIONS
            OTHERS       = 1.
  IF sy-subrc = 0.
    IF p_displ IS INITIAL AND w_okcode = 'SAVE'.
*     Confirm update
      CALL FUNCTION 'POPUP_TO_CONFIRM'
           EXPORTING
                titlebar              = p_table
                text_question         = 'Do you want to update table ?'
                default_button        = '2'
                display_cancel_button = ' '
           IMPORTING
                answer                = l_answer
           EXCEPTIONS
                OTHERS                = 1.
      IF l_answer = '1'.
*       Apply modifications
        IF NOT <ntab>[] IS INITIAL.
          PERFORM f_add_system USING space.
          MODIFY (p_table) FROM TABLE <ntab>.
          IF sy-subrc NE 0.
            l_eflag = 'X'.
          ENDIF.
        ENDIF.
*       Apply deletions
        IF l_eflag IS INITIAL.
          REFRESH: <ntab>.
          CALL FUNCTION 'STC1_GET_DATA'
               TABLES
                    deleted_data = <ntab>
               EXCEPTIONS
                    OTHERS       = 1.
          IF NOT <ntab>[] IS INITIAL.
            DELETE (p_table) FROM TABLE <ntab>.
            IF sy-subrc NE 0.
              ROLLBACK WORK.
              l_eflag = 'X'.
            ENDIF.
          ENDIF.
        ENDIF.
*       Apply creations
        IF l_eflag IS INITIAL.
          REFRESH: <ntab>.
          CALL FUNCTION 'STC1_GET_DATA'
               TABLES
                    new_data = <ntab>
               EXCEPTIONS
                    OTHERS   = 1.
          IF NOT <ntab>[] IS INITIAL.
            PERFORM f_add_system USING 'X'.
            INSERT (p_table) FROM TABLE <ntab>.
            IF sy-subrc NE 0.
              ROLLBACK WORK.
              l_eflag = 'X'.
            ENDIF.
          ENDIF.
        ENDIF.
        IF l_eflag IS INITIAL.
          COMMIT WORK.
          MESSAGE s261(53).
        ELSE.
          MESSAGE s075(3i).
          PERFORM f_select_table.
        ENDIF.
      ENDIF.
*     Display table again
      PERFORM f_display_table.
    ENDIF.
  ENDIF.

ENDFORM.


*---------------------------------------------------------------------*
*       FORM f_add_system                                             *
*---------------------------------------------------------------------*
FORM f_add_system USING new TYPE c.

  FIELD-SYMBOLS: <irec> TYPE ANY,
                 <upd>  TYPE ANY.

  LOOP AT it_fdiff INTO is_fdiff.
    READ TABLE it_dfies INTO is_dfies
                    WITH KEY fieldname = is_fdiff-fieldname.
    LOOP AT <ntab> ASSIGNING <irec>.
      ASSIGN COMPONENT is_fdiff-fieldname OF STRUCTURE <irec> TO <upd>.
      IF is_dfies-datatype = 'CLNT'.
        <upd> = sy-mandt.
      ELSE.
        CASE is_dfies-rollname.
          WHEN 'AENAM'.
            <upd> = sy-uname.
          WHEN 'AEDAT' OR 'LAEDA'.
            <upd> = sy-datum.
          WHEN 'AETIM'.
            <upd> = sy-uzeit.
          WHEN OTHERS.
        ENDCASE.
      ENDIF.
    ENDLOOP.
  ENDLOOP.

ENDFORM.

reward points if it is usefull......

Girish

Re: Join Table & Dynamic Selection

Former Member — Tue, 10 Jul 2007 14:20:52 GMT

Here is the simple logic of the using the FM 'FREE_SELECTIONS_INIT' & FM 'FREE_SELECTIONS_DIALOG'

parameters p_db              type rsdgscsel-iobjnm. 
parameters: p_texpr          type rsds_texpr no-display. 

at selection-screen on p_db. 
  gt_texpr = p_texpr. 


  perform f1200_get_db_selections. 


form f1200_get_db_selections . 
 gt_tabs-prim_tab = g_db_name. 
  append gt_tabs. 
  gt_fields-tablename = g_db_name. 
  loop at gt_keys into  ga_keys. 
    gt_fields-fieldname = ga_keys-fieldname. 
    append gt_fields. 
  endloop. 

  gt_trange_int = gt_trange. 
  call function 'FREE_SELECTIONS_INIT' 
    exporting 
    expressions              = gt_texpr 
      kind                     = 'F' 
*    field_groups_key         = qcat 
*     field_ranges_int          = gt_trange_int 
    importing 
      selection_id             = g_selid 
      expressions              = gt_texpr 
      where_clauses            = gt_twhere 
      field_ranges             = gt_trange 
      number_of_active_fields  = g_actnum 
    tables 
      tables_tab               = gt_tabs 
      fields_tab               = gt_fields 
*    EXCEPTIONS 
*      fields_incomplete        = 01 
*      fields_no_join           = 02 
*      field_not_found          = 03 
*      no_tables                = 04 
*      table_not_found          = 05 
*      expression_not_supported = 06 
*      incorrect_expression     = 07 
*      illegal_kind             = 08 
*      area_not_found           = 09 
*      inconsistent_area        = 10 
*      kind_f_no_fields_left    = 11 
*      kind_f_no_fields         = 12 
*      too_many_fields          = 13 
. 


  clear g_title. 
  concatenate 'Compare Master Data for' g_db_name 
  into g_title separated by space. 

  if sy-subrc = 0. 
    call function 'FREE_SELECTIONS_DIALOG' 
      exporting 
        selection_id            = g_selid 
        title                   = g_title 
        frame_text              = g_frame_text 
        as_window               = 'X' 
        start_row               = '5' 
        start_col               = '5' 
        tree_visible            = ' ' 
        status                  = 1 
        importing 
        where_clauses           = gt_twhere 
        expressions             = gt_texpr 
        field_ranges            = gt_trange 
        number_of_active_fields = g_actnum 
      tables 
        fields_tab              = gt_fields 
      exceptions 
*        internal_error          = 01 
        no_action               = 02 
        no_fields_selected      = 03 
        no_tables_selected      = 04 
*        selid_not_found         = 05.

reward points if it is usefull.....

Girish

Re: Join Table & Dynamic Selection

Former Member — Mon, 16 Jul 2007 10:01:44 GMT

Hi all,

Thanks for your reply. My problem solved.

As per yr advice, I have extended the field names with 'A_{' and 'B}' before calling FM 'FREE_SELECTIONS_RANGE_2_WHERE' from SAP and it worked well.

Thanks to Artem & Girish. Points Awarded.

BR,

Hikaruno