Generating sequence numbers for given range values

Database

Generating sequence numbers for given range values

Hi,

I have a requirement to generate sequence of numbers for a given range (like below) and i'm using recursive query and it works for small volume. As the volume keeps increasing query is spooling out. Please help me with a solution.

Eg:

If I have the range as given below for 1 record and I will have ~ 1 million records each day

    STARTID                 ENDID

1192014848      1192014975

I need to generate as below

SEQID                      STARTID                                 ENDID

1192014848     1192014848                        1192014975

1192014849     1192014848                        1192014975

1192014850     1192014848                        1192014975

1192014851      1192014848                       1192014975

 ..........................................................

.....................................................................

1192014973     1192014848                        1192014975

1192014974     1192014848                        1192014975

1192014975      1192014848                       1192014975

Currently I'm using recursive query and works for small volume of records.

WITH

RECURSIVE nums(n,startipnum,endipnum) AS (

SELECT startipnum AS n,startipnum,endipnum FROM DB.TBL

union all

SELECT n +1,startipnum,endipnum FROM nums

WHERE

n< endipnum

)

SELECT

n,startipnum,endipnum FROM nums;

Regards,

Prado 

3 REPLIES
Junior Contributor

Re: Generating sequence numbers for given range values

Hi Prado,

seems like you want to expand blocks of ip-addresses.

If your query runs out of spool it's probably not because of the recursion but due to the number of rows returned.

Also recursion will be quite slow as the biggest networks in IPv4 got 16.000.000 addresses.

Of course you could abuse EXPAND ON (this will work up to a 60 million range):

SELECT 
CAST(ip_block_start + (EXTRACT(SECOND FROM BEGIN(pd)) * 1000000) AS DECIMAL(10,0)),
ip_block_start,
ip_block_end
FROM
(
SELECT ip_block_start, ip_block_end, pd
FROM ip_blocks
WHERE ip_block_start IN ( 3177586048, 1192014848)
EXPAND ON PERIOD (TIMESTAMP '2000-01-01 00:00:00.000000', TIMESTAMP '2000-01-01 00:00:00.000000' + (ip_block_end - ip_block_start + 1) * INTERVAL '0000.000001' SECOND) AS pd
) AS dt

But why do you need to do this, if it's IPv4 you'll create up to 4 billion numbers?

Re: Generating sequence numbers for given range values

Thanks Dnoeth.

I'm not sure about the usage. The project team has asked me to provide a solution on teradata which is equivalent to "generate_sequence" function in Green plum.

Regards,

Prado

Junior Contributor

Re: Generating sequence numbers for given range values

Hi Prado,

you might create a table UDF returning a sequence of numbers.