Re: Avoiding an inline view using DML only

This is a common problem and a common solution is to have a table with integers from zero to some large number (32,767 is common) as described at http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html Although the SQL statements are specific to MS SQL Server, they can be adapted to other RDBMS.

Here is part of the article:
Generating date ranges

When you need to generate a set of dates in a range, the typical solution is to create a loop and iterate through the range, adding a day each time. However, a numbers table can help us generate this range as a set, instead of treating each date in the range individually. This way, you can use the code directly in a subquery or in a table-valued function, without having to worry about creating a temporary table to hold the values while you iterate through the loop. Received on Wed Apr 23 2008 - 22:00:19 CEST