This discussion is archived
13 Replies Latest reply: Mar 18, 2013 8:14 AM by Paul Horth RSS

currval acting like nextval

982331 Newbie
Currently Being Moderated
It will be very beneficial if you help me solve this problem and greatly appreciated. Please focus on the problem I’m having not why I’m doing what I’m doing. I’m trying to avoid a cursor solution and I can’t accomplish this using any group by or aggregate/analytical functions. This approach is basically trying to apply a group by while forcing the group by to operate on the data in a specific order(not the group by order). Sounds confusing, but if we focus on the problem… why is currval giving me the nextval we should get somewhere? Thanks.

Query:
INSERT INTO WAREHOUSE_DEV.STG_COV_SUBPROGRAM_FUN_CS1 (
CASENUMBER, PREV_CASENUMBER, CLIENT_NUMBER,
PREV_CLIENT_NUMBER, PROGRAMNAME, PREV_PROGRAMNAME,
SUBPROGRAMNAME, PREV_SUBPROGRAMNAME, COVERAGE_YEARMONTH,
CLIENT_COVERAGE, PREV_CLIENT_COVERAGE, CS_COUNT)
select CASENUMBER, PREV_CASENUMBER, CLIENT_NUMBER,
PREV_CLIENT_NUMBER, PROGRAMNAME, PREV_PROGRAMNAME,
SUBPROGRAMNAME, PREV_SUBPROGRAMNAME, COVERAGE_YEARMONTH,
CLIENT_COVERAGE, PREV_CLIENT_COVERAGE,
case when
CS_COUNT = 0 then 0
else case when CS_COUNT = 1 then 1
else case when (CS_COUNT = -1 AND LAG (CS_COUNT ) OVER (ORDER BY CASENUMBER, CLIENT_NUMBER, PROGRAMNAME, SUBPROGRAMNAME, COVERAGE_YEARMONTH) <> 0 ) then -1
else 1 -- catches tricky restart.....
end
end
end CS_COUNT
from
(select CASENUMBER, PREV_CASENUMBER, CLIENT_NUMBER,
PREV_CLIENT_NUMBER, PROGRAMNAME, PREV_PROGRAMNAME,
SUBPROGRAMNAME, PREV_SUBPROGRAMNAME, COVERAGE_YEARMONTH,
CLIENT_COVERAGE, PREV_CLIENT_COVERAGE,
case when
(CASENUMBER = PREV_CASENUMBER AND CLIENT_NUMBER = PREV_CLIENT_NUMBER AND PROGRAMNAME = PREV_PROGRAMNAME AND SUBPROGRAMNAME = PREV_SUBPROGRAMNAME AND PREV_CLIENT_COVERAGE=0) then 0
else case when (CASENUMBER = PREV_CASENUMBER AND CLIENT_NUMBER = PREV_CLIENT_NUMBER AND PROGRAMNAME = PREV_PROGRAMNAME AND SUBPROGRAMNAME = PREV_SUBPROGRAMNAME AND PREV_CLIENT_COVERAGE=1) then -1 -- EXCEPT FOR WHEN PREV PREV_CLIENT_COVERAGE WAS ZERO THEN 1.
else case when ((CASENUMBER <> PREV_CASENUMBER OR CLIENT_NUMBER <> PREV_CLIENT_NUMBER OR PROGRAMNAME <> PREV_PROGRAMNAME OR SUBPROGRAMNAME <> PREV_SUBPROGRAMNAME) AND PREV_CLIENT_COVERAGE=1) then-1
else case when ((CASENUMBER <> PREV_CASENUMBER OR CLIENT_NUMBER <> PREV_CLIENT_NUMBER OR PROGRAMNAME <> PREV_PROGRAMNAME OR SUBPROGRAMNAME <> PREV_SUBPROGRAMNAME) AND PREV_CLIENT_COVERAGE=0) then 1
else 88
end
end
end
end CS_COUNT

from WAREHOUSE_DEV.STG_COVERAGE_SUBPROGRAM_FUN_CS
where CASENUMBER = '0042432' and client_number = '0000942256'
ORDER BY CASENUMBER, CLIENT_NUMBER, PROGRAMNAME, SUBPROGRAMNAME, COVERAGE_YEARMONTH, CLIENT_COVERAGE);

PRODUCES RESULTS:
0042432     0042419     0000942256     0001571135     Child Care     Child Care     Child Care     Child Care     201008     1     0     1 nextval
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201009     1     1     -1 currval
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201010     1     1     -1 currval
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201011     1     1     -1 currval
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201012     1     1     -1 currval
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201101     1     1     -1 currval
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201102     1     1     -1 currval
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201103     0     1     -1 currval
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201104     1     0     0 0
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201105     1     1     1 nextval
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201106     1     1     -1 currval
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201107     1     1     -1 currval
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201108     1     1     -1 currval
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201109     1     1     -1 currval
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201110     1     1     -1 currval


The key is that when you have cs_count of 1… get nexval, when you have value of -1 get currval, zero is zero… The first row would get nexval…. So 1 the next seven rows should be 1, then next row 0, the next row 2 and the last 5 rows will also be 2.
That’s not what happens.

Query with currval and nextval:
Query:
INSERT INTO WAREHOUSE_DEV.STG_COV_SUBPROGRAM_FUN_CS1 (
CASENUMBER, PREV_CASENUMBER, CLIENT_NUMBER,
PREV_CLIENT_NUMBER, PROGRAMNAME, PREV_PROGRAMNAME,
SUBPROGRAMNAME, PREV_SUBPROGRAMNAME, COVERAGE_YEARMONTH,
CLIENT_COVERAGE, PREV_CLIENT_COVERAGE, CS_COUNT)

select CASENUMBER, PREV_CASENUMBER, CLIENT_NUMBER,
PREV_CLIENT_NUMBER, PROGRAMNAME, PREV_PROGRAMNAME,
SUBPROGRAMNAME, PREV_SUBPROGRAMNAME, COVERAGE_YEARMONTH,
CLIENT_COVERAGE, PREV_CLIENT_COVERAGE,
case when
(CS_COUNT = 0) then (0/warehouse_dev.cs_count_seq.nextval)
else case when (CS_COUNT = -1 AND LAG (CS_COUNT ) OVER (ORDER BY CASENUMBER, CLIENT_NUMBER, PROGRAMNAME, SUBPROGRAMNAME, COVERAGE_YEARMONTH) <> 0 ) then warehouse_dev.cs_count_seq.currval
else case when (CS_COUNT = 1) then warehouse_dev.cs_count_seq.nextval
else warehouse_dev.cs_count_seq.nextval
end
end
end CS_COUNT
from
(select CASENUMBER, PREV_CASENUMBER, CLIENT_NUMBER,
PREV_CLIENT_NUMBER, PROGRAMNAME, PREV_PROGRAMNAME,
SUBPROGRAMNAME, PREV_SUBPROGRAMNAME, COVERAGE_YEARMONTH,
CLIENT_COVERAGE, PREV_CLIENT_COVERAGE,
case when
(CASENUMBER = PREV_CASENUMBER AND CLIENT_NUMBER = PREV_CLIENT_NUMBER AND PROGRAMNAME = PREV_PROGRAMNAME AND SUBPROGRAMNAME = PREV_SUBPROGRAMNAME AND PREV_CLIENT_COVERAGE=0) then 0
else case when (CASENUMBER = PREV_CASENUMBER AND CLIENT_NUMBER = PREV_CLIENT_NUMBER AND PROGRAMNAME = PREV_PROGRAMNAME AND SUBPROGRAMNAME = PREV_SUBPROGRAMNAME AND PREV_CLIENT_COVERAGE=1) then -1 -- EXCEPT FOR WHEN PREV PREV_CLIENT_COVERAGE WAS ZERO THEN 1.
else case when ((CASENUMBER <> PREV_CASENUMBER OR CLIENT_NUMBER <> PREV_CLIENT_NUMBER OR PROGRAMNAME <> PREV_PROGRAMNAME OR SUBPROGRAMNAME <> PREV_SUBPROGRAMNAME) AND PREV_CLIENT_COVERAGE=1) then-1
else case when ((CASENUMBER <> PREV_CASENUMBER OR CLIENT_NUMBER <> PREV_CLIENT_NUMBER OR PROGRAMNAME <> PREV_PROGRAMNAME OR SUBPROGRAMNAME <> PREV_SUBPROGRAMNAME) AND PREV_CLIENT_COVERAGE=0) then 1
else 88
end
end
end
end CS_COUNT

from WAREHOUSE_DEV.STG_COVERAGE_SUBPROGRAM_FUN_CS
where CASENUMBER = '0042432' and client_number = '0000942256'
ORDER BY CASENUMBER, CLIENT_NUMBER, PROGRAMNAME, SUBPROGRAMNAME, COVERAGE_YEARMONTH, CLIENT_COVERAGE);

PRODUCES RESULTS:
0042432     0042419     0000942256     0001571135     Child Care     Child Care     Child Care     Child Care     201008     1     0     1
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201009     1     1     2
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201010     1     1     3
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201011     1     1     4
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201012     1     1     5
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201101     1     1     6
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201102     1     1     7
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201103     0     1     8
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201104     1     0     0
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201105     1     1     10
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201106     1     1     11
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201107     1     1     12
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201108     1     1     13
0042432     0042432     0000942256     0000942256     Child Care     Child Care     Child Care     Child Care     201109     1     1     14
  • 1. Re: currval acting like nextval
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!

    Whenever you post provide your 4 digit Oracle version.
    >
    It will be very beneficial if you help me solve this problem and greatly appreciated. Please focus on the problem I’m having not why I’m doing what I’m doing. I’m trying to avoid a cursor solution and I can’t accomplish this using any group by or aggregate/analytical functions. This approach is basically trying to apply a group by while forcing the group by to operate on the data in a specific order(not the group by order). Sounds confusing, but if we focus on the problem… why is currval giving me the nextval we should get somewhere?
    >
    That isn't how you identify solutions. Knowing why something is being done is the first step of the process; it can't be ignored.

    And just because you can't get the result using a group by or aggregate/analytical function doesn't mean that others can't.

    You are the one that is not focused on the problem; you are focused on the solution you want to use and are trying to keep people from using solutions that might be more appropriate.

    This is not focusing on a problem:
    >
    This approach is basically trying to apply a group by while forcing the group by to operate on the data in a specific order(not the group by order). Sounds confusing, but if we focus on the problem
    >
    That is stating the solution you are trying to impose on an 'unstated' problem.

    Why don't you start over and tell us what 'problem' you need to solve and let us help you find the best way to solve it.
  • 2. Re: currval acting like nextval
    982331 Newbie
    Currently Being Moderated
    why people like you feel they need to preach to others is beyond me. i stated the problem clearly:
    copied from post...
    This approach is basically trying to apply a group by while forcing the group by to operate on the data in a specific order(not the group by order).
  • 3. Re: currval acting like nextval
    rp0428 Guru
    Currently Being Moderated
    >
    This approach is basically trying to apply a group by while forcing the group by to operate on the data in a specific order(not the group by order).
    >
    That is NOT a problem statement. That is you attempt at a solution.

    You need to tell us what the problem is.

    It is YOU that is trying to preach to us by telling us what we can, and cannot consider. No one is trying to preach to you.

    Tell us what PROBLEM you are trying to solve; not what solution you want to use.

    This is how preaching would read:

    Read the FAQ for how to ask a question in the forums.

    Use \
     tags on the line before and after any code to preserver the formatting.
    
    Don't post two sets of unformatted code and results.
    
    When you do post code and results explain why those results are NOT correct and what results you are trying to produce.
    
    ALWAYS provide your 4 digit Oracle version.
    
    There: how'd I do?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 4. Re: currval acting like nextval
    SomeoneElse Guru
    Currently Being Moderated
    Can I hear AMEN!
  • 5. Re: currval acting like nextval
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    TractorEnvy wrote:

    why is currval giving me the nextval
    Because a sequence is a shared/concurrent object and not a serialised object.

    The current value that your SQL sees, may only exist for a brief moment as my SQL, running at the same time, just used a 100 sequence values. So between you current value call and your next value call, the sequence may have ticked over a number of times.

    Which is why the only safe call that you can make is getting the next value - as that will be valid and unique. You cannot use the current value call and then use that result in subsequent logic, as that current value results ceases to be that for all intents and purposes, the very moment after your code reads that value.

    As for the comments made by rp0428 - please consider. The forum is like a tool. And you need to use that tool correctly to get the desired results you want from the tool. This forum is best used for addressing problems. Not to get to fix broken solutions for unknown problems.
  • 6. Re: currval acting like nextval
    Stew Ashton Expert
    Currently Being Moderated
    TractorEnvy wrote:
    why is currval giving me the nextval we should get somewhere?
    http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns002.htm#SQLRF50946
    Within a single SQL statement containing a reference to NEXTVAL, Oracle increments the sequence once:
    ...
    If any of these locations contains references to both CURRVAL and NEXTVAL, then Oracle increments the sequence and returns the same value for both CURRVAL and NEXTVAL.
    create sequence tseq;
    WITH DATA AS (SELECT LEVEL-1 n FROM dual CONNECT BY LEVEL <= 2)
    SELECT 
    CASE WHEN n = 1 THEN tseq.nextval
    ELSE tseq.currval
    end seq
    from data;
    
           SEQ
    ----------
             1 
             2
    Edited by: Stew Ashton on Mar 16, 2013 10:00 AM
  • 7. Re: currval acting like nextval
    982331 Newbie
    Currently Being Moderated
    Thank you for the serialized answer it really shouldn't of surprised me. I appreciate it i can now continue on. I wanted to understand why currval wasn't working. If I wanted alternate approaches I would of asked for them. There are a lot of bright people on this forum but 50% of them don't want to answer the question being asked they want to tell the person why it's a dumb question or that they should do it another way. I hope this response helps someone else out there.
  • 8. Re: currval acting like nextval
    982331 Newbie
    Currently Being Moderated
    Thanks... especially for the reference to the doc. That's really too bad it increments it. Makes currval pretty useless in set processing.
  • 9. Re: currval acting like nextval
    sb92075 Guru
    Currently Being Moderated
    with free advice, you sometimes get what you paid for it.
  • 10. Re: currval acting like nextval
    982331 Newbie
    Currently Being Moderated
    Thanks... i really did laugh out loud... my wife probably wonders what is going on in that office... can't be work....
  • 11. Re: currval acting like nextval
    Stew Ashton Expert
    Currently Being Moderated
    From Kim Berg Hansen's blog :
    CREATE SEQUENCE te_seq;
    
    CREATE OR REPLACE FUNCTION te_seq_next_curr(p_num IN NUMBER)
    return number is
    BEGIN
    RETURN CASE p_num WHEN 0 THEN te_seq.currval ELSE te_seq.nextval END;
    END te_seq_next_curr;
    /
    
    SELECT ROWNUM rn,
    mod(ROWNUM-1,2) rn_mod,
    te_seq_next_curr(mod(ROWNUM,2)) seq
    FROM dual
    connect by rownum <= 10;
    
            RN     RN_MOD        SEQ
    ---------- ---------- ----------
             1          0          1 
             2          1          1 
             3          0          2 
             4          1          2 
             5          0          3 
             6          1          3 
             7          0          4 
             8          1          4 
             9          0          5 
            10          1          5 
  • 12. Re: currval acting like nextval
    982331 Newbie
    Currently Being Moderated
    Awesome it worked... I'll check performance.
  • 13. Re: currval acting like nextval
    Paul Horth Expert
    Currently Being Moderated
    TractorEnvy wrote:
    Thank you for the serialized answer it really shouldn't of surprised me. I appreciate it i can now continue on. I wanted to understand why currval wasn't working. If I wanted alternate approaches I would of asked for them. There are a lot of bright people on this forum but 50% of them don't want to answer the question being asked they want to tell the person why it's a dumb question or that they should do it another way. I hope this response helps someone else out there.
    Maybe because giving the technically correct answer to a wrong approach is anathema to them. They want to help, not steer you faster down the wrong path.

    A lot of the questions raised in the forum indicate a staggering misunderstanding of Oracle and a fundamentally flawed approach to whatever problem they are trying to solve.
    When they are then told that the approach is wrong and to do it another way, the O/P arrogantly decides to ignore that advice and whinges that nobody is answering their
    original question.

    Maybe you can see now why some people will try to get to the nub of the actual problem before giving a suitable answer.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points