Range of values in single row to multiple rows

Database
Enthusiast

Range of values in single row to multiple rows

Hi All,

Can someone help me how can we implement below scenario in Teradata?

Input

-----







AGE_CLASS_ID AGE_CLASS_DESC FROM TO
1  0 - 3 0 3
2  1 - 4  4 10

Output

--------






AGE AGE_CLASS_ID AGE_CLASS_DESC
0 1 0 – 3
1 1 0 – 3
2 1 0 – 3
3 1 0 - 3
4 2 4 - 10
5 2 4 - 10
…… ….. ……
10 2 4 - 10

Thanks,

Ashok.

5 REPLIES
Junior Contributor

Re: Range of values in single row to multiple rows

Hi Ashok,

join using between:

select day_of_calendar as AGE, AGE_CLASS_ID, AGE_CLASS_DESC 
from tab
where AGE between fromval and toval

Dieter

Enthusiast

Re: Range of values in single row to multiple rows

Thanks a lot Dieter.You are awesome :)

Cheers,

Ashok.

Fan

Re: Range of values in single row to multiple rows

Dieter,

The above solution works great when the range of data (in the between statment) is less than 73,414, since there are only 73,414 rows in the CALENDAR table. In my case some of the ranges I need to handel have millions of rows. How can I handle this?

Thanks,

Ted

Junior Contributor

Re: Range of values in single row to multiple rows

Hi Ted,

don't do that using millions of rows because it will result in a product join.

What are you trying to achieve?

Dieter

Fan

Re: Range of values in single row to multiple rows

I have a table which have IP ranges assigned to a country. however it is very difficault to join this table with a particular IP address/number. So I wanted to expand the values in this table.

Current range table:

IP_BIGIN_RANGE IP_END_RANGE CNTRY

1  1000000  AB

1000001  7000000  CD

ETC.

I want to get:

IP_NUM IP_BIGIN_RANGE IP_END_RANGE CNTRY

1 1  1000000  AB

2 1  1000000  AB

3 1  1000000  AB

.

.

1000001 1000001  7000000  CD

1000002 1000001  7000000  CD

ETC.

Thank you for your help.

Ted