Hello All,
Can you share your inputs on this;
I have below two tables t1 and t2
with t1
as
(select 1001 emp_id, 9001 emp_number, 'Y' inc_flag, 2018 emp_year, 'Y' prv_inc_flag FROM DUAL
UNION ALL
select 1001 emp_id, 9001 emp_number, 'N' inc_flag, 2019 emp_year, 'Y' prv_inc_flag FROM DUAL
)
EMP_ID EMP_NUMBER INC_FLAG EMP_YEAR PRV_INC_FLAG
1001 9001 Y 2018 Y
1001 9001 N 2019 Y
t2
with t2
as
(select 1 record_id, 1001 emp_id, 9001 emp_number, 'Y' inc_flag, 2018 emp_year, 2000 total_due, 10 payment from DUAL
UNION ALL
select 2 record_id, 1001 emp_id, 9001 emp_number, 'Y' inc_flag, 2019 emp_year, 2000 total_due, 10 payment from DUAL)
select * from t2;
RECORD_ID EMP_ID EMP_NUMBER INC_FLAG EMP_YEAR TOTAL_DUE PAYMENT
1 1001 9001 Y 2018 2000 10
2 1001 9001 Y 2019 2000 10
Requirement is to fetch Max(emp_year) for emp_number from t1 and fetch records corresponding to that emp_year from t2 table.
E.g. Max (emp_year) for emp_number - 9001 from t1 table - 2019
records corresponding to max(emp_year) from t2 table would be
RECORD_ID EMP_ID EMP_NUMBER INC_FLAG EMP_YEAR TOTAL_DUE PAYMENT
2 1001 9001 Y 2019 2000 10
Below is the query which was written for the same
with t1
as
(select 1001 emp_id, 9001 emp_number, 'Y' inc_flag, 2018 emp_year, 'Y' prv_inc_flag FROM DUAL
UNION ALL
select 1001 emp_id, 9001 emp_number, 'N' inc_flag, 2019 emp_year, 'Y' prv_inc_flag FROM DUAL
),
t2
as
(select 1 record_id, 1001 emp_id, 9001 emp_number, 'Y' inc_flag, 2018 emp_year, 2000 total_due, 10 payment from DUAL
UNION ALL
select 2 record_id, 1001 emp_id, 9001 emp_number, 'Y' inc_flag, 2019 emp_year, 2000 total_due, 10 payment from DUAL)
SELECT t2_main.*
FROM t2 t2_main
WHERE t2_main.record_id IN
(SELECT MAX(t11.record_id)
FROM
(SELECT t2.record_id, t2.emp_id, t2.emp_number,
MAX(t2.emp_year) OVER (partition BY t2.record_id) emp_max_year
FROM t2 WHERE t2.emp_id = t2_main.emp_id
) t11,
t1
WHERE t11.emp_id= t1.emp_id
AND t1.emp_year = t11.emp_max_year
);
I am getting PL/SQL: ORA-00904: : invalid identifier pointing to t2_main.emp_id in <t2 WHERE t2.emp_id = t2_main.emp_id >. Can you suggest in rewriting it in better way.
Thanks!