Multiple tables but only one active

[Former Member]

I need the following, mutilple tables but only one active.

work_table_1 work_table_2 work_table_3 work_table_4

I need the following to work.

select * from work_table

work_table will point to the active 1-4 table. Is usng a view the best way to do this in SQL Anywhwre 12 or above? In oracle I use a synonym.

[VolkerBarth]

Is there only one user at a time who needs to access one of the 4 work tables under a certain name?

Then I would agree with Breck that views are handy for that. When you have to switch to another table, you simply have to adapt the view definition, i.e. use an ALTER VIEW statement, such as

create view work_table as select * from work_table_1;
select * from work_table; -- will use wt 1
alter view work_table as select * from work_table_2;
select * from work_table; -- will use wt 2 now

---

However, if several users (or connections or applications) need to be able to use one of these work tables, and they may have to use different ones, you could "hide" that detail behind a stored procedure that returns the desired table as result set. Note that with v9 and above, a stored procedure can be used inside the FROM clause of a select statement quite similar to a table/view.

You could then use a connection-specifig variable (cf. CREATE VARIABLE) or an input parameter to specify the desired work table. - If that fits your requirement, we might show a simple scetch as guideline...

[Breck_Carter]

...or separate views could be owned by separate users.

[VolkerBarth]

...yes, for sure.

Note, the following answer shows some samples how to use views (separate ones for different users vs. a single one used with a variable to filter results) to return different result sets for different users:

http://sqlanywhere-forum.sap.com/questions/5281#5283.

In a similar fashion, one could use views to return result sets from different tables. Note however, that for the VARIABLE-specific solution, one might need to use an UNION ALL over all 4 work tables (and use the variable to filter out the result set of the "non-active" tables) - here a stored procedure would be easier to use, methinks.

[Breck_Carter]

CREATE VIEW is the way to go. Views are expanded (replaced) in the very first query processing step; see Query processing phases.