5

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?

SWalters
  • 3,615
  • 5
  • 30
  • 37
Moudiz
  • 7,211
  • 22
  • 78
  • 156

3 Answers3

4

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

Thomas G
  • 9,886
  • 7
  • 28
  • 41
1

This could work for you, but it takes a little bit of time (but you can do by parts). Sorry, but I don't know the best way to do this, just the bellow:

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

For example, If you need numbers until 99999, so you don't need the t6 and t5 (just to try to explain better). I ran here, and take more than a minute to finish until 5 millions.

RMH
  • 222
  • 1
  • 11
1

Could you provide some detail as to what/how you plan to use those 9 million (+1) numbers?

If the sole purpose is to send a stream of numbers back to the client, it seems to me that it'd be much more efficient to just have the client application generate the 9m numbers.

There are many ways to generate a sequence of numbers using a single query based on the cartesian product of a handful of smaller tables (see RMH's base-10 example), but all of these solutions require:

  • first, the entire cartesian product to be built in tempdb
  • then, the entire cartesian product to be sorted (in memory and tempdb)
  • and only then are the desired numbers sent back to the client

This overhead is required of the same 'generator' query regardless of whether you want to generate a small set of numbers (1 to 10) or a large set of numbers (1 to 9,000,000).

Obviously a better, more efficient, lightweight method needs to be used to keep from hogging dataserver resources (eg, imaagine several users/applications trying to generate 9,000,000 numbers ... all using large volumes of tempdb space as well as cpu resources ... egad ... expect an angry email/phone-call from the DBA!).

Another idea would be use a looping construct to generate the desired numbers ...

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

... and while this will eliminate the tempdb overhead, you're still going to use up a moderate amount of cpu resources looping 9 million times. An additional performance hit could come from the overhead of generating 9 million 1-row result sets, in particular you'll likely see an excessive number of network packets flowing between the dataserver and the client application.

While we could reduce the volume of network packets by making the final result look like a single result set, this would require turning the above looping construct into a stored procedure, making sure you have a loopback server defined, and then creating a proxy table to reference said proc; you would then query the proxy table to obtain what looks like a single result set containing the desired set of numbers.

'course, at this point we've now had to jump through a few hoops just to have the dataserver generate (in a somewhat efficient method) a series of numbers as a single result set.

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

markp-fuso
  • 28,790
  • 4
  • 16
  • 36