Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Select Statement question with Sequence

Posted on 2011-02-24
10
Medium Priority
?
386 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 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.

581 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