Hi

Hopefully, an 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