Here is the case:
I have one table EMP and one sequence EMP_SQ.
There are two fields EMP_ID and OLD_EMP_ID,
EMP_ID is unique, whereas OLD_EMP_ID can be duplicate.
I want to select all records from EMP table for EMP_ID, OLD_EMP_ID, SEQ_NUM.
If OLD_EMP_ID is unique, then SEQ_NUM should be EMP_SEQ.nextval.
if OLD_EMP_ID has duplicate, then for first OLD_INDV_ID, SEQ_NUM should be increment by 1, for second, it should remain same, which is EMP_SEQ.CURRVAL
Therefore I wrote the following.
SELECT emp_id, old_emp_id, decode(rw, 1, EMP_SQ.nextval, EMP_SQ.currval)
select emp_id, old_emp_id, row_number() over(partition by old_emp_id order by emp_id) rw, count(*) over(partition by old_emp_id) cnt
FROM EMP cv
WHERE old_emp_id in ( 529435617, 529800629, 529858157)
I am not getting the correct result. Everytime I am getting different value for EMP_SQ. So before getting EMP_SQ.currval, EMP_SQ.nextval has fired. I tried Case when as well, but unable to correct the problem.
Is there any other way to do this in one statement.