Creating a View?

Getting your data onto paper - or the web - Discussing the reports features of Base

Creating a View?

Postby JWB23 » Thu Jun 21, 2018 9:33 pm

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!
OpenOffice 4 on Windows 10
JWB23
 
Posts: 9
Joined: Wed Jun 20, 2018 12:02 am

Re: Creating a View?

Postby Villeroy » Thu Jun 21, 2018 9:47 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26867
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating a View?

Postby UnklDonald418 » Fri Jun 22, 2018 6:09 am

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
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1179
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Creating a View?

Postby Nocton » Fri Jun 22, 2018 9:50 am

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'.
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 504
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Creating a View?

Postby JWB23 » Sat Jun 23, 2018 3:20 am

thanks for all the tips, much appreciated!
OpenOffice 4 on Windows 10
JWB23
 
Posts: 9
Joined: Wed Jun 20, 2018 12:02 am


Return to Reporting

Who is online

Users browsing this forum: No registered users and 2 guests