Skip to Content

How to join the following 3 tables

I have the following 3 tables with data:

ZMYTABLE with columns: ZUSER & ZTCODE and 2 records

elias  VA01  
elias  VF01

AGR_1251 with the records

SD_role  VA01  
SD2_role VA01  
SD3_role VA01  
SD_role  VA02  
FI_role  VF01  
FI_role  VF02

AGR_USERS with records

elias  SD_role  
elias  SD2_role   
maria  SD_role  
maria  FI_role

I want to display the fields ZUSER, ZTCODE and AGR_NAME.
I want all records from ZMYTABLE with the role in which exists each tcode for the specific user, namely:

ZUSER---ZTCODE---AGRNAME  
elias---VA01-----SD_role  
elias---VA01-----SD2_role  
elias---VF01-----        

Can someone tell me how to do this by joining the 3 tables? Is there any way?

The system is an old one ECC6 V7.01 sp07.

Thanks

Elias

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Posted on Feb 08, 2019 at 10:28 AM

    Hi,

    You can go to transaction ABAPDOCU and read the stuff on selects and joins.

    But what is it functionally what you're trying to achieve? A list of users and their transactions in their authorization roles?

    There's probably an SAP standard report or transaction with which you can conjure up this information.

    Kind regards, Rob Dielemans

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 09, 2019 at 12:11 PM

    If there is a strong need to go to database (DB) only once (which is implicitly the best practice), then I would go for selecting the abundance of data and then doing sort and deletion of internal table. Otherwise the work around could be 2 selects: firstly, selecting data from ZMYTABLE only and then doing SELECT FROM agr_users JOIN agr_1251 FOR ALL ENTRIES IN (previously selected ZMYTABLE). Anyhow, one select and deletion could propably go as this:

    SELECT ZUSER, ZTCODE, AGR_1251~AGR_NAME, AGR_USERS~(ZUSER equivalent)
    	INTO TABLE lt_user_specific_tcodes_by_role
    	FROM ZMYTABLE
    	INNER JOIN AGR_1251 ON ZMYTABLE~ZTCODE = AGR_1251~? (ZTCODE equivalent)
    	INNER JOIN AGR_USERS ON ZMYTABLE~ZUSER  = AGR_USERS-? (ZUSER equivalent) 
    
    DELETE lt_user_specific_tcodes_by_role WHERE (ZUSER equivalent) = ''.
    

    The idea behind this is to also select information which line is abundant (AGR_USERS~(ZUSER equivalent) eg. you wont fill username to your internal table if there is none in AGR_USERS) and then removing it. Sorry if the code is not precise - I cant access any system right now. Though I hope that this idea can get you started. But personally, as a novice programmer, I would go for 2 selects and for all entries solution - althought it puts more load on DB, I find it easier to understand.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 08, 2019 at 10:59 AM
    -1

    No Rob it's not a standard table. We have a custom one and we must check this custom table with the standards. Unfortunately, there is no RIGHT JOIN in SAP. I found a workaround and solve my problem, but this is not a good program code. Of course if you do not have the full functionality of the JOIN, you go to the bad code.

    Elias

    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.