Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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...

share|improve this question
    
Check this link: hashfactor.wordpress.com/2009/04/08/… –  dezso Dec 20 '12 at 10:08
    
thanks, let me check it out & see... –  fa1c0n3r Dec 20 '12 at 10:19
3  
You might also consider keeping a permanent table of positive integers in the database. It comes in handy for all sorts of things. –  Paul White Dec 20 '12 at 10:27
    
interesting idea... would definitely work, though I think I preferred Vincent's answer for this one, thanks. –  fa1c0n3r Dec 21 '12 at 2:31
1  
@LeighRiffel Thank you –  Paul White Dec 25 '12 at 3:23

1 Answer 1

up vote 5 down vote accepted

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
share|improve this answer
    
Thanks, the CONNECT BY was exactly what I was trying to find. –  fa1c0n3r Dec 21 '12 at 2:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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