Need to use join result into second cursor in PL/SQL

21 views Asked by At

In current pl/sql script I have two cursor two of them have bunch of join query in them. I want to use the first cursor joins into second cursor to avoid discrepancy in the result. So from the first cursor JOIN query nbr j I want to use that in the second cursor so I can avoid nbr j again in the second cursor and no discrepancy comes.

`CURSOR first IS
SELECT 
j.nbr_ann_salary AS j_ann_salary,
j.nbr_status AS j_status,
j.nbr_effective_date AS j_effective_date
from res
JOIN nbr j
ON nbr_posn = 'result'
AND j.nbr_effective_date = (select max(m.nbr_effective_date)
FROM nbr m
WHERE j.nbr_posn = m.nbr_posn)
ORDER BY 2;

CURSOR second IS
SELECT
st.nbr_effective_date AS st,
spriden_id
from nbr j
JOIN nbr ST
ON j.nbr_pidm =st.nbr_pidm
AND j.nbr_posn= st.nbr_posn
AND j.nbr_suff = st.nbr_suff
AND st.nbr_effective_date = (
select min(m.nbr_effective_date)
FROM nbr m
WHERE st.nbr_pidm = m.nbr_pidm
AND st.nbr_posn = m.nbr_posn
AND st.nbr_suff = m.nbr_suff)
ORDER BY 2,1;`

I have tried to put them in nested cursor but the result is not what I expecting. In second cursor due to the independant cursor it doesn't get result from the cursor 1 result

0

There are 0 answers