Sequence and case in a select in oracle

Question

I'm trying to do this query (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'm getting this results:

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

And this is what 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 i get the next value of the sequence also when I should have the current value and not only when the case selects the next value? Yeah, this could be done with a plsql script but I can't.

Thanks you!

Answer 1

Nextval and currval are not functions, but are "Sequence Pseudocolumns".

"Within a single SQL statement containing a reference to NEXTVAL, Oracle increments the sequence once: For each row returned by the outer query block of a SELECT statement. Such a query block can appear in the following places. ..." (emphasis added) [Oracle Database SQL Language Reference, "How to Use Sequence Values"]

In other words, seq.nextval is not a function with a side affect, but a pseudocolumn that has a particular value per row. Once there is a single reference to seq.nextval, the value increments for every row, whether or not the value is used. The outcome OP is seeing is a peculiar to sequences, not case expressions. For example, same thing with decode:

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

Answer 2

Interesting. Per the Oracle docs:

The statements in a WHEN clause can modify the database and call non-deterministic functions. There is no fall-through mechanism as in the C switch statement

Notice it doesn't say the statements in the "true" WHEN clause. So even if the when statement is false, the 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 this is different than I expected.

EDIT: See answer from ShannonSeverance