topic Re: ptreq_attabsdata - ptreq_header - ptreq_items join is not getting data in Application Development and Automation Discussions

ptreq_attabsdata - ptreq_header - ptreq_items join is not getting data

Former Member — Tue, 11 Nov 2014 15:07:43 GMT

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.

Re: ptreq_attabsdata - ptreq_header - ptreq_items join is not getting data

roberto_vacca2 — Tue, 11 Nov 2014 17:59:25 GMT

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.

Re: ptreq_attabsdata - ptreq_header - ptreq_items join is not getting data

Former Member — Wed, 12 Nov 2014 07:26:59 GMT

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 ?

Re: ptreq_attabsdata - ptreq_header - ptreq_items join is not getting data

former_member226225 — Wed, 12 Nov 2014 12:56:06 GMT

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.

Re: ptreq_attabsdata - ptreq_header - ptreq_items join is not getting data

Former Member — Wed, 12 Nov 2014 14:20:42 GMT

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.

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

Re: ptreq_attabsdata - ptreq_header - ptreq_items join is not getting data

former_member226225 — Wed, 12 Nov 2014 14:47:51 GMT

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