Analog of “select level from dual connect by level

Question

By select level from dual connect by level < 10 in Oracle I can generate sub-query of integer and transform it to another sequence.

Is this possible with Sybase?

PS I want to find dates where are no data in table (missing days). In Oracle I do this as:

select to_date('2012-01-01', 'yyyy-mm-dd')+level-1 from dual
    connect by level < to_date('2013-01-01', 'yyyy-mm-dd') - to_date('2012-01-01', 'yyyy-mm-dd')
  MINUS
select distinct date from TBL
    where date between to_date('2012-01-01', 'yyyy-mm-dd')
                   and to_date('2013-01-01', 'yyyy-mm-dd')

In Sybase analog for MINUS is:

select whatever from table1 T1
  where not exists
  ( select 1 from table2 where id = T1.id )

but I don't know analog for connect by level...

UPDATE Is there any way to create temporary table in Sybase without actually storing data to disk?

Answer 1

The Oracle syntax you use is a hierarchical query used as a row generator. Sybase has the sa_rowgenerator system procedure can be used to generate a set of integers between a start and end value.

The following will produce a column of integers between 1 and 255.

SELECT row_num FROM sa_rowgenerator( 1, 255 );

Answer 2

sa_rowgenerator system procedure required DBA authority . Users without DBA authority must be granted EXECUTE permission to run the stored procedure.