OraQA

Oracle Question and Answer

  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed
« How to check a string format with the OWA_PATTERN.MATCH() function

How to determine remote dependencies mode? »

How to generate sequence numbers between two numbers

January 20th, 2006 By Eddie Awad

One way:

HR@XE> define v_from = 20
HR@XE> define v_to = 26
HR@XE> select (lvl + &v_from - 1) myseq
  2  from (
  3      select *
  4      from (
  5          select level lvl
  6          from dual
  7          connect by level <= (&v_to - &v_from) + 1
  8          )
  9      )
 10  order by myseq;
old   1: select (lvl + &v_from - 1) myseq
new   1: select (lvl + 20 - 1) myseq
old   7:         connect by level <= (&v_to - &v_from) + 1
new   7:         connect by level <= (26 - 20) + 1

     MYSEQ
----------
        20
        21
        22
        23
        24
        25
        26

7 rows selected.

The above does not work in 8i or below.

I'm sure there are other ways to answer this question.

A similar technique is used when Tom Kyte answered the question: How can I bind an in-list.

Filed under SQL. Follow any responses to this entry through this RSS 2.0 feed.  

7 Responses to “How to generate sequence numbers between two numbers”

  1. maceyah Says:
    January 20th, 2006 at 12:29 pm 
    SQL> create sequence seq1 minvalue 0 maxvalue 6 cycle cache 5;

Sequence created.

SQL> select seq1.nextval+20 from all_objects where rownum < 10;

SEQ1.NEXTVAL+20
---------------
             20
             21
             22
             23
             24
             25
             26
             20
             21

9 rows selected.
  2. Eddie Awad Says:
    January 20th, 2006 at 1:21 pm

    Maceyah, neat solution but is not very dynamic and it involves DDL.

    For example, using your method, to get the sequence numbers between 20 and 26 (i.e. 20, 21, 22, 23, 24, 25 and 26 — 7 ordered numbers in total), first you need to create a sequence with specific min and max values:

    create sequence myseq minvalue 1 maxvalue 7 cycle cache 6;

select myseq.nextval+19 ---- (from value - 1)
  from all_objects
 where rownum < =7 ---- (from - to + 1)

    Note that in this example, the minvalue and the maxvalue have to be 1 and 7 respectively (or 0 and 6), otherwise you may have numbers outside the range. So, if you have a different range you have to either create a new sequence or alter the existing one.

  3. Mihajlo Tekic Says:
    January 21st, 2006 at 9:54 am

    This is very nice solution it does not include any DDL activity.
    Since now, I used one table where I have only sequential numbers, which I increased always when I needed bigger range.

    Thanks,

  4. Laurent Schneider Says:
    January 23rd, 2006 at 7:50 am

    I just could not resist telling this is a hack, because connect by without prior is illegal and should generate a loop.

    Using “from all_objects” is a “limiting move”, because it is a slow view with a limited number of objects (maybe 500, maybe 50000, you do not know).

    You can search my blog for examples, but my “favorite” approach is to say there is no such function. Why do you need it? Think of doing a table containing your elements, ex :

    create table t(n number);
exec for i in 1..1000000 loop insert into t(n) values (i); end loop
commit;
select * from t where n between 20 and 26;
         N
----------
        20
        21
        22
        23
        24
        25
        26

    well, the PL/SQL part could be tuned, but the SELECT part is extremly fast

  5. Laurent Schneider Says:
    January 23rd, 2006 at 7:52 am 
    create table t(n number primary key);

    is better

  6. Eddie Awad Says:
    January 23rd, 2006 at 11:00 am

    Laurent Schneider Says: Why do you need it?

    A co-worker of mine asked me: “Lets say I have a table with a numeric field (called x) and there are 3 records in that table and for those 3 records field x has a value of 22, 23, 25. I want to produce a report (sql select statement) that lists any number between 21 and 25 that is not on this table (ie the sql select statement should return 21 and 24 as a result set). The range (between 21 and 25) is a user input, and as a result can change with each run.”

    The above query can come in handy to solve such a problem:

    select *
    from (
        select (lvl + &v_from - 1) mynum
            from (
                select *
                from (
                    select level lvl
                    from dual
                    connect by level < = (&v_to - &v_from) + 1
                   )
               )
       )
   where mynum not in (select x from t)
  7. gamyers Says:
    January 24th, 2006 at 8:53 pm

    Like Laurent, I’m not happy with the CONNECT BY/LEVEL solution.

    This is a good alternative for 10G, and the MODEL clause is actually intended to generate rows.

    SELECT days
FROM (select 20 days from dual) mnth
MODEL
  DIMENSION BY (days)
  MEASURES (days v)
  RULES UPSERT
  (v[FOR days FROM 20 TO 26 INCREMENT 1] = 1)
order by 1

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question
« How to check a string format with the OWA_PATTERN.MATCH() function

How to determine remote dependencies mode? »