All Forums Database
Ashok.Pentapati 16 posts Joined 06/09
20 Sep 2012
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.

dnoeth 4628 posts Joined 11/04
20 Sep 2012

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

Dieter

Ashok.Pentapati 16 posts Joined 06/09
20 Sep 2012

Thanks a lot Dieter.You are awesome :)
 
Cheers,
Ashok.

TedK 2 posts Joined 12/12
13 Dec 2012

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

dnoeth 4628 posts Joined 11/04
13 Dec 2012

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

Dieter

TedK 2 posts Joined 12/12
13 Dec 2012

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

You must sign in to leave a comment.