Skip to content
Advertisement

No more spool space in Database (Teradata)

I am a beginner in SQL and I have a problem with one of my queries on Teradata. When I run it, I get this error:

“No more spool space in Database”.

According to my research on the Internet, the problem would come from the fact that my query is not optimized enough, except that I don’t see how I could simplify it more than that. Could you please help me?

Query where the error comes from:

INSERT INTO DESTINATION_TABLE
(YEAR,
QUARTER,
LABEL,
NUMBER,
TIMESTAMP)
VALUES
((SELECT YEAR FROM DATE TABLE),
(SELECT QUARTER DATE TABLE),
'ANY LABEL',
(SELECT COUNT(*)
FROM DATE_TABLE AS TMP
FULL OUTER JOIN TABLE_1 AS T1 ON TMP.PRG_DT = T1.INN_DT
FULL OUTER JOIN TABLE_2 AS T2 ON TMP.PRG_DT = T2.INN_DT
INNER JOIN TABLE_3 AS T3 ON TMP.PRG_DT = T3.INN_DT),
CURRENT_TIMESTAMP);

What the query is supposed to do:

Year processed = year of DATE_TABLE
Quarter processed = quarter of DATE_TABLE
Label = ‘ANY LABEL’
Number = sum of the number of occurrences of TABLE_1 + number of occurrences of TABLE_2 + number of occurrences of TABLE_3 having for INN_DT the PRG_DT of DATE_TABLE
Timestamp = Current timestamp

Advertisement

Answer

You don’t want full join for the third component!

Instead you seem to want logic using INSERT . . . SELECT:

INSERT INTO DESTINATION_TABLE (YEAR, QUARTER, LABEL, NUMBER, TIMESTAMP)
    SELECT YEAR, QUARTER, 'ANY LABEL',
           ( (SELECT COUNT(*)
              FROM table1 t1
              WHERE dt.PRG_DT = t1.INN_DT
             ) +
             (SELECT COUNT(*)
              FROM table2 t2
              WHERE dt.PRG_DT = t2.INN_DT
             )
           ),
           CURRENT_TIMESTAMP
    FROM DATE_TABLE dt;
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement