Help with select * from, select count(*) from a table with 411,412,220,990 rows

Database

Help with select * from, select count(*) from a table with 411,412,220,990 rows

Hello experts, 

I am doing some analysis to check the rows between two specific dates and also doing a count(*) between two specific dates. The script is easy to write (but on small data). How do i write on table with data with 411,412,220,990 rows. Whenever i write a query, i get "-No more spool space".

Need some help in writing a query with Range_n & Patition by in Teradata, so that i dont get the error, and also the performance can be increased.

Any suggestion, would appreciate it.

Thanks

2 REPLIES
N/A

Re: Help with select * from, select count(*) from a table with 411,412,220,990 rows

Can you show what you did so far? Plus the table DDL?

If this is an important task and you keep running out of spool you might conact your DBA to increase it.

Re: Help with select * from, select count(*) from a table with 411,412,220,990 rows

I think i figured out the way to get data of (1 day, 7 days, 1 month from the current_date). Thanks for the reply back dieter.

This worked for me.

SELECT 
CAST(COUNT(*) AS BIGINT)
FROM TABLE_NAME
WHERE INV_DT >= ADD_MONTHS(CURRENT_DATE, -1) - EXTRACT(DAY FROM ADD_MONTHS(CURRENT_DATE, -1)) + 1;