DataPower 2 Queries
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
- Join tables or queries
- Update the database
With DataPower 2, 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 DataPower when you drag
fields from one table to another to create a join, and when you create
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 from browse mode by choosing them
from the Query menu, and you can also perform a sort and a search in a
single query by switching between Sort and Search on the query toolbar.
When you perform a Sort or Search in browse mode, 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 the query has
no search parameters, the 'Subset' button is shown as 'Index' instead,
to show that it affects the sort order).
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.
Whereas in DataPower 1 the 'Sort' command would permanently reorder
the records in your database (at least until you used the Sort command
again), each query in DataPower 2 can have its own sort order. In this
respect it's much more like searching - you can toggle between the underlying
sort order and your new sort order, and if you close and reopen the database,
the sort order reverts to what it was originally.
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 Edit Layout mode, 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
Method (1) is essentially what DataPower 1 always did - the actual table
records are physically sorted according to the sort fields you give, and
will be automatically reordered if necessary if you make changes to the
values in those fields. To do this, choose Query/Table=>Edit=>Clustering
key from the Edit Layout menu.
Method (2) is the recommended way for DataPower 2. 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, ie. the entry order
of the records.
To make it easy to create and attach queries to layouts, DataPower 2
behaves differently in Edit Layout mode: if you choose any of the query
commands (Sort, Search, Join or SQL) it will automatically create a new
query called "Join for layoutname", 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 edit layout mode), then choose Query/Table
=> Sort and click on the required sort fields in order. Then click on
OK to return to Edit Layout mode.
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 edit layout mode 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, which was not possible using
Because 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
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.
We've seen how you can create and use queries in browse mode, and also
how you can attach them to layouts while in Edit Layout mode, 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, DataPower determines that the fields being dragged on
are not currently available via the query that the destination layout is
Therefore, it creates a new query called 'Join for layoutname',
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 Fields
menu on the toolbox, since the new join query can show values from either
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.
Queries that DataPower 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 DataPower 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's 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.
Now consider what happens if we construct the query the other way round
- ie. if we 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 DataPower
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
More complex queries
In the above discussion I have 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.
DataPower 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
To overcome this, we 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.
DataPower 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, DataPower
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 DataPower 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.
From DataPower 2.10 onwards, 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:
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, we sum 'Table2.f1' separately
for each Table1 record, rather than for all records in Table2.
The cunning part is that DataPower 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, or Structured Query Language, is the traditional language used
by relational databases to construct queries.
DataPower 2 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 on our Examples page.
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.
DataPower 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:
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 (ie. 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 that 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!