asked on

Select Statement question with Sequence

Hi,

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)
FROM
(
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.

Sean Stuber

No, because curr_val can't change which is what you're trying to do.

Since some rows would call next_val and other rows not. curr_val won't have a reliable value in mid-run

Sharath S

I think you need DENSE_RANK. Can you check this?

SELECT emp_id, 
       old_emp_id, 
       DENSE_RANK() 
         OVER(PARTITION BY emp_id,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)

Open in new window

Devinder Singh Virdi

ASKER

Sean:
So basically, even if "Decode(rw, 1" conditions is not satisfied, sequence is incremented by one. ie
decode(rw, 1, TEMP_DEV_SQ.nextval, 0) -- even if rw value is not equal to 1, value of TEMP_DEV_SQ.nextval changes.
Do I need to write cursor based PL/Block or we can use alternate method?

Sharath:
Actually, I want to use sequence number and if RW is 1 then it should display nextval otherwise currval.

Sean Stuber

>>>> it should display nextval otherwise currval.

this doesn't make sense to me. currval is dependent on previous call of nextval

please provide sample input and expected output

Devinder Singh Virdi

ASKER

As per my understanding, statement will always do nextval first because of condition RW=1.
then we can use Currval.

I have split the operation into two parts. If we just look for first part, Decode part is not generating continuous sequence.
I believe this is the only problem where nextval is running (don't know for what reason) each time and making currval to display wrong value.

with decode(rw, 1, TEMP_DEV_SQ.nextval, 0)

   INDV_ID OLD_INDV_ID DECODE(RW,1,TEMP_DEV_SQ.NEXTVA
---------- ----------- ------------------------------
 530756578   529435617                             94
 530756579   529435617                              0
 529858144   529800629                             96
 529858145   529800629                              0
 529858146   529800629                              0
 529858147   529800629                              0
 529858148   529800629                              0
 529858149   529800629                              0
 529858150   529800629                              0
 529869322   529858157                            103
 529869323   529858157                              0
 529869324   529858157                              0

With decode(rw, 1,0, TEMP_DEV_SQ.currval)
   INDV_ID OLD_INDV_ID DECODE(RW,1,0,TEMP_DEV_SQ.CURR
---------- ----------- ------------------------------
 530756578   529435617                              0
 530756579   529435617                            117
 529858144   529800629                              0
 529858145   529800629                            117
 529858146   529800629                            117
 529858147   529800629                            117
 529858148   529800629                            117
 529858149   529800629                            117
 529858150   529800629                            117
 529869322   529858157                              0
 529869323   529858157                            117
 529869324   529858157                            117

Open in new window

yuching

refer to this, http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8491662046178
you can solve it by create a function as below

create function returnNextSeq return number is
dummy number;
begin
select EMP_SQ.nextval into dummy
from dual;
return dummy;
end;

SELECT emp_id, old_emp_id, decode(rw, 1, returnNextSeq , EMP_SQ.currval)
FROM
(
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)
)

ASKER CERTIFIED SOLUTION

Devinder Singh Virdi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

since there isn't a usable answer for the PAQ, I recommend a delete here instead

Devinder Singh Virdi

ASKER

Sure.

Thanks

Devinder Singh Virdi

ASKER

Wrote PL/Block not to increment sequence whenever required.