sa_rowgenerator system procedure

Returns a result set with rows between a specified start and end value.

Syntax
sa_rowgenerator(
 [ rstart
 [, rend
 [, rstep ] ] ]
)
Arguments
  • rstart   Use this optional INTEGER parameter to specify the starting value. The default value is 0.

  • rend   Use this optional INTEGER parameter to specify the ending value. The default value is 100.

  • rstep   Use this optional INTEGER parameter to specify the increment by which the sequence values are increased. The default value is 1.

Result set
Column name Data type Description
row_num INTEGER Sequence number.
Remarks

The sa_rowgenerator procedure can be used in the FROM clause of a query to generate a sequence of numbers. This procedure is an alternative to using the RowGenerator system table. You can use sa_rowgenerator for such tasks as:

  • generating test data for a known number of rows in a result set.
  • generating a result set with rows for values in every range. For example, you can generate a row for every day of the month, or you can generate ranges of zip codes.
  • generating a query that has a specified number of rows in the result set. This may be useful for testing the performance of queries.

You can emulate the behavior of the RowGenerator table with the following statement:

SELECT row_num FROM sa_rowgenerator( 1, 255 );
Permissions

None

Side effects

None

See also
Example

The following query returns a result set containing one row for each day of the current month.

SELECT DATEADD( day, row_num-1,
        YMD( DATEPART( year, CURRENT DATE ),
            DATEPART( month, CURRENT DATE ), 1 ) ) 
    AS day_of_month
    FROM sa_rowgenerator( 1, 31, 1 )
    WHERE DATEPART( month, day_of_month ) = 
       DATEPART( month, CURRENT DATE )
    ORDER BY row_num;

The following query shows how many employees live in zip code ranges (0-9999), (10000-19999), ..., (90000-99999). Some of these ranges have no employees, which causes the warning Null value eliminated in aggregate function (-109). The sa_rowgenerator procedure can be used to generate these ranges, even though no employees have a zip code in the range.

SELECT row_num AS r1, row_num+9999
 AS r2, COUNT( PostalCode ) AS zips_in_range
FROM sa_rowgenerator( 0, 99999, 10000 ) D LEFT JOIN Employees
   ON PostalCode BETWEEN r1 AND r2
GROUP BY r1, r2
ORDER BY 1;

The following example generates 10 rows of data and inserts them into the NewEmployees table:

INSERT INTO NewEmployees ( ID, Salary, Name )
SELECT row_num, 
   CAST( RAND() * 1000 AS INTEGER ),
   'Mary'
FROM sa_rowgenerator( 1, 10 );

The following example uses the sa_rowgenerator system procedure to create a view containing all integers. The value 2147483647 in this example represents the maximum signed integer supported in SQL Anywhere.

CREATE VIEW Integers AS
SELECT row_num AS n
FROM sa_rowgenerator( 0, 2147483647, 1 );

This example uses the sa_rowgenerator system procedure to create a view containing dates from 0001-01-01 to 9999-12-31. The value 3652058 in this example represents the number of days between 0001-01-01 and 9999-12-31, the earliest and latest dates supported in SQL Anywhere.

CREATE VIEW Dates AS
SELECT DATEADD( day, row_num, '0001-01-01' ) AS d
FROM sa_rowgenerator( 0, 3652058, 1 );