SQL Builder

The Create A New SQL Statement wizard and the SQL Builder both provide a visual interface for creating and running SQL statements. You can use the wizard to create a simple SQL statement, or you can use the SQL Builder, which supports a wider range of statements.

See Create A New SQL Statement wizard for details about what kind of statements can be created using the SQL Builder and the Create A New SQL Statement wizard. Statements generated by these tools are saved in a file with the extension .sqx.

The SQL Builder contains a number of panes and tabs. The options available depend on the type of statement that you create. The following screen capture shows the basic layout of the SQL Builder.

To open the SQL Builder, you can either create a new statement or double-click an existing statement in the Data Definition view.

The builder is composed of three main sections:

• SQL Source pane

• Tables pane

• Design pane

 

SQL Source pane

The top pane contains the source code of the SQL statement. You can type your SQL statement directly in this pane, or use the features provided by the tool to build your statement. Content assist is available from the pop-up menu of the SQL Source pane. If you modify your statement in this pane, the statement syntax is checked and the interface is updated when you switch the focus to another pane.

Note: SQL comments (lines preceded by a double dash: --) are not supported in the SQL Builder. However, they are tolerated. After you save the SQL statement, all of the sections of the SQL Builder except the SQL Source pane are unavailable. You can continue to use the SQL Source pane to edit the SQL statement and save it. If you remove the comments and save the statement, all of the sections of the SQL Builder are available again.

 

Tables pane

The Tables pane provides a visual representation of the tables or views used in your statement. In this pane you can add or remove a table, give a table an alias, and select or exclude columns from the table. When building a SELECT statement, you can also define joins between tables in this pane.

Note: If you rearrange the table graphics in the Tables pane and then save the SQL statement, the arrangement of the table graphics reverts back to the linear format and the order of the table graphics reverts to their order in the SQL statement.

 

Design pane

The options in the Design pane vary, depending on the type of statement that you are creating. When there is more than one set of options available, they appear as notebook pages. For example, for a SELECT statement, some of the options include: selecting columns, creating conditions, creating groups, and creating group conditions. This is the bottom section in the image above.

 

Other helpful features

You can open the Expression Builder wizard, which guides you through the creation of complex expressions, from anywhere in the SQL Builder where an expression can be entered.

The Outline view, which is to the right of the SQL Builder in the default Data perspective, shows the components of a WITH or FULLSELECT statement.

 

What you can do with the SQL statement that you create

When you are finished with the SQL Builder, you have created a simple SQL statement that you can run. You can also perform the following tasks with the statement:

• Generate an XML file that can be used with the XMLtoSQL class and run dynamically to update tables.

• Generate a DADX file that can be used by the DB2 XML Extender to compose or decompose data.

• Generate a Java bean from a statement that you can use to run the statement.

• Use it in a relational database-to-XML mapping. The RDB-to-XML mapping editor can be used to map the result set from an SQL statement to an XML file. This mapping can be used to generate a DAD file.

Refer to the related tasks for more details.

 

Database vendors supported by the SQL Builder

For a list of supported database vendors, see Supported database vendors

The SQL documentation was written based on the options available when used with the IBM DB2 Universal Database. If you are using a different database product, you may notice some differences in the behavior of the tool or the options that are available. The creation of a WITH statement, for example, is only available in DB2.

Opening the SQL Builder
The Create A New SQL Statement wizard and the SQL Builder both provide a visual interface for creating and executing SQL statements. You can use the wizard to create a simple SQL statement, or you can use the SQL Builder, which supports a wider range of statements. Statements generated by these tools are saved in a file with the extension .sqx. For more information, refer to the related information.

Working with unsupported or invalid code in the SQL Builder
The SQL Builder supports saving and loading unrecognized syntax. When the parser encounters unrecognized syntax, parts of the SQL Builder become unavailable. After you have fixed the syntax, all parts are available again. You have various options to fix the syntax, including reverting to the last known proper source and fixing the code manually.

 

Parent topic

SELECT, 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 concepts

Create A New SQL Statement wizard

 

Related tasks

Editing an SQL statement
Opening the SQL Builder
Generating DADX files
Generating XML from SQL using a wizard