Written by

Question Drew Holloway · Feb 28, 2020

How to get a Numbers View

#Caché #SQL

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!

Discussion (3)0

Add reply

Comments

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 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.

0 0

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

0 0

Drew Holloway Mar 3, 2020 to Vitaliy Serdtsev

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

0 0