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.

asked 08 Jan '13, 09:29

Bob%20Piskac's gravatar image

Bob Piskac
31225
accept rate: 0%

edited 15 Mar '13, 20:44

Mark%20Culp's gravatar image

Mark Culp
23.4k9132275


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

permanent link

answered 08 Jan '13, 12:46

Breck%20Carter's gravatar image

Breck Carter
27.0k444614889
accept rate: 21%

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...

permanent link

answered 08 Jan '13, 15:45

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676
accept rate: 33%

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

(08 Jan '13, 15:49) Breck Carter
Replies hidden

...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.

(08 Jan '13, 16:05) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×415
×28
×25

question asked: 08 Jan '13, 09:29

question was seen: 684 times

last updated: 15 Mar '13, 20:44