How to use CASE-operator for generating different sequences as per condition given

user_13023452 0
Viewed 185

I want to generate a sequence by matching the condition given. I've two sequences in the case condition and depending on the test condition the query should generate the respective sequence. However even though the output is correct both the sequences are being generated and resulting in missed sequence issue. Is there any way that only the success test condition is executed. Below is the query used in oracle DB.

select CASE
    WHEN :x=7
    THEN seq1.NEXTVAL
    ELSE seq2.NEXTVAL
END output from dual;

Suppose I pass x input as 7, I will get nextvalue of seq1 as output which is correct, however the nextvalue for seq2 is also generated in back end and missed the next time sequence is generated. I need this condition for auditing.

oracle-sqldeveloper sequence case oracle
1 Answers

You already know what's going on with your code. See if this helps.

First, create both sequences:

SQL> create sequence seq1;

Sequence created.

SQL> create sequence seq2;

Sequence created.

Now, create two functions, one for each sequence:

SQL> create or replace function f1 return number as begin return seq1.nextval; end;
  2  /

Function created.

SQL> create or replace function f2 return number as begin return seq2.nextval; end;
  2  /

Function created.

Run the select statement several times; once with input value 7 and several times with other values. But, don't select directly from the sequence - use functions instead:

SQL> select case when &x = 7 then f1
  2              else f2
  3         end result
  4  from dual;
Enter value for x: 7
old   1: select case when &x = 7 then f1
new   1: select case when 7 = 7 then f1

    RESULT
----------
         1

SQL> /
Enter value for x: 2
old   1: select case when &x = 7 then f1
new   1: select case when 2 = 7 then f1

    RESULT
----------
         1

SQL> /
Enter value for x: 3
old   1: select case when &x = 7 then f1
new   1: select case when 3 = 7 then f1

    RESULT
----------
         2

SQL> /
Enter value for x: 4
old   1: select case when &x = 7 then f1
new   1: select case when 4 = 7 then f1

    RESULT
----------
         3

SQL> /
Enter value for x: 5
old   1: select case when &x = 7 then f1
new   1: select case when 5 = 7 then f1

    RESULT
----------
         4

OK; let's now check sequence's values:

SQL> select seq1.currval, seq2.currval from dual;

   CURRVAL    CURRVAL
---------- ----------
         1          4

Aha! They aren't the same as they were using your code (i.e. having sequences in the select statement). Therefore, this might be a workaround for your problem.

However, sequences aren't to be used if you want gapless list of numbers. They will provide uniqueness, that's for sure, but - you most probably can't avoid gaps.

user_90979060
Related