From the Archives: Using RowGenerator – part deux
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:
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
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
RECURSIVEkeyword when specifying a recursive query;
- SQL Anywhere servers do not recognize the
- 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_ITERATIONSconnection 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
SELECTlist 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.