Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-27-2014
01:46 AM

4 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-27-2014
04:06 AM

01-27-2014
04:06 AM

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,

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-27-2014
05:38 AM

01-27-2014
05:38 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-27-2014
10:40 AM

01-27-2014
10:40 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-29-2014
11:47 PM

01-29-2014
11:47 PM

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

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.

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

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?