Joins and Data order

The Edit query dialog allows you to specify the joins between the tables in a record used for a form, report, or service.

Joins between tables referenced in the form are set up in the query property of the business record.

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.

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.