Question
Drew Holloway · Feb 28, 2020

How to get a Numbers View

In T-SQL, I can create a recursive CTE to create a subquery or a view that will hold a whole lot of numbers.  Is there a way to do this in Cache SQL?  I'd like the numbers from 0 to 10000 if possible. 

Thanks!

0
0 209
Discussion (3)1
Log in or sign up to continue

I created a Numbers table with a Numbers column.  I manually entered data from 0 to 99.  I was able to leverage this to give me my result...

SELECT N1."Number" * 100 + N2."Number" AS Num
FROM Numbers AS N1 
CROSS JOIN Numbers AS N2

This gives data up to 9999.  I can easily add more rows if needed, but this should suffice for now.  

Defining Stored Procedures

 
Source code
Result:
select * from dc.numbers(107)
n
1
2
...
105
106
107

I'm just using SQL to get this result.  I don't have a way to use other code.