Many times, in production systems around the world, there comes some need to generate some arbitrary number of rows. If I want 100 numbered rows, I do something like:
SELECT rownum FROM dual CONNECT BY LEVEL <= 100
I might use these to generate a list of 100 dates, by doing:
SELECT TO_DATE('19700101', 'yyyymmdd') + (rownum -1) FROM dual CONNECT BY LEVEL <= 100I often see other tricks employed to generate arbitrary numbers of rows, such as cartesian joining all_objects to itself. I got to wondering whether Oracle had ever considered having some sort of pseudotable that magically generated rows based on the contents of a where clause directed at it,such as:
SELECT integer FROM integers WHERE integer BETWEEN 1 AND 100
--maybe we need dates
SELECT TO_DATE('19700101', 'yyyymmdd') + integer FROM integers WHERE integer BETWEEN 0 AND 99
--maybe a pseudo table could provide dates
SELECT date FROM dates WHERE date BETWEEN SYSDATE - 30 and SYSDATEHow far one goes with providing tables like "hours", "seconds" is a matter of how important it would be to model these things for the developer; a numbers table can provide most/possibly all needs, just by doing a bit of math (such as selecting ints between 0 and 86399 and adding int/86400 to midnight, to get 86400 rows representing every second of the day)
Would there be any benefit (performance/resource) in having such a pseudotable, compared to other established methods (some of which feel like really dirty hacks) of creating arbitrary rows? If there was a consideration to implementing it, was there something that proved insurmountable or unviable in the implementation?
I don't know if there has ever been plans to implement an "integers" pseudo table.
But given how many options exist to do this, I doubt it. For example, you could use XMLTable. This is close to what you're asking for:
select column_value
from xmltable('1 to 5');
Result Sequence
--------------------------------------------------------------------------------
1
2
3
4
5
select column_value
from xmltable('3 to 6');
Result Sequence
--------------------------------------------------------------------------------
3
4
5
6And there are many other methods, such as model, recursive with, etc.. So I don't really see the need for making pseudo tables like this.
And, if you want to join to this kind of data in your production code, it's often better to create a real table. Particularly with dates.
People often use date generators to do things like "find next business day". But "business day" isn't a fixed concept. It varies from country to country and year to year, depending on when public holidays fall and other events. Which can make maintaining date generator SQL tough. Better to have a real table with is_business_day flags. Then you can have an app and let you users define this stuff ;)