Specifying FULLSELECT statements in the SQL builder

A FULLSELECT statement composes results from subqueries and sets of values to form a results table.

Prerequisite: Before you specify a FULLSELECT statement, create the statement in the SQL builder.

When the FULLSELECT is open in the SQL builder, the Outline view shows the statements and clauses that comprise the FULLSELECT statement. The SELECT statement at the root node in the Outline represents the overall FULLSELECT statement. If you add subordinate FULLSELECT statements, they are represented by UNION nodes. Click statements and clauses in the Outline view to edit them in the SQL builder.

To specify a FULLSELECT statement in the SQL builder:

Optional: To add a WITH statement:
1. Right-click the root SELECT statement in the Outline view, and then click Add Common Table Expression (WITH) on the pop-up menu.
2. Click the WITH node in the Outline view, and then build the WITH statement in the SQL builder.
Optional: To convert a SELECT statement to a FULLSELECT statement, right-click the statement in the Outline view, and then click Convert to FULLSELECT (UNION) on the pop-up menu.
Optional: To add a VALUES clause:
1. Under the UNION node to which you want to add the clause, delete one of the SELECT statements. Right-click the SELECT statement in the Outline view, and then click Delete on the pop-up menu.
2. Right-click the UNION node in the Outline view, and then click Add Values on the pop-up menu.
3. For each values row that you want to add to the VALUES clause, right-click the VALUES node in the Outline view, and then click Add Values Row on the pop-up menu.
4. For each added values row, click its node in the Outline view, and then specify the values row items in the Design pane of the SQL builder. You can either enter the value directly or use the Expression Builder wizard to build an expression.
For each of the subordinate SELECT statements, click the statement in the Outline view, and then build the SELECT statement in the SQL builder.
To specify the order of the rows in the results table, create an ORDER BY clause that sorts on either a single column or on multiple columns.
Run the statement. The output is shown in the Data Output view.