From the Archives: Using RowGenerator - part deux

[JasonHinsperger]

In this post, originally written by Glenn Paulley and posted to sybase.com 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 (http://scn.sap.com/community/sql-anywhere/blog/2014/06/25/using-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 1 FROM DUMMY

    UNION ALL

  SELECT counter + 1 FROM foo WHERE counter < 10 )

SELECT * FROM foo

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.