A FULLSELECT statement composes results from subqueries
and sets of values to form a results table.
Before you begin
Before you specify a FULLSELECT statement, create the statement in the SQL Query
Builder.
When the FULLSELECT is open in the SQL Query 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
Query Builder.
Procedure
To specify a FULLSELECT statement in the SQL Query Builder:
- Optional: To add a WITH statement:
- Right-click the root SELECT statement in the Outline
view, and then click Add Common Table Expression (WITH) on
the pop-up menu.
- Click the WITH node in the Outline view, and then build the WITH statement in the SQL Query
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:
- 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.
- Right-click the UNION node in the Outline view, and
then click Add Values on the pop-up menu.
- 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.
- 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 Query 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 Query 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 SQL Results view.