Sybase ASE connects by level equivalent

Question

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

This is in Oracle:

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

How to do it in Sybase ASE?

I cannot create a table and add an identifier because I need numbers from 1 to 9,000,000, so the table will be complicated. Is there a request for this?

Answer 1

Sybase IQ has a system procedure that can be used to generate numbers: sa_rowgenerator

You could do:

 SELECT row_num FROM sa_rowgenerator( 1, 9000000);

I don’t know Sybase ASE at all, so I looked for it and found that this procedure is not available in ASE, but there is an alternative:

SQL Anywhere: sa_rowgenerator, sa_split_list sa_conn_info ASE. ASE master , spt_values, SELECT sa_rowgenerator SQL Anywheres dbo.row_generator.

: SQL Anywhere ASE

spt_values, , . .

:

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

, ; -)

Answer 2

, ( ). , , , :

select * from 
(select (t6.i*1000000 + t5.i*100000 + t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_value from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t5,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t6) v
where selected_value between 1 and 200 --here you can change the interval ex.: between 201 and 1000
order by selected_value

, 99999, t6 t5 ( ). , 5 .

Answer 3

, / 9 (+1) ?

- , , 9 .

---

(. RMH), :

• -, , tempdb
• , ( tempdb)

"" , ( 1 10) ( 1 9 000 000).

, , dataserver (, /, 9 000 000 ... tempdb, ... egad... / !).

---

- ...

declare @counter bigint, @max bigint
select @counter=0, @max=9000000
while @counter <= @max
begin
    select @counter
    select @counter=@counter+1
end

... tempdb, - , 9 . 9 1 , , , , , dataserver .

, , , , , - proc; , , , .

', , dataserver ( ) .

---

There may be some other ways in which Sybase / ASE can generate a series of numbers (for example, create / populate a table with an identity column), but this will require either a) a good bit of dataserver resources, or b) some convoluted code (for example, functions application context, plug-in Java code) ... to do something that is more efficiently handled by the client / front-end application.