Queries Tutorial
- Introduction
Queries are at the heart of any relational database, in that they
allow you to view and update the data in your various tables in
several important ways, namely:
- Sort
- Search
- Join tables or queries
- Summaries
- Update the database
With ViewPoint, you can explicitly construct and use queries, either
by choosing them from a menu in browse mode, or by permanently attaching
them to various layouts.
Queries are also automatically constructed by Viewpoint when you
drag fields from one table to another to create a join, and when
you create subviews.
The advantage of this approach is that, while it's easy to construct
the join in the first place (by simply dragging and dropping), because
the result is a query, you can manipulate it afterwards using the
normal query editing tools.
- Sorting and Searching
You can perform a Sort or a Search by choosing Search or Sort from
the Query menu, or by clicking on the Search view or Sort view icons
on the toolbar.
When you perform a Sort or Search in this way, what actually happens
is that you create a temporary query with the desired sort and/or
search settings. You can then toggle between viewing this query
or the original table by clicking on the subset button on the toolbox.
If you like, you can also give your query a name, by typing it
into the writable icon in the query toolbar, which will store the
query definition in the database and make it available from the
Query => View/Run menu. This means that you can
run the query by simply choosing it from the menu rather than having
to specify the sort and/or search settings again.
If you also tick the 'Keep index' option in the query toolbar,
the results of the query will be stored in the main database file,
so that whenever you want to use the query, it will be instantly
available, rather than having to be recalculated from scratch. Note
that you don't need to tick the 'keep index' option in order to
save the query definition in the database - all you have
to do is to give your query a name. If 'keep index' is not set,
the query will have to be recalculated when you first open it, if
it's not open already.
- How do I make the sort order permanent?
Assuming the simple flat-file case where you have a single table
of records, you may have a number of layouts that are all viewing
the same data, but with different fields shown in different places.
What's actually happening is that all your layouts are 'attached'
to the underlying table (which is probably called 'Table1', as that's
the default table that's created when you create a new database).
From the Layout view, you can alter the sort order in one of two
ways:
- Set the 'clustering key' of the table
- Create a new query and attach the layouts to it
In Method (1) the actual table records are physically sorted according
to the sort fields you give, and will automatically be reordered
if necessary if you make changes to the values in those fields.
To do this, choose Query/Table=>Edit=>Clustering key.
Method (2) is the recommended way for Viewpoint. The actual table
records are left alone, and a new index is created that contains
just the fields in the sort order, plus a linking field that allows
each index record to locate the matching table record. This is known
as a secondary index, as it is separate from the actual data
records themselves.
Note: Because each secondary index needs to store a copy
of the clustering key of the main table, it's a good idea to keep
the clustering key as short as possible. Normally you would leave
the clustering key alone, so it will just contain the ID field of
the table, i.e. the entry order of the records.
To make it easy to create and attach queries to layouts, Viewpoint
behaves differently in Layout view: if you choose any of the query
commands (Sort, Search, Join or SQL) it will automatically create
a new query, attach it to the layout and allow you to edit it. If
your layout is already attached to a query, rather than a table,
it will simply edit that query.
So, to permanently sort the records in your layout, choose Layout=>Edit
layout (if not already in Layout view) or click on
the Layout view icon, then choose Query/Table => Sort
and click on the required sort fields in order. Click on Perform
Sort to return to the Layout view.
This is equivalent to choosing Query/Table=>Edit=>New
query, setting the sort order, typing a name in the query
name box, saving the query and then using Query/Table=>Attach
to=>queryname to permanently attach the layout to the
new query.
If you also want your other layouts to show the records in this
order, you must switch to each in turn and attach them to the new
query rather than your original table. Note that if you don't do
this, you will most likely end up with a different query for each
layout, as the action of choosing Search, Sort or Join from Layout
View will automatically create a query for the current layout.
The advantage of this approach is that it allows different layouts
to show the records in different sort orders.
As each layout can be attached to any arbitrary query, it also
means that the other capabilities of queries can be made available
in the same way. For instance, one of your layouts could be attached
to a query that not only sorts the records, but also performs a
search to filter out unwanted records.
Once the layout is attached to a query, if you perform searches
or sorts in browse mode, your new queries will also be based on
the query that the layout is attached to, rather than the main table.
- Joining tables
You have now seen how you can create and use queries in browse
mode, and also how you can attach them to layouts while in Layout
View, so that the query is immediately available when the layout
is displayed.
Joins are normally only used in queries that are actually attached
to layouts, since the operation of joining one table to another
changes the list of available fields, and you therefore will probably
want to make adjustments to the actual layout such as adding some
fields from the join.
So, what happens when you drag some fields from one layout to another,
given that the layouts are initially attached to different tables?
First of all, ViewPoint determines that the fields being dragged
on are not currently available via the query that the destination
layout is attached to.
Therefore, it creates a new query, and adds both tables to the
join for that query. If you have already specified a relationship
between the two tables (in the Relationships view), this line will
be copied into the join as well. Normally you will want to have
a join line, since if you don't you'll just get the first record
from the second table shown in the layout, regardless of which record
from the first table is shown. The layout is then automatically
attached to the new query, and the frames on the layout are 'bound'
to the fields of the query. If you have turned off 'Show tab order'
in the Field menu, you'll notice that the field names change to
show the table names as well.
The full set of fields from both tables is then shown in the Columns
menu on the toolbox, since the new join query can show values from
either table.
Note that you can achieve the same result by choosing Query/Table=>Join
from the menu, clicking on Add Table and adding the required table
to the join. Again, you'll have to make sure that the join line
is set up correctly, and this is best done by editing the relationships
beforehand, as you can also set up the primary keys.
- Subview queries
Queries that Viewpoint creates for you have their type set to 'Subview',
rather than 'Select', with the table that was originally attached
to the destination layout marked as 'Main'.
This is important, since subview queries are used by Viewpoint
to avoid having to create an index of all record pairs in the two
tables. The behaviour is slightly different for subview queries
compared to select queries, in that if a record on one side of the
join has more than one matching record on the other side, a select
query would show all combinations of the records, while a subview
query will always contain exactly one copy of each record from the
'main' table, and just the first matching record from the other
side of the join.
For example, if you have a table of Invoices and a table of Customers,
each invoice can refer to one customer, but each customer could
be referred to by many invoices. If you set up the appropriate relationship
between the tables and then drag some customer fields onto the invoices
layout, you'll get a subview query where the main table is Invoices,
with a link to the Customer table.
Note that the join line is also set up as a 'left join', with an
arrow pointing from the Invoices table to the Customers table. This
means that if there is an invoice with no customer name specified,
the Invoices record will still be shown in the resulting query (although
the customer fields would be left blank). Without the arrow, only
those invoices with matching customers would be shown in the join.
In this case the subview query will actually give the same results
as a select query, since the main table is on the 'many' side, and
it is not possible to have more than one matching customer for a
given invoice. Since it's also a left join, all the invoices will
be shown, and the records in the query will match the records in
the invoices table.
Consider what happens if you construct the query the other way
round - i.e. if you drag some invoice fields into the customer layout
instead. This time the select query would show more than one record
for a given customer if that customer is mentioned in more than
one invoice, with each matching invoice record shown.
The subview query, however, still shows exactly one record for
each customer, with only the first matching invoice record shown.
To display all matching invoices, you would have to create a subview
on the layout and link it to the invoices table. That is why the
query is known as a 'subview query': it's useful for displaying
data in subviews.
The key point is that since the number of records is always exactly
the same as the number of main table records, there is no need for
Viewpoint to create a top-level index that might take a long time
to compute. In addition, it means that the query can be used for
subviews, since each top-level record corresponds to a main table
record, while the subview shows all matching records from the other
table. If it was a select query, each top-level record would correspond
to a match between a record on one side and a record on the other,
so the subview would always show just one record.
- More complex queries
In the above discussion it is assumed that joins are made between
tables, but in fact you are able to make joins between queries,
that can themselves be joins between other tables or queries.
Viewpoint encourages you to use a single query for each layout,
with all relevant tables contained within it. This is fine for most
purposes, but there are cases where you need to use a subquery.
For example, in the Invoices example there is a layout showing
which products a particular customer has bought, which is a join
between the Customers, Invoices, Items and Products tables. The
layout shows the customer in the main record, and the list of products
bought in the subview.
However, since the join between Customers and Invoices is one-to-many,
and the join between Invoices and Items is also one-to-many, this
will not work as a subview query as only the first matching invoice
record will be shown.
To overcome this, you need to construct a select query between
Invoices, Items and Products, and then create a subview query for
the layout which joins the Customer table to the new query (which
is shown in the subview). This takes care of the two one-to-many
joins - one of them is dealt with by the subview, while the other
is dealt with by the select query.
- Summary fields
Viewpoint also uses queries to deal with summary fields.
If you drag a field from the body section of a report into the
header or footer, or drag a field from a subview onto the main record,
Viewpoint will automatically construct a new subview query (unless
the layout is already attached to a subview query), and will then
create a formula field of the form Sum('fieldname').
The formula field will also be connected to the part of the query
that corresponds with the area of the layout that you dragged the
field onto: so, if you drag a field from a subview to the main record,
the sum will be over the records in the subview, whereas if you
drag it into the main footer, the sum will be over all records in
the entire query.
Summary fields created in this way are available alongside the
normal fields, so the resulting query is not strictly an aggregate
query alone, but is a join between the original query and various
aggregate queries that Viewpoint creates automatically. You don't
need to worry about these aggregate subqueries, as they are automatically
created and destroyed as needed, and they're not shown in the join
view.
In Viewpoint the summary field's formula indicates which part of
the query it has aligned itself with. For example, if you have a
layout with a subview where the main table is 'Table1', and the
subview contains records from 'Table2', and you drag a field from
the subview onto the main record, the formula might look like this:
Sum('Table2.f1') For 'Table1'
Note that the For keyword does not refer to the table over
which the sum takes place: rather, it refers to the table that's
attached to the part of the layout that contains the summary field
itself.
The reason for this is that the field inside the sum indicates
the table over which the sum takes place, while the For keyword
indicates the limits of the sum - in this case, you sum 'Table2.f1'
separately for each Table1 record, rather than for all records in
Table2.
Viewpoint can deduce the Group By fields from the structure of
the query that links Table1 and Table2, so you don't need to specify
these separately.
- SQL
SQL, or Structured Query Language, is the traditional language
used by relational databases to construct queries.
Viewpoint allows you to specify queries using SQL instead of using
the graphical tools as described above. All queries can be represented
using SQL, and if you display the SQL version of a query, any changes
you've made will be reflected in the SQL text. If you make a change
to the SQL text, the query is converted to a SQL query, and any
changes you make to the SQL will be reflected in the graphical views.
Your SQL text will remain intact unless you make a change using
one of the other views, in which case the SQL will be regenerated
to show any changes you've made.
A full discussion of SQL is beyond the scope of this article: for
now, try constructing queries and seeing what the SQL equivalent
is, and take a look at the SQL database earlier in this section.
- Action queries
The above discussion has dealt with queries that are used to interrogate
the database, such as sorts, searches, joins and summaries.
'Action' queries, as the name implies, can be used to actually
modify the contents of the database.
Viewpoint currently supports two types of action queries:
An update query is similar to a search, except that there is an
Update view instead of a Sort view, where you specify the values
that you want to replace the existing field contents with.
These values can be formulae, and you can use the symbol '@' to
represent the old value of the field. So, for example, to increase
a numeric field by 1, you would type in:
@+1
into the field's frame.
You can also specify search criteria in the Search view if you
only want to update those records that match.
If you give the query a name you can save it for later use (i.e.
by choosing it from the Query => View/Run menu
in browse mode).
A delete query is similar except that you only need to type in
the search criteria, as the records will simply be deleted.
Note: While editing a query you can change its type at any time
using the Query type menu - indeed, to create a delete query you
need to do a search first and then change the type of the resulting
query to delete. This is no bad thing, as it's a good idea to check
which records will be deleted before you start!
|
