SQL : How to automatically run different SELECT query on a daily basis base on the current system date

Question

I have 31 tables one for each day of the month where the table names are like subscription_x where x is the day of the month

On a daily it should run a select * from subscription_x to get data from the table of the previous day e.g if today is Dec 14th it should run select * from subscription_13 then tomorrow Dec 15th it will run select * from subscription_14

Can someone help please need a way to do this in sql

Answer 1

You need dynamic SQL like this:

EXECUTE IMMEDIATE 'SELECT * FROM subscription_' ||TO_CAHR(SYSDATE-1, 'DD') BULK COLLECT INTO ...;