views: conflict might occur ?

[Peter Primrose]

Views:

I�m newbie to views and wanted to consult about the following issue:

Say I have 2 users: A & B, and both are asking for the same report at the same time. The report is a table constructed with some arguments that in order to facilitate I had to use views.

Example: User A asks for the production between date_W and date_X. The query will generate a view with the date arguments and will return the result to user A.
At the same time user B is asking for the same report but this time with *date_Y* and *date_Z* (mind that the view is constructed with those dates)

The problem is that the view has been constructed already with date_W and date_X and user B might get the wrong result (based on dates he didn't ask for)

How can I avoid this and make sure that the result asked by the users is correct without conflicting with other users?

* Of course I can drop the table but my fear is that it might happen concurrently (meaning 2 users are asking for the same report with diff dates)

[stu derby]

Huh?

Are you trying to say that, when generating the report, you are creating a view - with a "CREATE VIEW ...." SQL statement? If so, that's horribly horribly wrong.

Or are you saying you are querying against a view? Such as "SELECT a, b, c FROM my_view"? No problems then, the database constructs the result dynamically, just like it does from a table.

[Peter Primrose]

I guess it�s a combination of both. I am �CREAT VIEW� for the report in order to facilitate my query and than I do �SELECT a,b,c FROM the VIEW�

Does that present a problem?

[stu derby]

Originally posted by Peter Primrose:
I guess it�s a combination of both. I am �CREAT VIEW� for the report in order to facilitate my query and than I do �SELECT a,b,c FROM the VIEW�

Does that present a problem?

Yes. Views are intended to be "permanent" objects, and the problem you feared in your original post is real and unavoidable; you're misusing views.

There's probably a way to either use a permanent view or rewrite your query that gets around the problem and will keep you from redefining the view.

[Peter Primrose]

Ok...so I'll rewrite my query. So what is the strength of views? I thought they are meant primarily for this type of queries where it gets too complicated and views can facilitate.

Thanks

[stu derby]

Originally posted by Peter Primrose:
Ok...so I'll rewrite my query. So what is the strength of views? I thought they are meant primarily for this type of queries where it gets too complicated and views can facilitate.

Thanks

They are for that as well as other things. They're not for dynamically helping your query (some people use temporary tables for that, but they work very differently and involve temporary copies of data, not always efficient).

For example, consider an application that has 5 tables that are commonly joined together in the same way most of the time; you write a view that encapsulates that 4 joins and your SQL queries the view instead of having to repeat the joins all the time. For example, SELECT ... FROM FULL_ORDER, instead of SELECT ... FROM ORDER, SALESMAN, ORDER_ITEM, INVENTORY_ITEM, PURCHASER WHERE ....

Or say you have a an existing table and you decide you need to split it in two, but maybe there's some read only reporting that goes against the table and you can't rewrite that: create your 2 tables with new names and replace the old table with a view (of the same name and columns). The unchanged read only programs won't know the difference.

Or in some cases, you can use views for "security". Some databases have privilege systems where you can grant a user SELECT privileges on a view and withhold it on the underlying table. In those DBs, you can then hide some columns from some users, by omitting them from the view.