Hi

Hopefully, and age-old problem in InterBase can now be solved by this group.

I have posted a simple script with Stock, Invoice and Item tables. The view
is reproduced below:

CREATE VIEW "VInvoiceTotal" (invID,invSubTotal,iteSubTotal) AS
SELECT INV."invID", "invSubTotal", SUM("iteQuantity" * "itePrice")
FROM "TInvoice" INV
JOIN "TItem" ITE
ON INV."invID" = ITE."invID"
GROUP BY INV."invID", "invSubTotal"

The intention of the view is to compare the invoice total from the Invoice
table with the sum of the line items in the Item table by invoice number.

Interbase and Firebird consider this view read-only because of the
join. Some would say that it should be read-only because there is also
aggregation.

However, the following statement:

UPDATE VInvoiceTotal
SET invSubTotal = iteSubTotal
WHERE ... /* Status, difference, current or whatever suits the circumstance */

is both valid and reasonable.

The key into the underlying table to be updated is known (invID), and the
field to be updated has a known value (SUM(iteSubTotal)).

The fact of the matter is that one could put the following in a stored
procedure:

FOR
SELECT invID, iteSubTotal
FROM VInvoiceTotal
WHERE ... /* as above */
INTO :invID, :iteSubTotal DO
UPDATE TInvoice
SET invSubTotal = :iteSubTotal
WHERE invID = :invID;

IMHO the JOIN and AGGREGATION do not invalidate the updateability of the
view. The essential issues are that the field to be updated (invSubTotal)
is not an aggregation itself, and the key to locating the row (invID) is
known from the join context. Critically, they both appear in the GROUP BY
clause!

Many high-end SQL servers handle this and I would really like to see
Firebird close the gap in this regard.

Any comments, criticisms or suggestions?

Regards

Andrew