Edit the query for a business record

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.

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

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.

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 (4rdj). 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.