Database

turn on suggestions

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

Showing results for

- 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

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?