Edit the query for a business record

The query for a business record defines how the tables are joined together and how they are ordered.

Right-click on a record in the Business Record diagram and select Edit Query. The Edit Query dialog appears.

Figure: Edit query dialog

This figure is a screenshot of the Edit query dialog. See the surrounding text for more information about using the Edit query dialog to create a new form for a generated application.

Specify the join clause

The join clause defines how the tables relate to each other. If the meta-schema includes the primary-foreign key relationships between the tables, this section is auto-populated with the correct join criteria.

If the relationships are not defined by the meta-schema, or if you are using a join other than an inner join, you may need to create or modify the join criteria.

When selecting the type of join, you can choose between INNER JOIN, FULL OUTER JOIN, LEFT OUTER JOIN or RIGHT OUTER JOIN. Refer to your database vendor for details on the different types of joins and how they are handled by the database.

Add additional tables

While not typical, you can add additional tables to your query, and you can specify an alias for the tables you add.

Reasons for adding additional tables:
  • You may be adding a WHERE condition that includes tables whose columns were not part of the column selection. For example, your may have only asked for columns from the customer and items table, but if the orders table is required to enable the join between the customer and items table, you must add it here.

Specify a WHERE clause

The WHERE clause is the filter that determines which rows answer the query. If a query does not specify a WHERE clause, then all data is returned from the table(s). Use the Where field to enter SQL that applys selectivity to the rows returned from the database.

Define the ORDER BY clause

The Order by criteria defines two things:
  1. How the data is ordered.
  2. How the data is grouped.

The Order by section is not populated for you by default.

For the Genero Report Writer, the Order by section sets the report triggers for your data schema. A trigger allows the report design to place a report object (such as a subtotal or a section header) when leaving one group of data and starting the next group of data.

For example, consider a data model about accounts and their orders. The account table has a unique field, account_id, that also serves as its primary key. The order table also uses the account_id to map an order to an account. We order by account_id, because we want the data sorted by account, and we want to provide group-level information on the account level. A trigger is created for the account_id, and when the data is streaming, a change to the value of the account_id signals the move from one group of data to the next. The trigger can indicate to print a report section: subtotals, section headers, and so on.

The order of the tables referenced in the Order by section tend to go from the general to the specific. For example, if you have accounts, orders, and regions, you state:
  • A region contains many accounts
  • An account has many orders

In the Order by section, you first specify the region table (with a unique column, such as region_id), and then specify the account table in the second row (with a unique column, such as account_id). While it is typical to use the primary key column in the Order by section, it is not necessary. However, the column must be unique.

Custom SQL

On the Custom SQL tab, you can select the Override checkbox and handwrite the FROM and WHERE clause for the query. This allows for the creation of complex SQL that may otherwise not be generated by a wizard.

Figure: Edit query dialog Custom SQL

This figure is a screenshot of the Edit query dialog custom SQL tab.
Reasons for using custom SQL:
  • You may be providing additional criteria on joins defined in queries. This applies to entities that have a record view, such as Form, WebService, or report data file (4rd). For instance, you may need to generate statements with joins on a table with a condition that can not be expressed in the WHERE clause for all tables in the join:
    SELECT * FROM foo f LEFT OUTER JOIN bar b
ON (b.barId = f.barId)
AND (b.flag= 1) <-- Additional criteria
    The above example join with the ON...AND clause, can not be modeled with the query editor. The additional criteria can be achieved using Custom SQL.
Important: The custom SQL should be limited to FROM and WHERE clauses. Do not alter the column list with a SELECT clause, and do not include an ORDER BY clause.