How To Generate A List Of Number In Sql As It Was A List Of Comprehension?

In my case, using sql anywhere (sybase). Something similar to haskell. [1..100]. I don't know how to generate a random simple list of 1 up to 100. I only could do it doing: select

Solution 1:

SQL Anywhere contains an sa_rowgenerator stored procedure, which can be used for this purpose. For example:

select row_num fromsa_rowgenerator(1, 100)

returns a result set of 100 rows from 1 to 100 inclusive. A link to the documentation (for version 12.0.1) is here.

Disclaimer: I work for SAP/Sybase in the SQL Anywhere engineering.

Solution 2:

I find the easiest way for a short list is something like:

select t.*
from (selectrow_number() over (order by (select NULL)) as num
      from Information_Schema.columns
     ) t
where num <= 100

The columns table usually has at least 100 rows. Other tables can also be used.

For large numbers, something like the following:

with digits as (
      select0as dig unionallselect1unionallselect2unionallselect3unionallselect4unionallselect5unionallselect6unionallselect7unionallselect8unionallselect9
),
     numbers asselect d1.dig*10+d2.dig
      from digits d1 crossjoin
           digits d2
)
 . . .

Solution 3:

Oracle queries - use any number tostart/end:

 SELECT99+ ROWNUM
   FROM dual       
 CONNECTBY ROWNUM <=100+1/SELECT99+ LEVEL
  FROM dual       
CONNECTBY LEVEL <=100+1/