Geek Answers Handbook

Sequence and case in choice in oracle

I am trying to execute this request (in oracle), but I have some problems:

SELECT CASE WHEN deptno = '10' THEN scott.seq.nextval || 'next10' WHEN deptno = '20' THEN scott.seqnextval || 'next20' WHEN deptno = '30' THEN scott.seq.currval || 'curr' END col_1 FROM scott.emp;

I get the following results:

  Col_1
 ----------------------------------------------
 191next20
 192curr
 193curr
 194next20
 195curr
 196curr
 197next10
 198next20
 199next10
 200curr
 201next20
 202curr
 203next20
 204next10
 205next20
 206next10
 207next10

And I think they should be:

  Col_1
 ----------------------------------------------
 191next20
 192curr
 193curr
 194next20
 194curr
 194curr
 197next10
 198next20
 199next10
 199curr
 201next20
 201curr
 203next20
 204next10
 205next20
 206next10
 207next10

So, why do I get the next value of the sequence also when I have to have the current value, and not just when the case selects the next value? Yes, this can be done using the plsql script, but I cannot.

Thank you!

+4
source share
2 answers

Interesting. For Oracle docs :

Operators in the WHEN clause can modify the database and call non-deterministic functions. There is no failure mechanism, as in the C switch instruction

Note that it does not say statements in the “true” WHEN clause. Thus, even if the when statement is false, nextval will fire:

 select case when 1=0 then 'next ' || seq_id.nextval when 1=1 then 'curr ' || seq_id.currval end col1 from dual;

I must admit that this is different than expected.

EDIT: See ShannonSeverance Answer

+1
source

Nextval and currval are not functions, but are a " sequence of pseudo-commands .

"In one SQL statement containing a reference for NEXTVAL, Oracle increments the sequence once: for each row returned by the external query block of the SELECT statement. The query block may appear in the following places ..." (emphasis added) [ Oracle Database Reference , "How to use sequence values" ]

In other words, seq.nextval not a side-effect function, but a pseudo- seq.nextval that has a specific value for each row. When there is one link to seq.nextval , the value is incremented for each row, regardless of whether that value is used. The result of OP sees peculiar to sequences, but not to case expression. For example, the same with decoding:

 SQL> select decode(deptno 2 , 10, seq.nextval || 'next10' 3 , 20, seq.nextval || 'next20' 4 , 30, seq.currval || 'curr30') 5 from emp; DECODE(DEPTNO,10,SEQ.NEXTVAL||'NEXT10',20,SEQ. ---------------------------------------------- 35next20 36curr30 37curr30 38next20 39curr30 40curr30 41next10 42next20 43next10 44curr30 45next20 46curr30 47next20 48next10
+8
source

More articles:


All Articles