2

I would like to generate a named table in a WITH clause which will contain this arithmetic progression:

╔══════════╗
║ mynumber ║
╠══════════╣
║        0 ║
║       30 ║
║       60 ║
║       90 ║
║      120 ║
║      150 ║
║      180 ║
║      210 ║
╚══════════╝

...based on variable p_maxnumber (here p_maxnumber = 211 for example).

Ideally it would be in a format like:

WITH sequenceTable AS
(SELECT mynumber
FROM ...),

Within the same WITH clause, I would like to use that sequence in downstream derived tables to get data and to be eventually used as a time axis for chart rendering. I have a feeling there is a way to do it using create sequence? Just trying to figure a way to create this still...

3
  • Check this link: hashfactor.wordpress.com/2009/04/08/…
    – dezso
    Dec 20, 2012 at 10:08
  • interesting idea... would definitely work, though I think I preferred Vincent's answer for this one, thanks.
    – fa1c0n3r
    Dec 21, 2012 at 2:31
  • @SQL Kiwi FYI, this is almost never useful in Oracle. A CONNECT BY query as shown below can generate rows faster and with less overhead. Such a table may be useful on other databases and can be useful in Oracle in unusual scenarios. Dec 24, 2012 at 20:41

1 Answer 1

5

You can use a CONNECT BY query to generate an arbitrary sequence:

SQL> variable v_step NUMBER
SQL> variable v_maxnumber NUMBER
SQL> exec :v_step := 30; :v_maxnumber := 211;

PL/SQL procedure successfully completed

SQL> WITH sequenceTable AS (
  2     SELECT (LEVEL - 1) * :v_step myNumber
  3       FROM DUAL
  4     CONNECT BY (LEVEL - 1) * :v_step <= :v_maxnumber)
  5  SELECT * FROM sequenceTable;

  MYNUMBER
----------
         0
        30
        60
        90
       120
       150
       180
       210

8 rows selected

You can also use a pipelined function:

SQL> CREATE OR REPLACE TYPE tab_number IS TABLE OF NUMBER;
  2  /

Type created

SQL> CREATE OR REPLACE FUNCTION arithmetic_sequence (p_step NUMBER, p_maxvalue NUMBER)
  2     RETURN tab_number
  3     PIPELINED
  4  IS
  5  BEGIN
  6     FOR i IN 0..p_maxvalue/p_step LOOP
  7        PIPE ROW (i*p_step);
  8     END LOOP;
  9     RETURN;
 10  END;
 11  /

Function created

SQL> SELECT COLUMN_VALUE myNumber FROM TABLE(arithmetic_sequence(30, 211));

  MYNUMBER
----------
         0
        30
        60
        90
       120
       150
       180
       210

8 rows selected
1
  • Thanks, the CONNECT BY was exactly what I was trying to find.
    – fa1c0n3r
    Dec 21, 2012 at 2:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.