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.
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.
- 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
- How the data is ordered.
- 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.
ORDER BY clause.