In this post, originally written by Glenn Paulley and posted to in October of 2009, Glenn talks about how one can simulate the behaviour of the RowGenerator table and the sa_rowgenerator(…) function using standard SQL.

After posting my recent article on the use of the RowGenerator system table, I received a welcome email from Jan-Eike Michels of IBM who, like me, sits on the DM32.2 committee for INCITSas the IBM representative for the SQL Standard:

Hi Glenn,

Just stumbled across your blog about the RowGenerator ( . I don’t know whether iAnywhere supports the WITH clause but (since the standard does) you could use that one as well (similar to your sa_rowgenerator procedure):

with dummy (counter) as
    (select counter from table(values (1)) as x(counter) union all
    select counter + 1 from dummy where counter < 1000 ) select counter from dummy

would return 1000 rows.

I welcomed Jan-Eike’s contribution because, as he quite rightly points out, it is straightforward to generate a set of identifiers recursively using the SQL standard’s common table expression syntax, in this case using the recursive UNION construction.

One can use Jan-Eike’s example almost verbatim in SQL Anywhere. The issues with Jan-Eike’s SQL query are:

  • In SQL Anywhere, one must include the RECURSIVE keyword when specifying a recursive query;
  • SQL Anywhere servers do not recognize the TABLE keyword; and
  • SQL Anywhere already contains a (real) table, DUMMY, that generates a single-row, single-column result set.

So here is a version of Jan-Eike’s example that generates the values between 1 and 10 in SQL Anywhere:

WITH RECURSIVE foo(counter) AS
  SELECT counter + 1 FROM foo WHERE counter < 10 )

that defines the common table expression “foo” (instead of “dummy”) and generates the specified values. The graphical plan for this query is as follows:


Some points to mention:

  • Specifying a larger number of values – and hence a deeper level of recursion – may require setting the MAX_RECURSIVE_ITERATIONS connection option to a higher value.
  • Jan-Eike’s example generates  a sequential set of values, equivalent to what the RowGenerator system table or the sa_rowgenerator() system procedure generates. However, one could modify this query to generate a non-contiguous sequence of any values desired, simply by rewriting the SELECT list expressions in the common table expression.
  • Finally, while this recursive version does have utility, the RowGenerator system table may still be a better approach. The advantage of RowGenerator is that it is a (static) base table; hence the query optimizer is much better able to estimate the cardinality of intermediate results when RowGenerator is used within a complex query than when a common table expression is used.
To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply