0

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

1
  • 1
    You may review your database design. Why do you create a table for each day? Consider partitioning, which should make the handling much easier. Commented Dec 17, 2022 at 20:35

1 Answer 1

0

You need dynamic SQL like this:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.