How can I modify a view that other views depend on?

[derabbink]

I use LibreOffice Base to create weekly reports. I have one table (named time_spent) that contains a lot of normalized data, and I am using views to do the processing required to produce the reports I need.

Every week I add new data to that table, and I want to export fresh reports for only that week.

Instead of re-creating all the views that are being used to create my report every week, I was hoping to create a view named time_spent_selection, which is essentially SELECT * FROM "time_spent" WHERE "week" = '2015-04', and use that to assemble all subsequent views. Every week would then only have to change that WHERE clause and automatically get the reports for the right week.

However, LibreOffice base denies saving any changes made to time_spent_selection view with the following error message:

Table is referenced by view: bugs in statement [DROP VIEW “time_spent_selection”]

Here, bugs is one of the views that references time_spent_selection in it’s definition.

Question:

How can I force-modify this view, on which other views depend?

I understand that - if this were possible - all views would have to be recomputed, and this is exactly what I want.

[doug]

HSQLDB v1.8 guide provides optional Cascade syntax for the DROP VIEW statement, i.e.,

DROP VIEW <viewname> [IF EXISTS] [RESTRICT | CASCADE];

You would run that through the SQL dialog box. Dropping views seems like the hard way, but that is what your question references.

(If this answers your question, please accept the answer by clicking the check box () to the left)

[PYS]

Hi

May be I dot not understand your problem but I do not would do it like this: I would store my reference date in a table which avoids change views. See attached very simplified example. No need to update the views, only have to update the date in DateRef table.

Regards