How to create records from a defined range

Database
N/A

How to create records from a defined range

Hi All

I've got a problem to resolve. Table contains an ID and two columns(lo,hi) defining range of numbers. I need output(table) that will contain as many records as are defined in a range for each ID.

Example table has 3 records

ID LO HI
1 10 12
2 1 5
3 27 30

I need such output:

ID NUMBERS
1 10
1 11
1 12
2 1
2 2
2 3
2 4
2 5
3 27
3 28
3 29
3 30

Thans for help
kostek
Tags (1)
7 REPLIES

Re: How to create records from a defined range

For example:

BTEQ -- Enter your DBC/SQL request or BTEQ command:
create table my_db.prueba20 (id_n integer not null,
hi_n integer not null,
low_n integer not null)
unique primary index (id_n);

*** Table has been created.
*** Total elapsed time was 1 second.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
insert into my_db.prueba20 (id_n, hi_n, low_n ) values (1, 10,12);

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
insert into my_db.prueba20 (id_n, hi_n, low_n ) values (2, 1, 5);

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
insert into my_db.prueba20 (id_n, hi_n, low_n ) values (3, 27, 30);

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

select * from my_db.prueba20 order by 1;

*** Query completed. 3 rows found. 3 columns returned.
*** Total elapsed time was 1 second.

id_n hi_n low_n
----------- ----------- -----------
1 10 12
2 1 5
3 27 30

select a.id_n,
b.day_of_calendar
from my_db.prueba20 a,
sys_calendar.calendar b
where b.day_of_calendar between a.hi_n and a.low_n
order by 1, 2;

*** Query completed. 12 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

id_n day_of_calendar
----------- ---------------
1 10
1 11
1 12
2 1
2 2
2 3
2 4
2 5
3 27
3 28
3 29
3 30

HTH.

Cheers.

Carlos.
N/A

Re: How to create records from a defined range

Thanks for reply Carlos :-)

Very clever solution, but there is one big barrier which causes that I can't use it.
Calendar contains days from years 1900-2100 so max. value of day_of_calendar is 73414.
Numbers in my table(hi and lo) can be much bigger e.g 12000000-12000020

Currently I've got a solution using procedure but I'm still waiting for other ideas.

Re: How to create records from a defined range

This happens when you don't provide enough information.

You can always do:

select * from my_db.prueba20;

*** Query completed. One row found. 3 columns returned.
*** Total elapsed time was 1 second.

id_n low_n hi_n
----------- ----------- -----------
1 12000000 12000020

select a.id_n,
b.day_of_calendar - 1 + a.low_n
from my_db.prueba20 a,
sys_calendar.calendar b
where (b.day_of_calendar - 1 + a.low_n) between a.low_n and a.hi_n
order by 1, 2;

*** Query completed. 21 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

id_n ((day_of_calendar-1)+low_n)
----------- ---------------------------
1 12000000
1 12000001
1 12000002
1 12000003
1 12000004
1 12000005
1 12000006
1 12000007
1 12000008
1 12000009
1 12000010
1 12000011
1 12000012
1 12000013
1 12000014
1 12000015
1 12000016
1 12000017
1 12000018
1 12000019
1 12000020

Cheers.

Carlos.
N/A

Re: How to create records from a defined range

Carlos thanks a lot.

I've got one more question. Joining with calendar causes product join. Because my main table has thousands records the spool table(joining with calendar) has over than 100 million records and the query didn't finished even in 30 minutes. Do You have any ideas how to speed up this?

thanks and waiting for answer
N/A

Re: How to create records from a defined range

The only way to get rid of the cross join would be a table UDF creating those ranges "on the fly", but then there's still that huge spool.

If the range is usually small you could calculate the maximum range to restrict the number of rows selected from calendar.

Untested, the DISTINCT forces the optimizer to materialize "b":

select a.id_n,
b.day_of_calendar - 1 + a.low_n
from my_db.prueba20 a,
(select distinct day_of_calendar from sys_calendar.calendar
where day_of_calendar <= (select max(hi_n - low_n) from my_db.prueba20)) b
where (b.day_of_calendar - 1 + a.low_n) between a.low_n and a.hi_n
order by 1, 2;

Dieter
N/A

Re: How to create records from a defined range

Hi Carlos,Dieter -

this solution helped me immensely in my problem.

however, I have one question - how would you deal with two dimensional data e.g. instead of

id_n hi_n low_n

----------- ----------- -----------

1 10 12

2 1 5

3 27 30

I have

table 1 -

low_in  hi_n

0          500

table 2 -

low_in  hi_n

0          1500

table 3 - output

low_n      hi_n

0             0

0             1

0              2

.             .

.             .

0            1500

1             0

1              1

.               .

.               .

1             1500

.                .

.                .

500           1500

N/A

Re: How to create records from a defined range

Simply use two Derived Tables producing the required numbers and then cross join them.

Dieter