Hello,
My existing query is
SELECT Ih.INVID
,ih.ASSIGN_USER_ID
,USR.F_NAME||' '||L_NAME AS "USER_NAME"
FROM INV_HDR IH
INNER JOIN REF_USER USR ON Ih.ASSIGN_USER_ID = USR.USER_ID
WHERE ACTIVE_FLAG = 'N'
UNION
SELECT Ih.INVID
,rdv.USER_ID
,USR.F_NAME||' '||L_NAME AS "USER_NAME"
FROM INV_HDR IH
INNER JOIN REF_DET_V4 rdv ON rdv.GROUP_ID = IH.ASSIGN_GROUP_ID
INNER JOIN REF_USER USR ON rdv.USER_ID = USR.USER_ID
WHERE ACTIVE_FLAG = 'N'
AND IH.ASSIGN_USER_ID IS NULL
I need to rewrite it in a single query without the union where INV_HDR is the main table . Any help please ? Thanks .
Expected data :
INVID ASSIGN_USER_ID USER_NAME
2 100 JTERRY
3 102 JTERRY
4 104 ABC