At 06:24 PM 31/03/2011, you wrote:

>> You create the view over an entire set of data. Then, when you want
>> to use the view, you use it like a table, including SELECTing from
>> it. If the data set is not naturally updatable, you can make it so,
>> using triggers.
>>
>
>That's an answer to a different question, though. Parametrized VIEWs do
>have their uses and can be very useful in specific cases.

There is no such animal.

>Using WHERE
>clauses on completed views may achieve the same result, but often at a
>serious performance cost.

How? The design concept of a view expects a WHERE clause at run-time....otherwise, you might as well just run the underlying definition statement at runtime.

>Or it may not be possible to use the WHERE
>clause on VIEW at all, you need it in the VIEW definition.

One of the *reasons* to use a view for certain sets is exactly to resolve the complexity of embedding WHERE clauses - to get a subset of a subset [of a subset...]. The view defines the broadest set of data, which you might well want to have available for multiple purposes; you query the view to limit it to certain records (via the WHERE clause).

Note that you CAN use a WHERE clause in a view definition; just not a parameterised one. I think probably the mechanism that *you* are referring to here should be defined as a selectable SP, or the equivalent in DSQL if using latter versions of Firebird.

>Example (non-working, but giving the general idea):
>
>CREATE VIEW price_diff(product, price, difference)
>AS SELECT product, price, price-(SELECT price FROM products WHERE
>product=:my_product) FROM products;
>
>SELECT * FROM price_diff(123);
>SELECT * FROM price_diff(456);

Here is a case where either a SSP with inputs or (with Fb 2 or higher) using a derived table or an EXECUTE BLOCK and parameterising the DSQL statement in the normal way would be appropriate.

But - if you absolutely MUST use views to do something like this, define two views and join them, or define one view and join it to the underlying table, either as another view or in DSQL.

./hb