SQL to generate a list of numbers from 1 to 100

Question

Using the DUAL table, how can I get a list of numbers from 1 to 100?

Answer 1

I created an Oracle function that returns a table of numbers

CREATE OR REPLACE FUNCTION [schema].FN_TABLE_NUMBERS(
    NUMINI INTEGER,
    NUMFIN INTEGER,
    EXPONENCIAL INTEGER DEFAULT 0
) RETURN TBL_NUMBERS
IS
    NUMEROS TBL_NUMBERS;
    INDICE NUMBER;
BEGIN
    NUMEROS := TBL_NUMBERS();

    FOR I IN (
        WITH TABLA AS (SELECT NUMINI, NUMFIN FROM DUAL)
        SELECT NUMINI NUM FROM TABLA UNION ALL
        SELECT 
            (SELECT NUMINI FROM TABLA) + (LEVEL*TO_NUMBER('1E'||TO_CHAR(EXPONENCIAL))) NUM
        FROM DUAL
        CONNECT BY 
            (LEVEL*TO_NUMBER('1E'||TO_CHAR(EXPONENCIAL))) <= (SELECT NUMFIN-NUMINI FROM TABLA)
    ) LOOP
        NUMEROS.EXTEND;
        INDICE := NUMEROS.COUNT; 
        NUMEROS(INDICE):= i.NUM;
    END LOOP;

    RETURN NUMEROS;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
       RETURN NUMEROS;
  WHEN OTHERS THEN
       RETURN NUMEROS;
END;
/

Is necessary create a new data type:

CREATE OR REPLACE TYPE [schema]."TBL_NUMBERS" IS TABLE OF NUMBER;
/

Usage:

SELECT COLUMN_VALUE NUM FROM TABLE([schema].FN_TABLE_NUMBERS(1,10))--integers difference: 1;2;.......;10

And if you need decimals between numbers by exponencial notation:

SELECT COLUMN_VALUE NUM FROM TABLE([schema].FN_TABLE_NUMBERS(1,10,-1));--with 0.1 difference: 1;1.1;1.2;.......;10
SELECT COLUMN_VALUE NUM FROM TABLE([schema].FN_TABLE_NUMBERS(1,10,-2));--with 0.01 difference: 1;1.01;1.02;.......;10

Answer 2

Here is a fun way to generate a number table. It doesn't use the DUAL table, but should the DUAL table ever disappear then this could be a back-up plan.

DECLARE @TotalNumbers INT = 100;

DECLARE @From DATETIME = CONVERT(DATETIME, CONVERT(DATE, GETDATE())),
        @To DATETIME = DATEADD(SECOND, @TotalNumbers - 1, CONVERT(DATETIME, CONVERT(DATE, GETDATE())));

WITH AlmostNumberTable (Hola)
AS (SELECT @From
    UNION ALL
    SELECT DATEADD(SECOND, 1, Hola)
    FROM AlmostNumberTable
    WHERE Hola< @To
   )
SELECT [Number]
FROM
(
    SELECT DATEPART(MINUTE, AlmostNumberTable.Hola) * 60 + DATEPART(SECOND, AlmostNumberTable.Hola) + 1 AS [Number]
    FROM AlmostNumberTable
) AS NumberTable;

It's probably nonsense, but it is a working solution and it was fun to write.

Answer 3

Using Oracle's sub query factory clause: "WITH", you can select numbers from 1 to 100:

WITH t(n) AS (
  SELECT 1 from dual
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT * FROM t;

Answer 4

SELECT * FROM `DUAL` WHERE id>0 AND id<101

The above query is written in SQL in the database.

Answer 5

You could use XMLTABLE:

SELECT rownum
FROM XMLTABLE('1 to 100');

DBFiddle Demo

Answer 6

Do it the hard way. Use the awesome MODEL clause:

SELECT V
FROM DUAL
MODEL DIMENSION BY (0 R)
      MEASURES (0 V)
      RULES ITERATE (100) (
        V[ITERATION_NUMBER] = ITERATION_NUMBER + 1
      )
ORDER BY 1

Proof: http://sqlfiddle.com/#!4/d41d8/20837