ptreq_attabsdata - ptreq_header - ptreq_items join is not getting data

[Former Member]

Hi masters,

I'm trying to join three tables and call the report with personal number or begin/end date.

However gt_outtab is filling with nothing.

Can someone help me to make the join work ?

Thanks.

    SELECT a~request_id a~status a~mod_user

      c~pernr c~subty c~begda c~endda

    INTO CORRESPONDING FIELDS OF TABLE gt_outtab

    FROM ptreq_header AS a INNER JOIN

    ptreq_items AS b ON a~item_list_id = b~item_list_id

     INNER JOIN ptreq_attabsdata AS c ON b~item_ins = c~item_id

    WHERE c~pernr IN pernr AND

        c~begda IN begda AND

        c~endda IN endda.

[roberto_vacca2]

Hi.

You must pay attention to the data you're "joining". Well, there's for example a change of KEY JOIN FIELDS in the last INNER JOIN. The ITEM_ID is not present in PTREQ_HEADER. This determines a loss of criteria in the select operation.

You should try to separate your query or create a better join. First determines data from the first join and with these data, inside a temporary internal table, use the option FOR ALL ENTRIES IN gt_temp_tab.

This should resolve your needs.

Hope to help, bye.

[Former Member]

Hi Roberto.

I'm new to abap so i dont know the usage of statement ''KEY JOIN FIELDS''. I dont have any examples in the hand too. Can you give an example using my code above please ?

[former_member226225]

Hi Eldanar,

please find the below code and what is the problem with select query is inner joins on items table ptreq_items and ptreq_abbsdata with on condition is having the different lengths 16 and 32.

so you are not getting the data.

modify the select queries with required fields and test it .

DATA : lt_abs TYPE STANDARD TABLE OF ptreq_attabsdata,
        ls_abs LIKE LINE OF lt_abs,
        lt_items TYPE STANDARD TABLE OF ptreq_items,
        ls_items LIKE LINE OF lt_items,
        lt_item_id TYPE RANGE OF os_guid,
        ls_item_id LIKE LINE OF lt_item_id.

 DATA : lt_header TYPE TABLE OF  ptreq_header,
        ls_header TYPE ptreq_header.

 TYPES : BEGIN OF ty_data,
          request_id  TYPE ptreq_header-request_id,
          status      TYPE ptreq_header-status,
          mod_user    TYPE ptreq_header-mod_user,
          pernr       TYPE ptreq_attabsdata-pernr,
          subty       TYPE ptreq_attabsdata-subty,
          begda       TYPE ptreq_attabsdata-begda,
          endda       TYPE ptreq_attabsdata-endda,
         END OF ty_data.

 DATA : gt_final TYPE TABLE OF ty_data,
        gs_final TYPE ty_data.

 TABLES : pa0001.

 SELECT-OPTIONS : s_pernr FOR pa0001-pernr,
                  s_begda FOR pa0001-begda,
                  s_endda FOR pa0001-endda.


 START-OF-SELECTION.


   SELECT * FROM ptreq_attabsdata INTO TABLE lt_abs
       WHERE pernr IN s_pernr AND
               begda IN s_begda AND endda IN s_endda.

   IF lt_abs[] IS NOT INITIAL.

     LOOP AT lt_abs INTO ls_abs.
       MOVE : ls_abs-item_id TO ls_item_id-low.
       ls_item_id-sign = 'I'.
       ls_item_id-option = 'EQ'.
       APPEND ls_item_id TO lt_item_id.
       CLEAR :ls_abs.
     ENDLOOP.

     SELECT * FROM ptreq_items INTO TABLE lt_items
                   WHERE item_ins IN lt_item_id.

     IF lt_items[] IS NOT INITIAL.

       SELECT * FROM ptreq_header INTO TABLE lt_header
                     FOR ALL ENTRIES IN lt_items
                         WHERE item_list_id EQ lt_items-item_list_id.
     ENDIF.


     LOOP AT lt_abs INTO ls_abs.
       MOVE : ls_abs-pernr TO gs_final-pernr,
              ls_abs-begda TO gs_final-begda,
              ls_abs-endda TO gs_final-endda,
              ls_abs-subty to gs_final-subty.

       READ TABLE lt_items INTO ls_items WITH KEY item_ins = ls_abs-item_id.
       IF sy-subrc IS INITIAL.
         READ TABLE lt_header INTO ls_header WITH KEY  item_list_id = ls_items-item_list_id.
         IF sy-subrc IS INITIAL.
           MOVE : ls_header-request_id TO gs_final-request_id,
                  ls_header-status     TO gs_final-status,
                  ls_header-mod_user   TO gs_final-mod_user.
         ENDIF.
       ENDIF.
       APPEND gs_final TO gt_final.
       CLEAR : gs_final, ls_header,ls_items,
               ls_abs.
     ENDLOOP.
   ENDIF.

   if gt_final[] is NOT INITIAL.

     BREAK abaper.

ENDIF.

The output is :

I hope this will be helpful.

Thanks & Regards,

Raghunadh Kodali.

[Former Member]

Hi Raghunadh,

it seems correct in debugger but now another problem occurs. There is something wrong while calling alv.

my code is below:

REPORT  zd_ptreq_hdrtoattab.

INCLUDE zinc_alv_templ.

TABLES : ptreq_attabsdata,ptreq_header,ptreq_items,icon.

DATA:

   gd_repid   TYPE syrepid,

   gd_okcode  TYPE ui_func,

   gt_fcat    TYPE lvc_t_fcat,

   gs_layout  TYPE lvc_s_layo,

   gs_variant TYPE disvariant,

   go_docking TYPE REF TO cl_gui_docking_container,

   go_grid    TYPE REF TO cl_gui_alv_grid.

DATA extab TYPE slis_t_extab.

SELECTION-SCREEN BEGIN OF BLOCK 02 WITH FRAME.

PARAMETERS : p_vari LIKE disvariant-variant

              DEFAULT '/DEFAULT'.

SELECTION-SCREEN END   OF BLOCK 02.

   DATA : BEGIN OF gt_outtab OCCURS 0,

             request_id  TYPE ptreq_header-request_id,

             status      TYPE ptreq_header-status,

             mod_user    TYPE ptreq_header-mod_user,

             pernr       TYPE ptreq_attabsdata-pernr,

             subty       TYPE ptreq_attabsdata-subty,

             begda       TYPE ptreq_attabsdata-begda,

             endda       TYPE ptreq_attabsdata-endda.

   DATA : END OF gt_outtab .

   DATA : lt_abs TYPE STANDARD TABLE OF ptreq_attabsdata,

           ls_abs LIKE LINE OF lt_abs,

           lt_items TYPE STANDARD TABLE OF ptreq_items,

           ls_items LIKE LINE OF lt_items,

           lt_item_id TYPE RANGE OF os_guid,

           ls_item_id LIKE LINE OF lt_item_id.

   DATA : lt_header TYPE TABLE OF  ptreq_header,

           ls_header TYPE ptreq_header.

   TYPES : BEGIN OF ty_data,

             request_id  TYPE ptreq_header-request_id,

             status      TYPE ptreq_header-status,

             mod_user    TYPE ptreq_header-mod_user,

             pernr       TYPE ptreq_attabsdata-pernr,

             subty       TYPE ptreq_attabsdata-subty,

             begda       TYPE ptreq_attabsdata-begda,

             endda       TYPE ptreq_attabsdata-endda,

            END OF ty_data.

   DATA : gt_final TYPE TABLE OF ty_data,

           gs_final TYPE ty_data.

   TABLES : pa0001.

   SELECT-OPTIONS : s_pernr FOR pa0001-pernr,

                     s_begda FOR pa0001-begda,

                     s_endda FOR pa0001-endda.

INITIALIZATION.

   PERFORM zinc_alv_initialization.

START-OF-SELECTION.

   IF p_vari IS INITIAL.

     PERFORM zinc_alv_vari_def USING p_vari.

   ENDIF.

   SELECT * FROM ptreq_attabsdata INTO TABLE lt_abs

       WHERE pernr IN s_pernr AND

   begda IN s_begda AND endda IN s_endda.

   IF lt_abs[] IS NOT INITIAL.

     LOOP AT lt_abs INTO ls_abs.

       MOVE : ls_abs-item_id TO ls_item_id-low.

       ls_item_id-sign = 'I'.

       ls_item_id-option = 'EQ'.

       APPEND ls_item_id TO lt_item_id.

       CLEAR :ls_abs.

     ENDLOOP.

     SELECT * FROM ptreq_items INTO TABLE lt_items

     WHERE item_ins IN lt_item_id.

     IF lt_items[] IS NOT INITIAL.

       SELECT * FROM ptreq_header INTO TABLE lt_header

                     FOR ALL ENTRIES IN lt_items

       WHERE item_list_id EQ lt_items-item_list_id.

     ENDIF.

     LOOP AT lt_abs INTO ls_abs.

       MOVE : ls_abs-pernr TO gs_final-pernr,

              ls_abs-begda TO gs_final-begda,

              ls_abs-endda TO gs_final-endda,

              ls_abs-subty TO gs_final-subty.

    READ TABLE lt_items INTO ls_items WITH KEY item_ins = ls_abs-item_id.

       IF sy-subrc IS INITIAL.

         READ TABLE lt_header INTO ls_header

         WITH KEY item_list_id = ls_items-item_list_id.

         IF sy-subrc IS INITIAL.

           MOVE : ls_header-request_id TO gs_final-request_id,

                  ls_header-status     TO gs_final-status,

                  ls_header-mod_user   TO gs_final-mod_user.

         ENDIF.

       ENDIF.

       APPEND gs_final TO gt_final.

       CLEAR : gt_outtab, gs_final, ls_header,ls_items,

               ls_abs.

     ENDLOOP.

   ENDIF.

   IF gt_final[] IS NOT INITIAL.

     break abaper.

   ENDIF.

END-OF-SELECTION.

   PERFORM display_alv.

*&---------------------------------------------------------------------*

*&      Form  BUILD_FIELDCATALOG

*&---------------------------------------------------------------------*

*       text

*----------------------------------------------------------------------*

*  -->  p1        text

*  <--  p2        text

*----------------------------------------------------------------------*

FORM display_alv.

   DATA : lt_fieldcat    TYPE slis_t_fieldcat_alv,

          w_fieldcat_alv TYPE slis_fieldcat_alv,

          lw_layout      TYPE slis_layout_alv,

          lw_events      TYPE slis_t_event,

          lw_eventexit   TYPE slis_t_event_exit.

   PERFORM zinc_alv_catalog USING 'GT_OUTTAB'

                           CHANGING lt_fieldcat.

   DATA : l_modified TYPE xflag.

   LOOP AT lt_fieldcat INTO w_fieldcat_alv.

     CLEAR l_modified.

     CASE w_fieldcat_alv-fieldname.

       WHEN 'REQUEST_ID'.

         l_modified = 'X'.

         w_fieldcat_alv-reptext_ddic = 'Talep Numarası'.

       WHEN 'PERNR'.

         l_modified = 'X'.

         w_fieldcat_alv-reptext_ddic = 'Personel Numarası'.

      WHEN 'BEGDA'.

         l_modified = 'X'.

         w_fieldcat_alv-reptext_ddic = 'Geçerlilik Başlangıcı'.

       WHEN 'ENDDA'.

         l_modified = 'X'.

         w_fieldcat_alv-reptext_ddic = 'Geçerlilik Sonu'.

       WHEN 'SUBTY'.

         l_modified = 'X'.

         w_fieldcat_alv-reptext_ddic = 'Alt Tip'.

       WHEN 'STATUS'.

         l_modified = 'X'.

         w_fieldcat_alv-reptext_ddic = 'Durum'.

       WHEN 'MOD_USER'.

         l_modified = 'X'.

         w_fieldcat_alv-reptext_ddic = 'Son Değiştiren'.

     ENDCASE.

     IF l_modified NE space.

       w_fieldcat_alv-seltext_l = w_fieldcat_alv-reptext_ddic.

       w_fieldcat_alv-seltext_m = w_fieldcat_alv-reptext_ddic.

       w_fieldcat_alv-seltext_s = w_fieldcat_alv-reptext_ddic.

     ENDIF.

     w_fieldcat_alv-key = space.

     MODIFY lt_fieldcat FROM w_fieldcat_alv.

   ENDLOOP.

   PERFORM zinc_alv_grid_display TABLES gt_outtab

                               USING lt_fieldcat

                                     p_vari

                                     'STANDARD'

                                     'USER_COMMAND'

                                     lw_layout

                                     lw_events

                                     lw_eventexit.

ENDFORM.

FORM standard USING  extab TYPE slis_t_extab.

   SET PF-STATUS 'STANDARD' EXCLUDING extab.

ENDFORM.                    "standard

[former_member226225]

Hi eldanar,

Pass gt_final[] instead of gt_outtab[] for the ALV .

pass the gt_final for the SET_TABLE_FOR_FIRST_DISPLAY method.

CALL METHOD GRID1->SET_TABLE_FOR_FIRST_DISPLAY

CHANGING  IT_OUTTAB        = GT_FINAL[].

Thanks & Regards,

Raghunadh Kodali