Search code examples
sqlsybasesap-ase

Sybase ASE connect by level equivalent

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?

Solution

  • 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 ;-)