Writing SQL Statements

Writing SQL Statements

The Execute Query text box allows you to write not only SELECT and CALL queries, but most SQL statements, including DDL statements such as CREATE TABLE, and DML statements such as INSERT, UPDATE, and DELETE.

You can specify SQL code in the Execute Query text box using the following:

• Type (or paste) the SQL code into the text box. The SQL code area does not colorize SQL text or provide any syntax or existence validation. However, it does provide automatic spelling verification. You can erase the contents of the text box using the X icon.

• Use the Show History list to select a prior SQL statement. The selected statement is copied into the text box. Upon execution, this statement moves to the top of the Show History list. Note that Show History lists all previously executed statements, including those that failed execution.

• Use Table Drag and Drop to construct SQL code in the text box.

• You can use the Query Builder, rather than the Execute Query text box, to specify and execute a SELECT query. A SELECT query executed using Query Builder is not shown in Execute Query or listed in Show History.

SQL code in the Execute Query text box can include:

• ? Input Parameters. If you specify input parameters, such as TOP ? or WHERE Age BETWEEN ? AND ?, the Execute button displays the Enter Parameter Value for Query window, with entry fields for each input parameter in the order specified in the query. For further details on ? input parameters, refer to Executing an SQL Statement.

• Whitespace Characters. You can specify multiple blank spaces, single and multiple line returns. The tab key is disabled; when copying code into the SQL code area, existing tabs are converted to single blank spaces. Line returns and multiple blank spaces are not retained.

• Comments. The SQL code area supports single-line and multiline comments. Comments are retained and shown in the Show History display. Comments are not shown in the Show Plan Statement Text display or in cached queries.

• Queries that Return Multiple Result Sets.

After writing SQL code in the text box, you can click the Show Plan button to check the SQL code without executing the SQL code. If the code is valid, Show Plan displays a Query Plan. If the code is invalid, Show Plan displays an SQLCODE error value and message. You can also use the Show Plan button to display this information for the most-recently-executed SQL code.

To execute the SQL code, click the Execute button.