rank csum quantile - spool space

[dupa12345]

Hi all,
I'm trying to get deciles for a large file about 20-30 million recs. I'm running out of spool space on the sort part. Is there any way i could improve my sorts?

This is what i do. first, i get sums of sales per client. then, i assign deciles to the sales. finaly, i compute max, min, avg per decile and count of clients in deciles

for step 2 i tried
select rank(sales), sales -- don't like it because clients are not distributed evenly in deciles
select csum(1,sales), sales -- works
quantile(10,sales) , sales -- same problem as with rank, but cuts down my sql code in half

the problem is that all of these fail when i attempt to run it on a larger nbr of recs.

i can't really split the query becuase it will affect the deciles. i'm looking for a way to run it in smaller pieces or for a better way to sort such that i won't run out of spool. any ideas?
thx.

 

[dupa12345]

i found out what the problem is - but i still don't have a solution.
looks like the sort is getting hang up on a lot of 0's. in one case i have about 100k rows of sales = 0. if i eliminate these rows, query runs fine, but if it's just the zero's, it freaks out. i tried nullifzero() but it dislikes sorting on NULL as much as on 0's. i guess it's just the fact that i have so many rows with the same value, that kills tera's sort logic.