Sybase ASE connect by level equivalent

Question

I want to generate numbers from 0 to 9000000. In Oracle, I can use the below code. How do I do this in Sybase ASE?

This is in Oracle:

SELECT level  Num
FROM DUAL
CONNECT BY LEVEL  <= 9000000
ORDER BY Num;

How to do this in Sybase ASE?

I cannot create a table and add an identity, because I need the numbers from 1 to 9000000, so a table will be complex. Is there a query to do this?

Answer 1

In Sybase IQ, there's a system procedure that can be used to generate numbers: sa_rowgenerator

You could have done :

 SELECT row_num FROM sa_rowgenerator( 1, 9000000);

I don't know Sybase ASE at all, so I googled it and have found that this procedure isn't available in ASE, but that an alternative exists :

The SQL Anywhere system procedures sa_rowgenerator, sa_split_list, and sa_conn_info are not supported by ASE. An ASE master database contains a table, spt_values, that can be used to SELECT integer values in a way similar to that of the sa_rowgenerator procedure, or SQL Anywhere’s dbo.row_generator system table.

Source : Migrating SQL Anywhere database applications to ASE

This table spt_values containing integer numbers is incredibly NOT documented. It's like a ghost table.

I suggest that you give this a try :

select number 
FROM master.dbo.spt_values
WHERE number BETWEEN 0 AND 9000000

But I am not responsible if your database system explodes ;-)