Home » SQL & PL/SQL » SQL & PL/SQL » Recycle sequence
Recycle sequence [message #281931] Tue, 20 November 2007 01:22 Go to next message
ednms
Messages: 39
Registered: November 2007
Member
Hi. i have problem with sequence.
Is there any way we can know the sequence has been recycle
i've created sequence

CREATE SEQUENCE NPCS_NBNTSC_LTSN
START WITH 1
INCREMENT BY 1 MINVALUE 1 MAXVALUE 9999 NOCACHE CYCLE ORDER

my problem now is i use the sequence with this query

select ltrim(to_char(case when NPCS_NBNTSC_LTSN.nextval = 9999 then T4.BATCH_OCC_NUM+1
else T4.BATCH_OCC_NUM
end,'09'))
||ltrim(to_char(NPCS_NBNTSC_LTSN.currval,'0999'))
from NPCS_INBND_TXN T1
,NPCS_INBND_FILE_BATCH_HDR T4
where T1.NPCS_FILE_SEQ_NUM=T4.NPCS_FILE_SEQ_NUM
and T1.NPCS_FILE_BATCH_SEQ_NUM = T4.NPCS_FILE_BATCH_SEQ_NUM
and T1.NPCS_FILE_SEQ_NUM = 305

when NPCS_NBNTSC_LTSN reach 9999, i have to add 1 to BATCH_OCC_NUM. So when the sequence cycle, it still has to use BATCH_OCC_NUM + 1. how can i do that?
please help.
Re: Recycle sequence [message #281937 is a reply to message #281931] Tue, 20 November 2007 01:33 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

are you asking about how to increase the maxvalue of the sequence,then you can use the ALTER SEQUENCE command.


regards,
Re: Recycle sequence [message #281939 is a reply to message #281937] Tue, 20 November 2007 01:48 Go to previous messageGo to next message
ednms
Messages: 39
Registered: November 2007
Member
No. Ok for example

NPCS_NBNTSC_LTSN.currval = 9997
BATCH_OCC_NUM = 1

when NPCS_NBNTSC_LTSN.nextval reach 9999
BATCH_OCC_NUM should
increase by 1


i know my case statement is wrong because only when NPCS_NBNTSC_LTSN.nextval = 9999, then it add 1 to BATCH_OCC_NUM.
else remain.

case when NPCS_NBNTSC_LTSN.nextval = 9999 then T4.BATCH_OCC_NUM+1
else T4.BATCH_OCC_NUM
end

Re: Recycle sequence [message #281942 is a reply to message #281939] Tue, 20 November 2007 02:12 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As far as I can tell, I'd create a sequence as
CREATE SEQUENCE NPCS_NBNTSC_LTSN;

What's the purpose of setting its MAXVALUE if you want to make sequence increment its value nevertheless it reached its maximum value or not?

By the way, "increment by 1" might not always be true; depending on cached sequence values, you might have gaps in a sequence.
Re: Recycle sequence [message #281944 is a reply to message #281931] Tue, 20 November 2007 02:18 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Proper syntax would be:

SELECT 
   CASE NPCS_NBNTSC_LTSN.NEXTVAL
      WHEN 9999 then T4.BATCH_OCC_NUM+1
      ELSE T4.BATCH_OCC_NUM
   END CASE
...


@Littlefoot: OP specified CYCLE ORDER and NOCACHE, so the "increment by 1" would work (or is it still possible it won't???)

Would the following code show the same behavior:

SELECT DECODE(
          MOD( NPCS_NBNTSC_LTSN.NEXTVAL,9999) ,
               0 , T4.BATCH_OCC_NUM+1 ,
               T4.BATCH_OCC_NUM )
          )
...


Call me old-fashioned but for some unclear reason I prefer DECODE over CASE Smile

[Updated on: Tue, 20 November 2007 02:19]

Report message to a moderator

Re: Recycle sequence [message #281945 is a reply to message #281942] Tue, 20 November 2007 02:20 Go to previous messageGo to next message
ednms
Messages: 39
Registered: November 2007
Member
i want the sequence to recycle when i reach 9999.

my prob is how can i know the sequence has been recycled.
lets say i have 20000 records

records 1 to 9999 - 1 cycle
records 10000 to 19999 - 2 cycle


is there any indicator or way i can get the number of cycle the has occured?
Re: Recycle sequence [message #281948 is a reply to message #281945] Tue, 20 November 2007 02:26 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Analytic functions are your way out. Be careful when ordering your results!
Have a look at this example (it increases x at the end of the sequence cycle):
CREATE SEQUENCE mhe_seq 
 START WITH 1 
 INCREMENT BY 1
 NOCACHE
 MAXVALUE 99
 CYCLE
/
SELECT mhe_seq.nextval seqval
     , SUM(DECODE(MOD(rownum, 99),0,1,0)) OVER ( ORDER BY rownum)  x
FROM   dual
CONNECT BY LEVEL < 200
/

DROP SEQUENCE mhe_seq
/
My example doesn't really care about order (the order by rownum doesn't really make sense) but you'll catch the drift.

MHE
Re: Recycle sequence [message #281953 is a reply to message #281945] Tue, 20 November 2007 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
cycle: trunc(seq/10000)
base number: mod(seq,10000)

Regards
Michel


Re: Recycle sequence [message #281955 is a reply to message #281948] Tue, 20 November 2007 03:02 Go to previous messageGo to next message
ednms
Messages: 39
Registered: November 2007
Member
Thanks. It's work!. Razz
and help a lot.
Re: Recycle sequence [message #281957 is a reply to message #281953] Tue, 20 November 2007 03:04 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Michel Cadot wrote on Tue, 20 November 2007 09:58

cycle: trunc(seq/10000)
base number: mod(seq,10000)

Regards
Michel





But you can't have your sequence CYCLE, nor have a MAXVALUE.

Or am I missing something?
Re: Recycle sequence [message #281960 is a reply to message #281957] Tue, 20 November 2007 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No but this answer the question to have 2 columns: one with the cycle number and the other one with a value from 0 to 9999.

Regards
Michel
Re: Recycle sequence [message #281984 is a reply to message #281960] Tue, 20 November 2007 04:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I totally agree with Michel.
What if the sequence shoots through the 9999 in a rolled-back transaction? No way the counter is increased then! (ok, except when you'd use autonomous transactions)

Using mod & trunc will always work, regardless of rollbacks
Plus it enables caching, and thus makes it scalable.
Re: Recycle sequence [message #282056 is a reply to message #281931] Tue, 20 November 2007 13:14 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
also using nextval in the same select will ALWAYS return the same value. For example

select my_seq.nextval col1, my_seq.nextval col2
from dual;

will return the same value for col1 and col2. The database is smart enough to know what you are asking.

Previous Topic: Extract from unconventional XML.
Next Topic: Triggers
Goto Forum:
  


Current Time: Mon Dec 05 10:38:06 CST 2016

Total time taken to generate the page: 0.05397 seconds