Using Oracle rank () on left join
When I run this subquery myself:
select *
from (select rpt_nbr, iteration, seq_nbr, emp_id_key, rank() over
(partition by emp_id_key order by iteration, seq_nbr) rk
from SJTCAPP.LAB_RPT_SPEC_EMP where rpt_nbr = 1572413)
where rk = rownum
I am getting a good result with only returning 1 emp_id_key
for each iteration and seq_nbr
although multiple may be assigned emp_id_key
. So this is fine, however, when I add this to the rest of my request:
select * from
SJTCAPP.LAB_RPT r
left join SJTCAPP.LAB_RPT_SPEC s on s.rpt_nbr = r.rpt_nbr
left join (select * from
(select rpt_nbr, iteration, seq_nbr, emp_id_key, rank() over (partition by emp_id_key order
by iteration, seq_nbr) rk from SJTCAPP.LAB_RPT_SPEC_EMP ) where rk = rownum)
se on se.rpt_nbr = s.rpt_nbr and se.seq_nbr = s.seq_nbr and se.iteration = s.iteration
left join sjtcapp.employee tech on tech.emp_id_key = se.emp_id_key
I am getting NULL for the tech.emp_id_key connection
Update:
select * from (select rpt_nbr, iteration, seq_nbr, emp_id_key, rank() over (partition by emp_id_key order by iteration, seq_nbr ) rk from SJTCAPP.LAB_RPT_SPEC_EMP where rpt_nbr = 1572413) where rk = rownum and rpt_nbr = 1572413
The above query also gives good results.
RPT_NBR ITERATION SEQ_NBR EMP_ID_KEY RK
1572413 1 1 44746 1
1572413 1 2 44746 2
Before I got a more direct connection here and got the correct query with the names of the individual technicians. The only problem is that if more than one was assigned, it caused a duplicate, so I added a rank subquery.
source to share
If constrain iteration and seq_nbr are unique you can use exists instead of rank
SELECT
rpt_nbr,
iteration,
seq_nbr,
emp_id_key
FROM
SJTCAPP.LAB_RPT_SPEC_EMP emp
WHERE
NOT EXISTS
(
SELECT
*
FROM
SJTCAPP.LAB_RPT_SPEC_EMP emp2
WHERE
emp2.emp_id_key = emp.emp_id_key AND
emp2.iteration < emp.iteration AND
emp2.seq_nbr < emp.seq_nbr
)
source to share
I ended up reverting to using GROUP BY
left join (
select min(rpt_nbr) as rpt_nbr, min(iteration) as iteration, min(seq_nbr) as seq_nbr, min(emp_id_key) as emp_id_key from LAB_RPT_SPEC_EMP group by rpt_nbr, iteration, seq_nbr
) se
on se.rpt_nbr = s.rpt_nbr and se.seq_nbr = s.seq_nbr and se.iteration = s.iteration
source to share