Our database runs a report on a job every day, the results of which go into a table which the database creates each day. The problem is the table it creates is HB975_111111, then the next day it will create a new table HB975_111112 and so on.
I am trying to create a select SQL that will look at all HB975 tables with a created date of a few dayes (there is a field with date created in each table)
However, as the table name would effectively keep changing and I would have to change the select SQL to match - I am wondering if there is a way to say select table that starts 'HB975_' and it would look at all tables whose names start HB975.
so for example
select claim_id, batch_date, run_date, update_description
from HB975_111222
where update_description = 'failed'
and run_date >= date('today') - '1 day'
This works as long as the HB975_111222 is there (i.e. the table name) but as the end figures of the table name I want to check each day will change by a random amount, I am not sure if there is a way to say something like this.
select claim_id, batch_date, run_date, update_description
*from any table containing HB975*_
where update_description = 'failed'
and run_date >= date('today') - '1 day';
I imagine this isnt possible with a table name - but any advice much appreciated!!
