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.
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'
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
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)
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
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