Solved

Select Statement question with Sequence

Posted on 2011-02-24
10
380 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 41

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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

688 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