Solved

Select Statement question with Sequence

Posted on 2011-02-24
10
373 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 74

Expert Comment

by:sdstuber
ID: 34972164
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
ID: 34972203
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
ID: 34972434
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
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 74

Expert Comment

by:sdstuber
ID: 34972549
>>>> 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
ID: 34973334
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
 
LVL 11

Expert Comment

by:yuching
ID: 35014064
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
ID: 35018933
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 74

Expert Comment

by:sdstuber
ID: 35018964
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
ID: 35019030
Sure.

Thanks
0
 
LVL 15

Author Closing Comment

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

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

830 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