Re: How to cycle sequence number.

Shirley (shirley_at_odin.mda.uth.tmc.edu) wrote:
> I have created a sequence to generate unique numbers which are to be used
> as filenames. When I have filled up one folder with 50 files, I want to
> move the another folder and start the filename with the MinValue of the
> sequence.
> Example:
> folderA has files 1, 2, 3 ... 50
> folderB has files 1, 2, 3, 4, ...50
> folder C has files 1, 2, etc
 

> This is how I created the sequence
> create sequence GenFileName
> increment by 1
> start with 1
> maxvalue 1000
> minvalue 1
> noCycle
> order;
 

> The reason I set it to NoCycle is because each file within the same
> folder must have unique filename.
 

> Question: Is using the sequence the best way to accomplish this? What
> other methods should I look into?

Sorry, I'm missing something: I was expecting you to say that you were using *two* sequences, at which point I'd have said 'No! use one' The obvious thing to do seems to be to take the next value from the sequence, and use MOD and TRUNC to chop it into a file number and folder number as in:
  SELECT GenFileName INTO FileNo FROM DUAL;   FolderNo := TRUNC(FileNo/50);
  FileNo := FileNo MOD 50;
  FolderName := CHR(ASCII('A')+FolderNo);

That should do it.

--
 _________________________   __________________________________________
/  Tommy Wareing          \ /  I've been looking for an original sin,  \
|  p0070621_at_uk.ac.brookes  X   One with a twist and a bit of a spin    |
\  0865-483389            / \     -- Pandora's Box, Jim Steinman       /
 ~~~~~~~~~~~~~~~~~~~~~~~~~   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~