Single query to return a sequence or range of integers
13030Dec 9 2007 — edited Dec 10 2007Here is a brain teaser for all you SQL pros...
Anyone know a way to write a single query to retrieve a range of integers? i.e. something like:
select * from some_dummy_table where n between 101 and 103
would return:
n
-----
101
102
103
the point being that I need one row for each integer in the sequence.
Of course, I could write a plsql function to return an array, and then cast that array to a table, but I could have sworn I have seen a solution like this somewhere using analytics, i.e. just SQL and not PLSQL
I could also do something with rownum and selecting from dba_objects, but I don't want this query to be dependent on how many objects are in the system or how many rows are in a given table.