Re: Sequential number gen function that uses letter A-Z

From: Danny Roosens <roosens>
Date: 1996/08/02
Message-ID: <4tu3td$2nsq_at_news-s01.ny.us.ibm.net>#1/1


debor19071_at_aol.com (Debor19071) wrote:
>It came up in a meeting I attended that there is a need to produce up to
>36 values per digit of a varchar2(7) column - the 36 values would be 0-9 &
>A-Z
>
>Would anyone know of how we could do this. Someone from Oracle already
>mentioned a decode of a sequence - if the number is greater than 9, decode
>it to A-Z, but that is not the most preferred way to do it, because we'd
>have to recreate the sequence every time it reached 36 and we would have
>to do a substring of the generated number in order to know what place to
>add 1 to. I would like to hear of functions, procedures or other ways we
>could do this.
>
>Please help us!
>
>Thanks very much in advance
>
Hi,

first about your sequence : if you use the option CYCLE the sequence restarts after you reach 36.

instead of the DECODE, I'd use a small table hoding the values 1-36 and there translations, and join this with your sequences value something like in :

select T.SYMBOL
from CONVERT T
where T.NUMBERVALUE = my_seq.nextval;

ir Danny Roosens
Genesys - Belgium Received on Fri Aug 02 1996 - 00:00:00 CEST

Original text of this message