I am working on an automation project and the query I was given to automate has a variable in it called &Prev_MTH_YYYMM. The query joins to a table AB_&Prev_MTH_YYYMM. The actual table name is AB_202401. How can I change the join to reflect AB_202401 and then next month AB_202402 when the query needs to run again and so on for each month. I only have access to the sql query and nothing that creates the new table each month as thats done within another process that the owner is unwilling to change.
I know this is not the optimal way to name a table but this is what I was given. Any advice is greatly appreciated.
Ive tried to do this
select AA.1, AA.2, AA.3, B.1, B.2 from AA JOIN (select 'AB_'||(SELECT DISTINCT to_char(trunc(last_day(add_months(sysdate,-1))),'YYYYMM') from dual) from dual) B ON AA.1 = B.1
and well this is not working.
Basically I need it to do this:
select AA.1, AA.2, AA.3, B.1, B.2 from AA JOIN AB_202401 B ON AA.1 = B.1