How to complete Multiple Join within With Query?

Question

I want to complete an additional Inner Join within the query below but am encountering syntax errors after creating a temporary table and inner joining.

WITH myPos
AS (
    SELECT row_number() OVER (
            PARTITION BY pr_emp_id ORDER BY db_last_upd
            ) AS RowNum
        ,pos.*
    FROM master.s_postn pos
    )

SELECT act.row_id
    ,People.SalesID
FROM master.s_evt_act act
INNER JOIN (
    SELECT myPos.RowNum AS update_count
        ,myPos.db_last_upd
        ,myPos.bu_id
        ,myPos.ou_id
        ,myPos.Name
        ,myPos.pr_emp_id
        ,regexp_extract(myPos.Name, '(\\d+)', 1) AS SalesID
    FROM myPos
    INNER JOIN (
        SELECT max(rowNum) maxRowNum
            ,pr_emp_id
        FROM myPos
        GROUP BY pr_emp_id
        ) AS maxPos ON myPos.pr_emp_id = maxPos.pr_emp_id
        AND myPos.rowNum = maxPos.maxRowNum
    WHERE lower(myPos.name) LIKE '% specifictitle %'
    ) AS People ON People.pr_emp_id = act.owner_per_id

With the additional data captured in the following query:

    SELECT sr_num
        ,owner_per_id
        ,x_cs_mgr_txt
    FROM mastertable.s_srv_req AS req
    WHERE req.x_cs_mgr_txt IS NOT NULL
        AND req.sr_stat_id <> 'Closed'

This additional Join would be ON req.owner_per_id = act.owner_per_id

I've done a lot of trial and error and thought I'd as for a little help.

Thanks!

Answer 1

Hard to say something more than try this:

WITH myPos
AS (
    SELECT row_number() OVER (
            PARTITION BY pr_emp_id ORDER BY db_last_upd
            ) AS RowNum
        ,pos.*
    FROM master.s_postn pos
    )

SELECT act.row_id
    ,People.SalesID
FROM master.s_evt_act act
INNER JOIN (
    SELECT myPos.RowNum AS update_count
        ,myPos.db_last_upd
        ,myPos.bu_id
        ,myPos.ou_id
        ,myPos.Name
        ,myPos.pr_emp_id
        ,regexp_extract(myPos.Name, '(\\d+)', 1) AS SalesID
    FROM myPos
    INNER JOIN (
        SELECT max(rowNum) maxRowNum
            ,pr_emp_id
        FROM myPos
        GROUP BY pr_emp_id
        ) AS maxPos ON myPos.pr_emp_id = maxPos.pr_emp_id
        AND myPos.rowNum = maxPos.maxRowNum
    WHERE lower(myPos.name) LIKE '% specifictitle %'
    ) AS People ON People.pr_emp_id = act.owner_per_id
    INNER JOIN (
    SELECT sr_num
        ,owner_per_id
        ,x_cs_mgr_txt
    FROM mastertable.s_srv_req AS req
    WHERE req.x_cs_mgr_txt IS NOT NULL
        AND req.sr_stat_id <> 'Closed'
    ) AS req  ON req.owner_per_id = act.owner_per_id