Using a oracle DB.
Table1: Dept
| Deptno | Dname |
|---|---|
| 10 | HR |
| 20 | IT |
Table2: Dept_Backup
| Deptno | Dname |
|---|---|
| 10 | HR |
| 20 | IT |
Lets say I have a join with emp table and dept table:
(Query Below)
select e.*, d.dept_name
from emp e
left join dept d
on e.deptno = d.deptno;
Lets say the table dept do not exist, in that case the join should change to dept_backup.
(If main table is down or do not exist it should query from backup table)
select e.*, d.dept_name
from emp e
left join dept_backup d
on e.deptno = d.deptno;
If you are using it in PL/SQL then you can check and try as follows:
DECLARE
V_TAB VARCHAR2(128);
MYVAR RELEVANT_DATATYPE;
BEGIN
SELECT MIN(TABLE_NAME) -- as MIN will give your main table dept if both tables exist
INTO V_TAB
FROM ALL_TABLES
WHERE TABLE_NAME IN ('MAIN_TABLE', 'BACKUP_TABLE');
EXECUTE IMMEDIATE '
SELECT COLUMN
FROM EMP E LEFT JOIN '
|| V_TAB
|| ' D ON E.DEPTNO = D.DEPTNO' INTO MY_VAR;
...
...
END;
/
Sample data is great, but you also need to specify the expected result. I.e. provide a complete minimal reproducible example.
Which dbms are you using? (Non-accessible tables will normally cause an error.)
under what circumstances would the main table not exist or be ‘down’ ,what do you mean by down when both tables are in same db also if main table does not exist/is down presumably the same issue could pertain for backup able.
Do this in stored procedure – check does “main” table is available, and execute according query.
If you cannot guarantee that the table exists, how can you guarantee that it will have the structure you need? Query that uses the table should definitely know if it exists or not. Use view as an abstraction layer and alter it’s text in the same place that accidentally drops the table.