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;