822.TXT
=====================================================================
Using Views
=====================================================================
PRODUCT: R:BASE VERSION: 4.5 or Higher
=====================================================================
CATALOG: Programming in R:BASE AREA : Logic & Data Manipulation
=====================================================================
Views are powerful and useful database tools. A view is a stored
SELECT command. No data is stored with a view; when a view is used,
the data is dynamically generated so it is always current. Users
often comment that they don't use views, or can't get them to work
correctly, this article shows how easy views are to use and how
useful they can be.
A good relational database design almost always requires the use of
views. Descriptive information is commonly stored in only one table,
and related to other data through linking columns. For example, the
invoice table won't store the employee name, rather it stores the
employee id number. The employee name is looked up when needed or
joined with the invoice table through a view.
Views are commonly used to generate reports, manipulate data, and
provide answers to queries not possible with a single SELECT command.
A view can be based on one table or many tables, and can even use
other views. Views are created using Query by Example (QBE) or the
CREATE VIEW command in the R> prompt window.
Using QBE to Create a View
When you create a view using QBE, the menus prompt you through
selecting the tables and the columns to display. You can view the
data to make sure the query retrieves the desired data, then save the
query as a view. The next time you want to look at that set of data,
open the view from the Object Manager. The current data set is
retrieved from the underlying tables and displayed in the Data
Browser. Views created in QBE link the tables using the equals (=)
operator and common column names by default. Other links can be
created by changing QBE User Settings or by modifying the WHERE clause
in the Where Builder. When you create a query using QBE, R:BASE builds
a SELECT command with a WHERE clause to link the tables and identify
the rows to display. That SELECT command is saved as the view
definition.
R:BASE 5.5 provides more query options through QBE than prior versions
of R:BASE. You can even edit the WHERE clause in the Where Builder to
create more complex queries, such as a query using a GROUP BY clause.
This option is not available in DOS versions of R:BASE.
Using the CREATE VIEW Command
Not all queries can be defined using QBE. The R> prompt window
provides a wider range of query options than does QBE. When creating
a view from the R> prompt, first use the SELECT command to create and
verify the query, then modify the SELECT command by adding the
command string CREATE VIEW viewname AS to the front of the query.
Here's an example of testing a query, then editing the command and
creating a view.
1. At the "R> Prompt" window, enter the command
SELECT Customer.company, T1.custid, T1.transid, +
T1.transdate, T1.invoicetotal +
FROM Customer , TransMaster T1 +
WHERE T1.custid = Customer.custid
Verify that the correct data is displayed.
2. Press [PgUp] to retrieve the SELECT command. [PgUp] and [PgDn]
cycle through the keyboard buffer of previously executed commands.
3. Press [Home] to move to the beginning of the command.
4. Turn insert mode on if it is not already on. Then, type "CREATE
VIEW testview" and press the spacebar. The command now looks like
this:
CREATE VIEW v_trans AS +
SELECT Customer.company, T1.custid, T1.transid, +
T1.transdate, T1.invoicetotal +
FROM Customer , TransMaster T1 +
WHERE T1.custid = Customer.custid
5. Press [End] to move to the end of the command, then press [Enter]
to execute the edited SELECT command and create the view.
This technique is especially useful when working on complex views as
you do not need to continually drop and create the view definition.
Since a view is just a stored SELECT command, once the SELECT
command works correctly it can be saved as a view using this
technique.
Table Relationships
A view can be created to select data from one table or from many
tables. A view can also retrieve data from other views. When a view is
designed to retrieve data from many tables, the tables are joined. The
data retrieved is determined by the relationship between the tables. A
relationship is defined by the linking columns, which are columns that
are from different tables and contain matching data. Linking columns
often have the same name in both tables. There are three basic
relationships between tables in a relational database:
One-to-one_there is only one row in each table where the
linking column(s) contain matching data. The linking
column in each table is unique. When two tables with a
one-to-one relationship are joined, the result is a "one"
table.
One-to-many_in one of the tables, the linking column is
unique, there is only one row for each value. In the
other table, there can be one row or many rows for the
linking column. When the tables are joined, the result is
a "many" table. The number of rows returned is the number
of rows from the "many" table.
Many-to-many_in both tables, there can be many rows for
the linking column(s). The linking column value is not
unique in either table. The result of a join between two
"many" tables is a "many" table. The number of rows
returned is the number of rows in one table multiplied by
the number of rows in the other table.
Relationships are usually defined between two tables at a time.
Relationships between more than two tables are more easily defined
when taken two at a time. For example, a one- to-many-to-many join
can be thought of as a one-to-many join and then a many-to-many join.
The one-to-many is the most common relationship in a relational
database. For example, the relationship between customers and orders
is a one-to-many relationship. Each customer has a row of data in
a customer table, but can have many associated rows in an orders
table. An example of a many-to-many relationship is the relationship
between orders and payments. An order can have many payments
associated with it, and a payment can be associated with many
orders. When orders and payments are connected to customers, the
relationship becomes a one-to-many-to-many. Each customer has a row of
data in a customer table, many associated rows in an orders table, and
many associated rows in a payments table. The payments and orders are
both associated to the customer, but not necessarily to each other.
Views are easy to create on one-to-one or one-to-many relationships.
It is difficult to create a view on tables with a many-to-many or
one-to-many-to-many relationship. Data is often duplicated or appears
to be duplicated. Sometimes, intermediate views can be used to
manipulate the data so that the view ends up with only one many table
in the final result.
Joining Tables
The table relationships are used when joining tables. There are three
types of joins_inner join, outer join, and self join. The most common
way to join tables is to use an inner join, also called an equi-join.
An inner join links the tables where the data in the linking column
matches. When you create a view in QBE using one of the default
WHERE clause options you create a view that is an inner join_only
matching rows of data are retrieved.
An outer join is also common, but is not created in QBE. An outer
join view_a view where you retrieve all the matching rows between two
tables and all the non-matching rows_must be created from the R>
prompt window. The SELECT command includes a special syntax for
creating outer joins. A view made using the outer join syntax of the
SELECT command uses only two tables at a time. To join more than two
tables using this syntax, you create multiple views.
A self join, in which a table is joined to itself, is not commonly
used. A self-join is usually created at the R> prompt.
Naming the Columns in a View
A view can be thought of as defining the structure for a temporary
table. Each item specified for display in the SELECT command becomes
a column. If you do not specify names for the items, R:BASE generates
default names. If the item selected is a column, the default name is
the column. If the item selected is an expression, the default name
is the first part of the expression definition or just UNNAMED.
Expressions do not generate a name that can be used to select this
item in a report or a WHERE clause.
You can explicitly name the columns in your view. This is an optional
part of the CREATE VIEW syntax. Naming the columns is required on
views that use expressions. In QBE you name the columns in your view
by choosing Query: columns Aliases..... (available in R:BASE 5.5
only). You can name the columns for any view created at the R>
prompt.
For example, create view v_trans1 with an expression but without
explicit column names:
CREATE VIEW v_trans1 AS +
SELECT transid,SUM (extprice) +
FROM transdetail T1,transmaster T2 +
GROUP BY transid
Selecting from the view displays the data as shown below. Notice
that the first part of the expression becomes a column heading:
transid SUM (extprice)
---------- ---------------
4780 $665,000.00
4790 $3,972,500.00
4795 $12,320,000.00
4800 $13,632,500.00
Change the view definition to use explicit column names. The column
names can be the same as columns selected from the tables or they can
be different. They are used for this view definition only. If you use
a name that exists in another table or view in the database, the data
type of the data in the column must match.
CREATE VIEW v_trans2 (ID#,amount) AS +
SELECT transid,SUM (extprice) +
FROM transdetail T1,transmaster T2 +
GROUP BY transid
Now select the data from the view; the column headings reflect the
names specified in the view definition. The expression is now named
and can be referenced in a form, report, WHERE clause, or ORDER BY
clause.
ID# amount
---------- ---------------
4780 $665,000.00
4790 $3,972,500.00
4795 $12,320,000.00
4800 $13,632,500.00
When naming columns in a view, you can't just name the expressions;
you must provide a name for each item that is selected. If there are
five items selected for display, you must provide five column names
when you create the view.
Modifying a View Definition
Views created in QBE can be modified in QBE. Some views created at
the R> prompt can also be modified in QBE_views that could have been
created in QBE. Normally, however, views created at the R> prompt
cannot be modified in QBE. You will usually get the message "Internal
Error - Out of Dynamic Space" when the view cannot be modified in QBE.
To modify a view outside of QBE, follow these steps:
1.Use the UNLOAD command to place the CREATE VIEW command for the
view definition in a file.
OUTPUT view.str
UNLOAD STRUCTURE FOR v_trans
OUTPUT SCREEN
2.Edit the file using the R:BASE text editor or another ASCII text
editor and modify the view definition as desired.
RBEDIT view.str
3.Remove the current view definition.
DROP VIEW v_trans
4.Re-create the view using the modified view definition.
RUN view.str
Some users always create their views using command files. You can
easily incorporate the drop and create view commands into a command
file, then just edit that command file to change a view definition.
For example, the file v_trans.cmd might contain these commands:
DROP VIEW v_trans
CREATE VIEW v_trans (ID#,amount) AS +
SELECT transid,SUM (extprice) +
FROM transdetail +
GROUP BY transid
To modify the definition of the view v_trans, edit and then run the
file v_trans.cmd. This type of command file is best run in
single-user; in multi-user you cannot drop the view unless you can
get a full database lock.
Using Variables in Views
Sometimes you want a view to retrieve rows based on different
criteria, but don't want to always drop and re-create the view
definition in order to change the WHERE clause. You can use a variable
in the WHERE clause of your view definition or you can use a variable
in an expression that is part of the view definition. The variable
must exist in memory any time the view is created. The R:BASE BACKUP
and RESTORE commands require that the variable be defined (exist in
memory) when restoring the view definition.
In the WHERE clause, put the variable name in parentheses, (.varname),
so that the variable name, not the variable value, becomes part of the
view definition. If the variables in the view definition are not
enclosed in parentheses, their current value becomes part of the view
definition. For example, the following commands create a view with the
literal values for the date variables as part of the view definition:
SET VAR vdate1 DATE = 9/1/95, vdate2 DATE = 9/30/95
CREATE VIEW v_trans (transid, amount) AS +
SELECT transid,SUM (extprice) +
FROM transdetail T1, transmaster T2 +
WHERE T1.transid = T2.transid AND +
transdate BETWEEN .vdate1 AND .vdate2 +
GROUP BY transid
LIST VIEW v_trans
View: v_trans (transid, amount)
SELECT transid,SUM (extprice) FROM transdetail
T1,transmaster T2 WHERE T1.transid = T2.transid AND
transdate BETWEEN '9/1/95' AND '9/30/95' GROUP BY transid
To store the variable name as part of the view definition, put
parentheses around the variables in the CREATE VIEW command:
SET VAR vdate1 DATE = 9/1/95, vdate2 DATE = 9/30/95 CREATE VIEW+
v_trans (transid, amount) AS +
SELECT transid,SUM (extprice) +
FROM transdetail T1, transmaster T2 +
WHERE T1.transid = T2.transid AND +
transdate BETWEEN (.vdate1) AND (.vdate2 ) +
GROUP BY transid
LIST VIEW v_trans
View: v_trans (transid, amount)
SELECT transid,SUM (extprice) FROM transdetail T1,transmaster T2
WHERE T1.transid = T2.transid AND transdate BETWEEN (.vdate1) AND
(.vdate2 ) GROUP BY transid
When the variable name is stored in the view definition the view is
completely dynamic and generates different data for each user based on
their current values of the variables. Note, however, that the view
does not use indexes to retrieve the data if the variables are
enclosed in parentheses. Indexes are used to link the tables, but not
to qualify the rows using other conditions. Instead of putting the
WHERE clause in the view definition, another option is to add the
WHERE clause to the command that uses the view. This way indexes are
used to qualify rows as well as to link rows.
CREATE VIEW v_trans (transid, amount) AS +
SELECT transid,SUM (extprice) +
FROM transdetail T1, transmaster T2 +
WHERE T1.transid = T2.transid +
GROUP BY transid
SET VAR vdate1 DATE = 9/1/95, vdate2 DATE = 9/30/95
SELECT * FROM v_trans +
WHERE transdate BETWEEN .vdate1 AND .vdate2
Referencing Columns in Views
A frequent question about views is "How do you refer to the column
names in a view? T1.transid or just transid?" Remember a view is a
stored SELECT command. The stored command, the view, uses the
correlation names, T1, for example, that are stored with it. Other
commands that use the view have their own, separate correlation
names. The correlation names used in the column list and the WHERE
clause of the view definition are not used in the column list, WHERE
clause, or ORDER BY clause of the command accessing the view.
For example, look at the following view definition:
View: empview
SELECT T1.empid, T1.emplname, T1.empfname, T1.empcity, T1.empstate
FROM Employee T1
The T1 is a correlation name for the stored SELECT command only. When
the view is accessed by another SELECT command, that SELECT command
has its own correlation names.
SELECT EV.empid, EV.emplname, SUM(invoicetotal) +
FROM empview EV, transmaster TM +
WHERE TM.empid = EV.empid +
GROUP BY EV.empid, EV.emplname
In this command, the transmaster table has a correlation of TM, the
view empview has a correlation name of EV. The columns in the view
are referenced by the correlation name EV from the executed command,
not the correlation name T1 that is part of the view, a stored SELECT
command. The columns could also be referenced by the view name, for
example, empview.emplname.
Create Tables From Views
Views are handy because they allow you to manipulate your data, join
tables, and customize data output without storing additional data in
the database. Views dynamically generate the data each time you use
the view so the data is always up to date. However, you can use a view
to create a permanent table in your database. The PROJECT command
creates a table with the column definitions and data from the view
definition. The view must have named columns. If the view has any
unnamed columns or expressions, R:BASE is unable to create column
names in the table. The column data types are automatically
determined: if the column name is already defined, the new table picks
up that column's data type; if the column in the view is a TEXT
expression, the data type is always NOTE; other expressions are
assigned the data type of the expression result. Once the table has
been created, it contains that particular set of data. The data is
not updated unless explicitly edited.
For example, the following command creates a table, sales_table,
using the current data retrieved by the view v_trans. Once the table
is created the data in the table is static unless edited by the user.
PROJECT sales_table FROM v_trans USING ALL
Views on Views
You are not limited to creating views on tables, you can also create
views on views. That means that in a CREATE VIEW command or SELECT
command a view can be used just like a table. The ability to build a
view on a view lets you solve even more database problems using views.
There are many queries that cannot be done with a single command. By
using views, you can manipulate the data in steps to get the final
result you want.
For example, you cannot group data by month using a date data type
column in a SELECT command. You need to have a separate column to
store the month. However, you can use a view to generate a data set
that contains just the month instead of the full date value. Then,
queries that need to be grouped by month are based on the view instead
of the table. You don't need to store extra data in your database.
CREATE VIEW trans_month +
(transid, custid, empid, month, +
netamount, freight, tax, invoicetotal) AS +
SELECT transid, custid, empid, (IMON(transdate)), +
netamount, freight, tax, invoicetotal +
FROM transmaster
The view trans_month contains the integer value for the month
(January = 1) instead of the full date. You can then easily generate
sales data grouped by month using the view rather than the actual
table.
Use Test Data
Sometimes, a command works great when you test it, then fails when put
into a production system. The difference is that the production system
has more data, and happens to contain a subset of data that causes the
command to fail. Make sure that you have a test data set that contains
a row of data for each condition that might be met, both true and
false. Then when you test a view using a SELECT command, you know that
the command is correct and gives you the expected results in every
data situation.