Search code examples
abapopensqlalvinternal-tables

Show roles of two users against each other?

I have written the below ABAP program which can fetch the roles of two users from AGR_USERS, which are specified by user on the selection screen.

Output screen should have User id as well as roles assigned to the user. Program is working well for one user but cannot fetch the roles for the second user.

REPORT Z_REFERNCE_USER_PROG.
TYPE-POOLS: slis.  " SLIS contains all the ALV data types
TYPE-POOLS: SLIS,KCDE.
DATA: username(20) TYPE C,
      G_REPID TYPE SY-REPID,
      IT_EVENTS TYPE SLIS_T_EVENT,
      IT_FIELD TYPE SLIS_T_FIELDCAT_ALV,
      WA_FIELD TYPE SLIS_FIELDCAT_ALV,
      IT_SORT TYPE SLIS_T_SORTINFO_ALV.
INITIALIZATION.
G_REPID = SY-REPID.

DATA:BEGIN OF itab OCCURS 0,
       UNAME type AGR_USERS-UNAME,
       AGR_NAME  type AGR_USERS-AGR_NAME,
      UNAME1 type AGR_USERS-UNAME,
       AGR_NAME1  type AGR_USERS-AGR_NAME,
      END of itab.

start-of-selection.
     SELECT-OPTIONS AFF_USER FOR USERNAME NO INTERVALS.
     SELECT-OPTIONS REF_USER FOR USERNAME NO INTERVALS.
end-of-selection.

perform tosql.
perform listshow.
*--------------------------------
* form tosql
*--------------------------------
form tosql.
SELECT DISTINCT AGR_USERS~AGR_NAME    AGR_USERS~UNAME
         INTO corresponding fields of table itab
        FROM AGR_USERS
        where AGR_USERS~UNAME in AFF_USER.

SELECT DISTINCT AGR_USERS~AGR_NAME    AGR_USERS~UNAME
         INTO corresponding fields of table itab
        FROM AGR_USERS
        where AGR_USERS~UNAME in REF_USER.

endform.
*--------------------------------
* form listshow
*--------------------------------
form listshow.
  DEFINE ADD_FIELD.
    WA_FIELD-FIELDNAME = &1.
    WA_FIELD-REPTEXT_DDIC = &2.
    WA_FIELD-NO_ZERO = 'X'.
    APPEND WA_FIELD TO IT_FIELD.
  END-OF-DEFINITION.
     ADD_FIELD 'UNAME' 'Affected user'.
     ADD_FIELD 'AGR_NAME' 'Role'.
     ADD_FIELD 'UNAME1' 'Reference user'.
     ADD_FIELD 'AGR_NAME1' 'Role'.

  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'"
       EXPORTING
            I_CALLBACK_PROGRAM = G_REPID
            I_BACKGROUND_ID   = 'ALV_BACKGROUND'
            IT_FIELDCAT        = IT_FIELD
*            IS_LAYOUT          = GS_LAYOUT
*            IT_SORT            = IT_SORT
            I_SAVE             = 'A'
            IT_EVENTS          = IT_EVENTS[]
       TABLES
            T_OUTTAB           = itab
       EXCEPTIONS
            PROGRAM_ERROR = 1
            OTHERS        = 2.
endform.

Input: enter image description here Output: enter image description here

I want to get all values from first SELECT into table 1 and all values of second select into table 2 and then combine them in table 3. I just want to see the roles of both the users adjacent to each other, the order does not matter.

Solution

  • SELECT ... INTO overwrites the content of the table with the results. So when you want to have the results of both SELECTS, you need two tables:

    SELECT DISTINCT agr_name, uname
        INTO TABLE @DATA(lt_aff)
        FROM agr_users
        WHERE uname IN @aff_user.

SELECT DISTINCT agr_name, uname
        INTO TABLE @DATA(lt_ref)
        FROM agr_users
        WHERE uname IN @ref_user.

    Now how do you get those two results into one table?

    The problem is that you don't know which one of these two tables will be longer. So we need to use a WHILE loop with a counter which counts up while the counter is smaller than the line count of either table. Then within that loop, we need to get the lines of the two tables which correspond to the current value of the counter using READ TABLE. If successful, we need to move its values to a new line for the output table itab, which we then append to it:

    DATA lv_counter TYPE i.
DATA ls_result LIKE LINE OF itab.

lv_counter = 0.

WHILE lv_counter < lines( lt_aff ) OR lv_counter < lines( lt_ref ).
   lv_counter = lv_counter + 1.
   CLEAR ls_result.

   READ TABLE lt_aff INDEX lv_counter INTO DATA(ls_aff).
   IF sy-subrc = 0.
      ls_result-uname = ls_aff-uname.
      ls_result-agr_name = ls_aff-agr_name.
   ENDIF.

   READ TABLE lt_ref INDEX lv_counter INTO DATA(ls_ref).
   IF sy-subrc = 0.
      ls_result-uname1 = ls_ref-uname.
      ls_result-agr_name1 = ls_ref-agr_name.
   ENDIF.

   INSERT ls_result INTO TABLE itab.
ENDWHILE.