Trying to avoid duplication in Oracle query with table alias causing "missing right parenthesis" error

Question

I am trying to write a query that assembles a list of ID numbers from two tables.

The first part of the query selects employee ID numbers that start with 'B' and were created after January 1, 2020 from a table containing the master list of IDs. The second part of the query looks at another table that holds team IDs (which are composed of two or more employee IDs) - basically I want to select any team ID that contains an employee ID that meets the criteria in the first query.

I have the below code working and it returns the proper data:

SELECT 
    emp_id AS id
FROM 
    emp_master_view 
WHERE 
    emp_id IN
(
    SELECT DISTINCT t.team_id AS id
    FROM team_master_view t
    WHERE t.emp_id IN
    (
        SELECT emp_id
        FROM emp_master_view
        WHERE SUBSTR(emp_id, 1, 1) IN ('B')
        AND crt_date >= TO_DATE('2020-01-01', 'yyyy-mm-dd')
    )
)
UNION SELECT emp_id AS id
FROM emp_master_view
WHERE SUBSTR(emp_id, 1, 1) IN ('B')
AND crt_date >= TO_DATE('2020-01-01', 'yyyy-mm-dd')

However, I hate the duplication I need to use with the union select statement...I tried using a table alias like this:

SELECT 
    emp_id
FROM 
    emp_master_view 
WHERE 
    emp_id IN
(
    SELECT DISTINCT t.team_id AS emp_id
    FROM team_master_view t
    WHERE team_id IN
    (
        SELECT emp_id
        FROM emp_master_view
        WHERE SUBSTR(emp_id, 1, 1) IN ('B')
        AND crt_date >= TO_DATE('2020-01-01', 'yyyy-mm-dd')
    ) emp
)
UNION SELECT emp_id
FROM emp

...but this causes a "missing right parenthesis" error. I know I'm making some dumb mistake but I've tried a number of variations and can't get it working without duplicating the select entirely, so I'm waving the white flag. Can anyone point out the proper way to use a table alias in this case?

Edit

Sample data would be as follows:

emp_master_view contains the master list for every ID number, including teams:

EMP_MASTER_VIEW
+--------+------------+
| emp_id |  crt_date  |
+--------+------------+
| B56    | 2019-11-02 |
| B99    | 2020-03-02 |
| S34    | 2020-03-02 |
| RZF    | 2020-04-01 |
| RQR    | 2020-04-01 |
+--------+------------+

team_master_view contains only team IDs and there is a row for every individual employee in the team:

TEAM_MASTER_VIEW
+---------+--------+
| team_id | emp_id |
+---------+--------+
| RZF     | B99    |
| RZF     | B56    |
| RQR     | B56    |
| RQR     | S34    |
+---------+--------+

Desired results - pull out all of the IDs in the emp_master_view that meet the criteria and ALSO select all the team IDs from team_master_view where that team ID contains one of the employee IDs selected in the first part of the query.

Given the above tables, it would select B99 from emp_master_view as it's the only code that meets both criteria (starts with 'B' and created after Jan 1 2020). It would also select RZF from the team_master_view because that team ID contains B99. The end result set would be two rows:

B99
RZF

Hope that makes sense...

Answer 1

Since you want to use the same query more than once, you could use the WITH clause for subquery factoring:

with data as
(
    SELECT emp_id AS id
    FROM emp_master_view
    WHERE SUBSTR(emp_id, 1, 1) IN ('B')
    AND crt_date >= TO_DATE('2020-01-01', 'yyyy-mm-dd')
)
SELECT 
    emp_id AS id
FROM 
    emp_master_view 
WHERE 
    emp_id IN
(
    SELECT DISTINCT t.team_id AS id
    FROM team_master_view t
    WHERE t.emp_id IN
    (
        SELECT emp_id
        FROM data)
)
UNION 
select emp_id from data;

Update:

After you posted your table data and requirement, it looks like a simple JOIN between the two tables:

SELECT e.emp_id, 
       t.team_id
FROM
    emp_master_view e
    JOIN team_master_view t 
    ON e.emp_id = t.emp_id
WHERE
    e.emp_id LIKE 'B%'
    AND e.crt_date >= TO_DATE('2020-01-01', 'yyyy-mm-dd');