View of a Query; when refreshed?
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
View of a Query; when refreshed?
Hi All,
Just want to know when a view of a Query is refreshed?
It appears that I had a view that was not up to date.
Dream
Just want to know when a view of a Query is refreshed?
It appears that I had a view that was not up to date.
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: View of a Query; when refreshed?
A View works much the same as a Query. Generally a View is used as a data source for a form. When the form is refreshed the view data should be reloaded.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: View of a Query; when refreshed?
A view of a query does not exist.
A view is a SELECTion that is stored in the backend.
A query is a SELECTion that is stored in the frontend.
The frontend can see the backend but the backend can not see the frontend, so you can create a query that is based on a view but not the other way round.
A value of a calculated field (SELECT ... "X"+"Y" ... )is refreshed instantly when the current record is stored.
A subform refreshes when you modified and stored the parent form's current record or move to another record.
Anything else requires the click on some refresh button or macro code.
A view is a SELECTion that is stored in the backend.
A query is a SELECTion that is stored in the frontend.
The frontend can see the backend but the backend can not see the frontend, so you can create a query that is based on a view but not the other way round.
A value of a calculated field (SELECT ... "X"+"Y" ... )is refreshed instantly when the current record is stored.
A subform refreshes when you modified and stored the parent form's current record or move to another record.
Anything else requires the click on some refresh button or macro code.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: View of a Query; when refreshed?
I think you are confusing the OP, Villeroy. The normal way of creating a View is from a Query - Right-click on the Query and select 'Create View'.so you can create a query that is based on a view but not the other way round.
The answer to dreamquartz's question is that the View should be refreshed automatically when a record is changed so that the Query and the View should show the same results. I use Views mostly for reports as sometimes if there are compicated chains of queries the report cannot evaluate the result from a Query.
Of course if you alter the Query, you will need to recreate the View.
OpenOffice 4.1.12 on Windows 10
Re: View of a Query; when refreshed?
Sorry, I intrpreted "view of a query" as the query result as seen on screen.
If "view of a query" means "SELECT statement that has been forked from a query into a view", then the answer is that the view's SELECT statement will never be updated. You have to delete the view and save the query again under the same view name.
Alternative #1: Use a DROP VIEW and then a CREATE VIEW statement
Alternative #2: Close all connections to the database and edit the database's script file.
If "view of a query" means "SELECT statement that has been forked from a query into a view", then the answer is that the view's SELECT statement will never be updated. You have to delete the view and save the query again under the same view name.
Alternative #1: Use a DROP VIEW and then a CREATE VIEW statement
Alternative #2: Close all connections to the database and edit the database's script file.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: View of a Query; when refreshed?
As Nocton indicated.
I use many views for that reason as described.
OK. Now you , Villeroy indicated, can this be automated the moment you the moment you do a shutdown?
I use 16 views I want to have refreshed when ever I want, but definitely @ startup.
Is there a way to deal with this one way or an other?
Villeroy, I do not quite understand alternative #2.
I use many views for that reason as described.
OK. Now you , Villeroy indicated, can this be automated the moment you the moment you do a shutdown?
I use 16 views I want to have refreshed when ever I want, but definitely @ startup.
Is there a way to deal with this one way or an other?
Villeroy, I do not quite understand alternative #2.
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: View of a Query; when refreshed?
A HSQLDB consists of files. One of them is the script file where the whole definition of tables, indices, relations and views is written as a sequence of SQL commands.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: View of a Query; when refreshed?
I forgot that you can edit a view when using an embedded HSQL. Right-click>"Edit in SQL View" is not availlable with "normal" (to me this means not embedded) databases. Without knowing the actual type of database, we spread more and more confusion. In this special case of embedded HSQL it might be easiest to copy and paste SQL.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: View of a Query; when refreshed?
@Villeroy
I am indeed aware of the script file.
I use it on a regular basis.
I do not use embedded databases anymore.
I use the HSQLDB 2.4.1.
Is anything accomplished with:
Checkpoint Defrag and
Shutdown Compact?
I am under the impression that everything is 'rebuiild' when re-starting the database.
Dream
I am indeed aware of the script file.
I use it on a regular basis.
I do not use embedded databases anymore.
I use the HSQLDB 2.4.1.
Is anything accomplished with:
Checkpoint Defrag and
Shutdown Compact?
I am under the impression that everything is 'rebuiild' when re-starting the database.
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: View of a Query; when refreshed?
A view that used to be a query, saved with right-click>"Create as view", will never be "refreshed" in terms of "synchronized with the modified query" just like a file that has been saved in some other file format will never be synchronized with its source.
I know of 4 ways to edit the definition (the SELECT statement) of a view.
1. Delete view and save the query again with right-click>"Create as view".
2. Do the same on the command line with DROP VIEW and CREATE VIEW.
3. Edit the view definition in the script file.
4. Copy the modified SELECT statement from the query into the SQL view of the view.
The last option works with embedded HSQLDB only.
As far as I know, this has absolutely nothing to do with anything else that you do to your database such as SHTDOWN COMPACT.
I know of 4 ways to edit the definition (the SELECT statement) of a view.
1. Delete view and save the query again with right-click>"Create as view".
2. Do the same on the command line with DROP VIEW and CREATE VIEW.
3. Edit the view definition in the script file.
4. Copy the modified SELECT statement from the query into the SQL view of the view.
The last option works with embedded HSQLDB only.
As far as I know, this has absolutely nothing to do with anything else that you do to your database such as SHTDOWN COMPACT.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: View of a Query; when refreshed?
Villeroy:Villeroy wrote:A view that used to be a query, saved with right-click>"Create as view", will never be "refreshed" in terms of "synchronized with the modified query" just like a file that has been saved in some other file format will never be synchronized with its source.
I know of 4 ways to edit the definition (the SELECT statement) of a view.
1. Delete view and save the query again with right-click>"Create as view".
2. Do the same on the command line with DROP VIEW and CREATE VIEW.
3. Edit the view definition in the script file.
4. Copy the modified SELECT statement from the query into the SQL view of the view.
The last option works with embedded HSQLDB only.
As far as I know, this has absolutely nothing to do with anything else that you do to your database such as SHTDOWN COMPACT.
I strongly disagree with your comment above, 3. Edit the view definition in the script file. .
The only approved ( and therefore safe AND therefore should be used in all situations ) is defined in HSQL documentation:
http://www.hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html#dbc_view_creation
Of course, one can always get VIEW DEFINITIONS with the following Queries:[b]HSQL Documentation:[/b] [url]http://www.hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html#dbc_view_creation[/url] wrote:
ALTER VIEW
alter view statement
<alter view statement> ::= ALTER VIEW <table name> <view specification> AS <query expression> [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
Alter a view. The statement is otherwise identical to CREATE VIEW. The new definition replaces the old. If there are database objects such as routines or views that reference the view, then these objects are recompiled with the new view definition. If the new definition is not compatible, the statement fails.
Code: Select all
-- Return all View Definitions from the CURRENT_SCHEMA Select INFORMATION_SCHEMA.VIEWS.TABLE_NAME as VIEW_NAME, INFORMATION_SCHEMA.VIEWS.VIEW_DEFINITION From INFORMATION_SCHEMA.VIEWS Where INFORMATION_SCHEMA.VIEWS.TABLE_SCHEMA = CURRENT_SCHEMA
Code: Select all
-- Return View Definition For a Specific VIEW Select INFORMATION_SCHEMA.VIEWS.TABLE_NAME as VIEW_NAME, INFORMATION_SCHEMA.VIEWS.VIEW_DEFINITION From INFORMATION_SCHEMA.VIEWS Where INFORMATION_SCHEMA.VIEWS.TABLE_NAME = 'MY_VIEW_NAME' -- Change to the name of VIEW you desire
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: View of a Query; when refreshed?
I think this is going to be side tracked, because I may have asked it the wrong way.
Our Client uses e.g. a view called 'vAddress' (based on a Query called 'qAddress'; and created via 'Create as View') for standardization purposes.
The Query qAddress does not require User input.
For test purposes, I just added information to the table 'tAddress', and that information is processed by 'qAddress' to show up in vAddress instantly (this is the expected outcome).
In the Database 'vAddress' is used is many situations.
Big advantage for our Client is that if we update/modify 'qAddress', everywhere where 'vAddress' is used, it will show the newly implemented results, based on 'qAddress'.
It might be an extra step, but for standardization purposes, we have chosen it to do it this way.
It does not really affect the performance of the database except in one location, which we are trying to solve still.
What we found, however, is that 'vAddress' is not always updated instantly, and we were wondering what the problem could be.
It appears to be a potentially random issue (arghh; WCS [Worst Case Scenario]), but so far nothing points in a certain direction.
Dream
Our Client uses e.g. a view called 'vAddress' (based on a Query called 'qAddress'; and created via 'Create as View') for standardization purposes.
The Query qAddress does not require User input.
For test purposes, I just added information to the table 'tAddress', and that information is processed by 'qAddress' to show up in vAddress instantly (this is the expected outcome).
In the Database 'vAddress' is used is many situations.
Big advantage for our Client is that if we update/modify 'qAddress', everywhere where 'vAddress' is used, it will show the newly implemented results, based on 'qAddress'.
It might be an extra step, but for standardization purposes, we have chosen it to do it this way.
It does not really affect the performance of the database except in one location, which we are trying to solve still.
What we found, however, is that 'vAddress' is not always updated instantly, and we were wondering what the problem could be.
It appears to be a potentially random issue (arghh; WCS [Worst Case Scenario]), but so far nothing points in a certain direction.
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: View of a Query; when refreshed?
Structural changes should be done when nobody is working with the database.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice