Creating a FULLSELECT statement

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

Composition can be specified using INTERSECT, UNION, EXCEPT, INTERSECT ALL, UNION ALL and EXCEPT ALL operators.

• For DB2, all the operators as well as values clauses are supported.

• For Sybase, only UNION and UNION ALL operators are supported.

• For Oracle, only the UNION, UNION ALL , MINUS and INTERSECT operators are supported.

• For Cloudscape, only the UNION and UNION ALL operators, as well as values clauses, are supported.

For more information on these operators, see the SQL reference guide for your database product.

A FULLSELECT statement can be nested as a subquery in other SQL statements. A FULLSELECT statement used to retrieve a single value as an expression within a statement is called a scalar fullselect or scalar subquery.

A VALUES clause specifies rows of values, using simple or complex expressions. One or more values rows can be specified in a VALUES clause. Each VALUES row consists of values for each column of a row in the result table for the FULLSELECT. Depending on the composition operator selected, the values may or may not be included in the result table.

Note that ORDER BY clauses are not allowed on any SELECT statements within a FULLSELECT statement, but an ORDER BY clause can be specified on the overall FULLSELECT statement.

You cannot create a FULLSELECT statement with the Create A New SQL Statement wizard; you can only create it with the SQL Builder.

To create a FULLSELECT statement:

1. Switch to the Data Definition view in the Data perspective.

2. Expand the project or folder that contains your database, and then expand the database nodes until you see the Statements folder.

3. Right-click the Statements folder, and then click New > FullSelect Statement on the pop-up menu.

4. Type a name for the statement and then click OK.

5. Right-click the FULLSELECT statement in the Outline view to add statements and VALUES clauses to your FULLSELECT statement

6. In the Outline view, expand or collapse the nodes in the FULLSELECT statement to see the structure of the statement. Click the statements and clauses to edit them.

7. If you add a SELECT or FULLSELECT, complete the statement.

8. If you add a VALUES clause, expand the FULLSELECT statement in the Outline view, right-click the added VALUES clause, and then click Add Value Row on the pop-up menu.

9. Enter the values for the value row in the Values Row Item list in the SQL Builder. You can either enter the value directly or use the Expression Builder wizard to build an expression.

10. Add other SQL statements, VALUES rows, or VALUES clauses as necessary.

11. When you are finished, select SQL > Execute to execute the statement. The output is shown in the DB Output view.

Creating an ORDER BY clause in a FULLSELECT statement
The ORDER BY clause specifies the order of the rows of a results table for an SQL statement.

 

Parent topic

UPDATE, INSERT and DELETE statements. The SQL Builder also supports WITH (DB2 only) and FULLSELECT statements. Any statement that you create is stored in a file with the extension .sqx.">Creating an SQL statement

 

Related tasks

Editing an SQL statement
Executing an SQL statement