Question Drew Holloway · Feb 28, 2020 How to get a Numbers View #SQL #Caché 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 231
Drew Holloway · Feb 28, 2020 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 NumFROM Numbers AS N1 CROSS JOIN Numbers AS N2This gives data up to 9999. I can easily add more rows if needed, but this should suffice for now. Vitaliy Serdtsev · Mar 3, 2020 Defining Stored Procedures Source code Class dc.test [ Abstract ] { Query numbers(count As %Integer = 4) As %Query(ROWSPEC = "n:%Integer") [ SqlName = numbers, SqlProc ] { } ClassMethod numbersExecute( ByRef qHandle As %Binary, count As %Integer = 4) As %Status { s qHandle=$lb(0,count) q $$$OK } ClassMethod numbersFetch( ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = numbersExecute ] { s n=$li(qHandle,1)+1 i n>$li(qHandle,2) { s AtEnd=1 } else { s Row=$lb(n) s $li(qHandle,1)=n } q $$$OK } ClassMethod numbersClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = numbersExecute ] { q $$$OK } } Result: select * from dc.numbers(107) n 1 2 ... 105 106 107 Drew Holloway · Mar 3, 2020 I'm just using SQL to get this result. I don't have a way to use other code.
Vitaliy Serdtsev · Mar 3, 2020 Defining Stored Procedures Source code Class dc.test [ Abstract ] { Query numbers(count As %Integer = 4) As %Query(ROWSPEC = "n:%Integer") [ SqlName = numbers, SqlProc ] { } ClassMethod numbersExecute( ByRef qHandle As %Binary, count As %Integer = 4) As %Status { s qHandle=$lb(0,count) q $$$OK } ClassMethod numbersFetch( ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = numbersExecute ] { s n=$li(qHandle,1)+1 i n>$li(qHandle,2) { s AtEnd=1 } else { s Row=$lb(n) s $li(qHandle,1)=n } q $$$OK } ClassMethod numbersClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = numbersExecute ] { q $$$OK } } Result: select * from dc.numbers(107) n 1 2 ... 105 106 107 Drew Holloway · Mar 3, 2020 I'm just using SQL to get this result. I don't have a way to use other code.
Drew Holloway · Mar 3, 2020 I'm just using SQL to get this result. I don't have a way to use other code.
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
I'm just using SQL to get this result. I don't have a way to use other code.