SQL Query Builder - Query Layout tab

Navigator > Setup > SQL Query Builder > Query Layout tab - OR -

Navigator > Setup > Report Designer > Edit Query (Alt+Q) button > Query Layout tab

See also SQL Query Builder.

Note: Some knowledge of SQL development is recommended for users of the SQL Query Builder.

Use the tools to build a query on the SQL tab. To build a query:

Select one or more tables from the right hand selector, then double-click or drag them into the query Design Window.
Joins between tables are auto-created as tables are placed in the design window. You can join tables by dragging fields between tables. The fields that are joined must contain identical data in both tables or the query will result in no data. To view the Link Options for the join, click the join line, then select Link Options.
Select the fields to appear by selecting the checkbox to the left of the field name. Each field name selected is added to the Query By Example (QBE) grid.
Set the properties for each field in the grid.

Note: Fields for each table are listed in the order of Join fields, followed by all other fields.

Grid Fields

Column

The name of the table and the field, separated by a ".". For example, ARCUST.CustomerCode.

Alias

An alias that can be used to refer to the field.

Show

Selected, the field will be visible in the query results.

Unselected, the field will not be included in the query results.

Sort

The sort order of the records. This determines the ORDERBY function on the SQL tab. To sort by a field, click in this line and select Ascending or Descending. Defaults to no sort order (blank).

Function

You can apply a function to values in the field. If you specify a Function, you must specify a Group.

Average	The average amount of the values in each group.
Count	The number of records in each group.
Maximum	The highest value in each group.
Minimum	The lowest value in each group.
Sum	The total of all the values in each group.

Group

Selected, this field will be grouped, using the Function (above). This determines the GROUP BY function on the SQL tab. Defaults to Clear. See SQL GROUP BY clause.

Criteria

Criteria for filtering records to returned by the query. This determines the WHERE clause on the SQL tab. String values are enclosed by single quotes. See SQL WHERE Clause.

The Query Builder will add all criteria in the Criteria column to the SQL WHERE Clause, with each entry joined with an AND.

Or...

Use these columns to enter any additional Criteria.

The Query Builder builds the Criteria column, then builds each Or... each column separately. The SQL Where Clause built will contain:

criteria in the Criteria column in brackets, then
OR, then
criteria in the first OR... column in brackets, then
OR, then criteria in subsequent OR... columns, and so on.

Only use OR... columns if you have a complete separate set of criteria. If you want to apply an OR operator to a field, Use an OR statement or an IN statement in the Criteria column. See SQL Logical Operators.

For example:

Column	Criteria	Or...
INHEAD.DocumentClass	='I'	'C'
INHEAD.PostStatus	='P'	'U'

Would generate the following where statement:

WHERE (INHEAD.DocumentClass = 'I' AND INHEAD.PostStatus = 'P')

OR (INHEAD.DocumentClass = 'C' AND INHEAD.PostStatus = 'U')

Column	Criteria	Or...
INHEAD.DocumentClass	='I' OR 'C'
INHEAD.PostStatus	='P' OR 'U'

Would generate the following where statement:

WHERE (INHEAD.DocumentClass = 'I' OR INHEAD.DocumentClass = 'C' AND INHEAD.PostStatus = 'P' OR INHEAD.PostStatus = 'U')

Grid Toolbar

Expand Toolbar (Ctrl+F9)

Expand the toolbar to give access to all toolbar options. Press Esc to close the expanded toolbar.

Move (Shift+Up) (Shift+Down)

Select a field name, click to order the list of fields.

Delete (F3)

Remove the selected field from the query.

Customise (Alt+F5)

Opens Customise Fields, you can customise the fields visible in the grid.

In This Section

Link Options