"Use as Subquery" Option in Join Condition - For Workbook Reports

User_A51BX

Hi Team,
Req: We need option in Join as "Use as Subquery" (Forced to use subquery instead of Left or Right Join). This requirement is for Workbook.

Use Case:
In Contact Center Domain, we have may to one mapping within dimensional tables itself. When we join these Dim table and Mapping dim table with fact table, the data in fact table are duplicated based on multiple data in mapping table.
Why We need this: We need to use Dim table as prompt. When filtering then these fact data are getting duplicated as mentioned above.

Ex:
Attached Excel for table Info:

SELECT DA.AGENT_NAME,SUM(CALLS_ANSWERED) AS CALLS_ANSWERED FROM FACT_AGENT_CALLS FAC
LEFT JOIN DIM_AGENT DA ON FAC.AGENT_ID = DA.AGENT_ID
LEFT JOIN DIM_MAPPING DM ON DM.AGENT_ID = DA.AGENT_ID
LEFT JOIN DIM_GROUP DG ON DG.GROUP_ID = DM.GROUP_ID
WHERE DG.GROUP_NAME IN ('GROUP_NAME_1','GROUP_NAME_2','GROUP_NAME_3');

GROUP_NAME is prompt for user.
The above query result is getting duplicated. attached excel for the same.

DIM_AGENT:
AGENT_ID	AGENT_NAME
1	AGENT_1
2	AGENT_2
3	AGENT_3

DIM_GROUP:
GROUP_ID	GROUP_NAME
1	GROUP_NAME_1
2	GROUP_NAME_2
3	GROUP_NAME_3

DIM_MAPPING:
MAPPING_ID	GROUP_ID	AGENT_ID
1	1	1
2	1	2
3	1	3
4	2	1
5	3	1

FACT_AGENT_CALLS:
FACT_ID	AGENT_ID	CALLS_ANSWERED
1	1	5
2	2	5
3	3	5

RESULT:				EXPECTED RESULT:
AGENT_NAME	CALLS_ANSWERED			AGENT_NAME	CALLS_ANSWERED
AGENT_1	15			AGENT_1	5
AGENT_2	5			AGENT_2	5
AGENT_3	5			AGENT_3	5

Expected query with Subquery Option:

SELECT DA.AGENT_NAME,SUM(CALLS_ANSWERED) AS CALLS_ANSWERED FROM FACT_AGENT_CALLS FAC
LEFT JOIN DIM_AGENT DA ON FAC.AGENT_ID = DA.AGENT_ID
where DA.AGENT_ID in (SELECT DM.AGENT_ID FROM DIM_MAPPING DM
LEFT JOIN DIM_GROUP DG ON DG.GROUP_ID = DM.GROUP_ID
WHERE DG.GROUP_NAME IN ('GROUP_NAME_1','GROUP_NAME_2','GROUP_NAME_3'));