DataPower 2 Queries

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 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 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 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:

1. Set the 'clustering key' of the table
2. 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 DataPower 1.

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 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

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 attached to.

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 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 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 record.

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 be shown.

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.

Summary fields

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:

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, 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

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.

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.

DataPower currently supports two types of action queries:

• Update
• Delete

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 (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!