Solved

Select Statement question with Sequence

Posted on 2011-02-24
10
364 Views
Last Modified: 2013-12-19
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.

0
Comment
Question by:Devinder Singh Virdi
10 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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

0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
Comment Utility
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.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>>> 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


0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
Comment Utility
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

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Expert Comment

by:yuching
Comment Utility
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)
)
0
 
LVL 15

Accepted Solution

by:
Devinder Singh Virdi earned 0 total points
Comment Utility
Since tables were not huge and job needs to run once at any quite time,
We had written small PL/Block to resolve this problem.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
since there isn't a usable answer for the PAQ,  I recommend a delete here instead
0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
Comment Utility
Sure.

Thanks
0
 
LVL 15

Author Closing Comment

by:Devinder Singh Virdi
Comment Utility
Wrote PL/Block not to increment sequence whenever required.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now