Creating a View?

[JWB23]

Back when I was working, over 10 years ago (since retired), part of my job included working with databases so I’m fairly familiar with working with Views, but it was at the SQL level and I’m an OOo Base newbie – so I’m kinda spinning my wheels here.

What I’ve got is two tables I created and I want to join them together (a 1:n relationship) to create a ‘third’ table I can run Queries and Reports against.

If I remember right, the SQL syntax would be something like:

CREATE VIEW “View_1” AS
SELECT
TBL1.FLD_A AS FLDA,
TBL1.FLD_B AS FLDB,
TBL1.FLD_C AS FLDC,
COUNT(*) AS CNT,
MAX(TBL2.FLD_A) AS MY_MAX_DT
FROM TBL1
LEFT OUTER JOIN TBL2 ON
FLD_B = TBL1.FLD_B
WHERE TBL1.FLD_A > ‘ ‘
ORDER BY TBL1.FLD_A

I can’t figure out how to go about doing this. The tutorial I’m going through doesn’t talk about working with Views very much, and I’ve pretty much hit a brick wall when rooting around after clicking ‘Create View… ‘ on the main menu.

Can anyone nudge me in the right direction or point to some reference material or maybe even a Users Guide for OOo Base?

Thanks!

[Villeroy]

The first thing to know is the database you are using. Base is just a database frontend. The status bar of your Base document indicates the database you are using.

menu:Insert>"Query (SQL view)" and write your SELECT statement without CREATE VIEW
A query is the thing you want to have in 95% of all cases. It is stored in the Base document. It is NOT stored in the database, so your database engine is not aware of this. You can not do something like: INSERT INTO "MyTable" (SELECT * FROM "MyQuery") because the database engine is not aware of Base queries.

You want a view in the rare cases where you want the database engine being aware of the SELECT statement.
Then you call menu:Tools>SQL... to communicate with the database engine directly and issue your CREATE VIEW statement
or you call menu:Insert>"View(Simple)" and save the SELECT statement of a new view.

However, your SELECT is an aggregation, therefore I'm sure you want to store a SELECT statement as a Base query.

[UnklDonald418]

OpenOffice documentation can be downloaded from
https://wiki.openoffice.org/wiki/Documentation
But there is only one chapter devoted to Base in the Getting Started guide
LibreOffice has a Base Handbook
https://documentation.libreoffice.org/en/english-documentation/
As Villeroy mentioned, Base is a front end for a database engine. If you are using the standard issue Embedded database look in the Announcement area of the main Base page of this Forum for a link "HSQLDB 1.8 documentation not easily reachable".
There is another option you should consider
[Wizard] Create a new 'split' HSQL 2.x database
If you are going to be writing Base reports, the built in Report Wizard is rather limited. The Oracle Report Builder Extension is far more versatile
https://extensions.openoffice.org/en/project/oracle-report-builder

[Nocton]

What I’ve got is two tables I created and I want to join them together (a 1:n relationship) to create a ‘third’ table I can run Queries and Reports against.

You can use a query as your data source for the report (Make sure you have installed the Oracle Report Builder extension) without explicitly creating a table. And you can use the Query wizard to create your query, although you may need to write directly in SQL for the final touches, especially if you are familiar with SQL statements. But occasionally, as Villeroy says, you may need to create a View/table. The simplest way to do this is to is to right-click on your query and choose 'Create as View'.

[JWB23]

thanks for all the tips, much appreciated!