RANGE in INTEGER data fields

Database
Teradata Employee

RANGE in INTEGER data fields

hi, i've got this table with the field ampcputime, which is a integer and used the following text in a query

WHEN ampcputime between '0' and '999' then '0'
WHEN ampcputime between '1000' and '1999' then '1'
WHEN ampcputime between '2000' and '2999' then '2'
WHEN ampcputime between '3000' and '3999' then '3'
WHEN ampcputime between '4000' and '4999' then '4'
WHEN ampcputime between '5000' and '5999' then '5'
WHEN ampcputime between '6000' and '6999' then '6'
WHEN ampcputime between '7000' and '7999' then '7'
WHEN ampcputime between '8000' and '8999' then '8'
WHEN ampcputime between '9000' and '9999' then '9'

I'm sure there's a more useful way to divide the data using INTERVAL and RANGE, but i'm not finding any example that don't use partitions.

Can someone point me in the right direction?




4 REPLIES
Enthusiast

Re: RANGE in INTEGER data fields

Not able to understand your requirement. Can you please put in the same format as you put (WHEN.........'9')  and what you want in the same format.

Cheers,

Teradata Employee

Re: RANGE in INTEGER data fields

CASE WHEN ampcputime between '0' and '999' then '0'
WHEN ampcputime between '1000' and '1999' then '1'
WHEN ampcputime between '2000' and '2999' then '2'
WHEN ampcputime between '3000' and '3999' then '3'
WHEN ampcputime between '4000' and '4999' then '4'
WHEN ampcputime between '5000' and '5999' then '5'
WHEN ampcputime between '6000' and '6999' then '6'
WHEN ampcputime between '7000' and '7999' then '7'
WHEN ampcputime between '8000' and '8999' then '8'
WHEN ampcputime between '9000' and '9999' then '9'
ELSE '-1'
END as cpusec

Instead of declaring each interval by myself, i just want to declare WHEN ampcputime between 0 and 999 (only the first), then increase the interval 1000 by 1000

Junior Contributor

Re: RANGE in INTEGER data fields

What's the dataype of ampcputime?

If it's a string then this CASE will not returning the answer you expect as every value will not get past the first WHEN thus returning 0.

And if it's an INT you should compare it to numeric values instead of strings :-)

In your case you could do a simple CAST(ampcputime AS INT) / 1000

For equally sized buckets there's also:

RANGE_N (ampcputime BETWEEN 0 AND 9999 EACH 1000)
or
WIDTH_BUCKET(ampcputime_, 0,10000, 10)

will return values between 1 and 10.

Data outside of the range will be handled differenty:

WIDTH_BUCKET returns 0 for ampcputime < 0 and 11 for >= 10000

RANGE_N returns NULL

Teradata Employee

Re: RANGE in INTEGER data fields

As I stated in the object of the thread, the datatype is INTEGER.

Thank you for the insight of WIDTH_BUCKET and for the RANGE_N solution :)

I wasn't sure about using RANGE_N without the involvement of partitions, but seems like it worked pretty fine

(My solution btw uses EACH 999, since i'm not counting the next thousands in the unit range)

Thank you again