CODEWITHSUNDEEP
sqlteradata
Daulat Daga
Daulat Daga

Reputation: 13

No more spool space in DUKESQLMOOC10203

select deptinfo.dept, deptinfo.deptdesc, skuinfo.brand, skuinfo.style, skuinfo.color, max(total) from
(select deptinfo.dept, deptinfo.deptdesc, skuinfo.brand, skuinfo.style, skuinfo.color, sum(trnsact.amt) as total
from deptinfo
inner join skuinfo
on deptinfo.dept = skuinfo.dept
inner join trnsact
on skuinfo.sku = trnsact.sku
where stype = 'R'
group by 1,2,3,4,5) as a
group by 1,2,3,4,5
order by 6 desc;

I get this error:

No more spool space in DUKESQLMOOC10203

Upvotes: 0

Views: 453

Answers (1)

JNevill
JNevill

Reputation: 50209

As others have pointed out, your query doesn't make sense as written. You are generating an intermediate result set/subquery that holds the sum(trnsact.amt) for each distinct combination of deptinfo.dept, deptinfo.deptdesc, skuinfo.brand, skuinfo.style, and skuinfo.color.

This is all good so far. If you want to get the department that has the largest sum(trnsact.amt) you can order your results of that subquery with an ORDER BY total and select only one record with SELECT TOP 1 at the beginning of that query:

SELECT TOP 1
    deptinfo.dept,
    deptinfo.deptdesc,
    skuinfo.brand,
    skuinfo.style,
    skuinfo.color,
    sum(trnsact.amt) AS total
FROM deptinfo
    INNER JOIN skuinfo ON deptinfo.dept = skuinfo.dept
    INNER JOIN trnsact ON skuinfo.sku = trnsact.sku
WHERE stype = 'R'
GROUP BY 1,2,3,4,5
ORDER BY total DESC;

As an Alternative to the ORDER BY Total DESC you can use a QUALIFY statement. This is useful if you have multiple "TOP" records that have the same sum(trnsact.amt). That first query may return a different result each time since there is no way to say "If there are multiple department/brand/style/color combos that have the same sum(trnsact.amt) then choose the record with the highest deptinfo.dept number". Using a Qualify to do that would look like:

SELECT 
    deptinfo.dept,
    deptinfo.deptdesc,
    skuinfo.brand,
    skuinfo.style,
    skuinfo.color,
    sum(trnsact.amt) AS total
FROM deptinfo
    INNER JOIN skuinfo ON deptinfo.dept = skuinfo.dept
    INNER JOIN trnsact ON skuinfo.sku = trnsact.sku
WHERE stype = 'R'
GROUP BY 1,2,3,4,5
QUALIFY ROW_NUMBER() OVER (ORDER BY total DESC, dept DESC) = 1;

The most likely reason you are running out of spool space in your query is because your first SELECT is referencing table names, but those table names are only in context in the subquery. By naming the tables AGAIN in the first SELECT you are causing a Cross Join, essentially joining every record from deptinfo with every record in skuinfo with every record in trnsact with every record from your subquery which is going to be huge, even if your tables are small. It's also not what you are looking for.

When you use a subquery, then your outer SELECT, WHERE, ORDER BY, GROUP BY, and QUALIFY reference the Alias that you gave the subquery like the following (Use one of the two queries above for your results, this is just an example of how to use a subquery):

select a.dept, a.deptdesc, a.brand, a.style, a.color, max(total) from
(select deptinfo.dept, deptinfo.deptdesc, skuinfo.brand, skuinfo.style, skuinfo.color, sum(trnsact.amt) as total
from deptinfo
inner join skuinfo
on deptinfo.dept = skuinfo.dept
inner join trnsact
on skuinfo.sku = trnsact.sku
where stype = 'R'
group by 1,2,3,4,5) as a
group by 1,2,3,4,5
order by 6 desc;

Like I noted above, this still won't get you the results you want, but at least you can run it now and see why the results don't match up with your expectations.

Upvotes: 1

Related Questions