ansaurus

Question

Oracle-SQL: Generating cyclical, composite sequences

Answer 1

+7 A:

Personally I would store just a NUMBER and then calculate the "composite sequence" on the fly with something like:

select
chr(ascii('A') + ((number_sequence div 100) mod 26)) || to_char(number_sequence mod 100) composite_sequence,
...
from mytable

26 assuming the English alphabet, modify for your desired alphabet

gpeche 2010-09-08 20:36:43

+1 Exactly! Use a sequence, and use that 'A00' and 'A99' stuff only for display.

Rob van Wijk 2010-09-09 08:27:16

Awesome! Thanks for the code. Got some ideas.

invarbrass 2010-09-09 08:39:53

If anyone's interested, here's the Oracle-compatible rewrite of the above query: SELECT CONCAT(CHR(ASCII('A') + MOD((SEQUENCE_NAME.NEXTVAL / 100), 26)), TO_CHAR(MOD(SEQUENCE_NAME.CURRVAL, 100))) COMPOSITE_KEY FROM DUAL;

invarbrass 2010-09-15 14:49:40

Answer 2

+3 A:

Use:

  SELECT CHR(x.ascii) || LPAD(y.num - 1, 2, '0') AS val
    FROM (SELECT 64 + LEVEL AS ascii
          FROM DUAL
    CONNECT BY LEVEL <= 26) x,
      (SELECT LEVEL AS num
         FROM DUAL
   CONNECT BY LEVEL <= 100) y

OMG Ponies 2010-09-08 20:37:51

`chr()`, not `ascii()` :)

be here now 2010-09-09 02:54:55

@be here now: I don't know what you mean :)

OMG Ponies 2010-09-09 02:58:23

Thanks for the code! I have some questions: turns out I'll need to reset the "counter" each day (i.e. at the start of the day, the sequence should begin at ***A00***) How may I "reset" the sequence?

invarbrass 2010-09-10 15:31:24

Another issue I've found: despite the "CONNECT BY LEVEL <= 90" clause, this query doesn't stop at Z99 - it continues indefinitely printing garbage characters (in terms of 1251 codepage at least ;)) How can we reset the sequence at Z99?

invarbrass 2010-09-10 15:37:14

@invarbrass: What version of Oracle are you using? I did see something about this use of `CONNECT BY` being 10g+... Otherwise, there's no need to "reset"--it should be generating A00-Z99, every time you run the query.

OMG Ponies 2010-09-10 15:41:20

I'm running 10gXE (win32) on my dev machine. The production db runs on 10gR2 (x64) (we have plans to upgrade to 11gR2 next year)

invarbrass 2010-09-10 19:37:30

Oops! Hit the save button and I can't edit the previous comment... Anyway, after **Z99** value is generated, subsequent series starts at **]00**, then **^00** and so on. I've generated about 10,000 sequences from this query - it doesn't revert back to **A00**.

invarbrass 2010-09-10 19:46:40

@invarbrass: I'm going to have to load Oracle XE, but I've never known `LEVEL` to be persisted between queries or sessions.

OMG Ponies 2010-09-10 19:49:53

It seems the second "SELECT LEVEL...CONNECT BY" clause (**y**) is interfering with the first one (**x**). The numeric part of the compound sequence always resets at 99, it's the alphabet part that goes well over the boundary.

invarbrass 2010-09-10 19:59:54

invarbrass 2010-09-10 20:19:22

But that still leaves me with the second problem unsolved. At the beginning of each day, I need to reset the "sequence" (i.e. start from **A01** each morning).The database app will be used by a SME that caters to about 1000 clients each day. This sequence will be used as an unique serial number for every client each day (some of the job involves human involvement and it's much easier to work with simple codes instead of a huge numeric PK)

invarbrass 2010-09-10 20:21:32

@invarbrass: Sorry about that - I forgot to update the LEVEL limit in the first query after realizing it would start at one. Thx, corrected.

OMG Ponies 2010-09-10 20:27:58

@invarbrass: I loaded Oracle XE and tested the query. Every time I run the query, either by clicking the **Run Statement** or a new window in PLSQL Developer, I get a dataset of 2600 rows, beginning at A00 and ending with Z99 as desired. No need to "reset sequences", because it's a derived table. I used the query to create view, because it's self contained--it will return the values you desire every time you use the query I posted (or as a view).

OMG Ponies 2010-09-11 21:43:39

@invarbrass: The only issue I see is whether A00 should be in the results - you want them to "wrap around", but seem to want it to start at "A01" rather than A00? Is it really just a matter of ordering that you're hung up on?

OMG Ponies 2010-09-11 21:44:53

@OMG Ponies: Thanks for the reply. No, the A00 vs A01 value is not an issue. I just need the ability to start over from **A00** (or **A01** for that matter) at the beginning of each working day (I guess the word reset doesn't apply here)... i.e. the first client of each day will always get the sequence **A00** (and not the next sequence value from the previous day)

invarbrass 2010-09-15 14:27:35

ansaurus

tags:

views:

answers:

Oracle-SQL: Generating cyclical, composite sequences

related questions