A
FULLSELECT statement composes results from subqueries and sets
of values to form a results table.
Before you begin
Prerequisite: 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.
About this task
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.