All Forums Database
pradotera 2 posts Joined 03/10
06 Mar 2015
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 

dnoeth 4628 posts Joined 11/04
07 Mar 2015

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?
 
 

Dieter

pradotera 2 posts Joined 03/10
09 Mar 2015

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

dnoeth 4628 posts Joined 11/04
09 Mar 2015

Hi Prado,
you might create a table UDF returning a sequence of numbers.

Dieter

You must sign in to leave a comment.