Passing User name from Table HRP1001 to HRUS_D2 User Substituted by User

[Former Member]

I am new to writing queries through using SQ01 and SQ02 in SAP. I am trying to design a query where I grab the workflow approver/ reviewer from HRV1218_RO, show the funds center workflow responsibility, find the approver /reviewer user from HRP1001- SOBID, then pass the user over to HRUS_D2: User Substituted by User to find the active subsitutions. I joined HRV1218_RO, HRP1000, and HRP1001 with the OBJID field. So far this looks fine. When I try to determine how many active substitution an approver or reviewer has in table HRUS_D2, I am only able to retrieve one value for a user that actually should have more than one active substitutes. Could you assist me? I've created an extra field called Z_REP_NAME to get the HRUS_D2-REP_NAME values where Z_US_NAME represent another extra field HRUS_D2-US_NAME and Z_SOBID represents HRP1001-SOBID.

Here is my code:

Clear Z_US_NAME.

TABLES: HRUS_D2.

DATA: REP_NAME1 TYPE STRING.

REP_NAME1 = HRUS_D2-REP_NAME.

Select US_NAME FROM HRUS_D2 INTO Z_US_NAME
WHERE BEGDA <= sy-datum
AND ENDDA >= sy-datum
AND REPPR = ''
AND ACTIVE = 'X'
AND US_NAME = Z_SOBID
AND REP_NAME LIKE REP_NAME1.
ENDSELECT.

Clear Z_REP_NAME.

SELECT REP_NAME FROM HRUS_D2 JOIN HRP1001
ON HRUS_D2~US_NAME = HRP1001~SOBID
INTO Z_REP_NAME
WHERE US_NAME = Z_US_NAME
AND ACTIVE = 'X'
AND REPPR = ''.
ENDSELECT.

I appreciate it greatly!

Chad