Using Visual SQL to Edit a Procedural Object

Visual SQL aids you in creating select, insert, update, and delete statements for use in your procedural objects.

To create a SQL statement using Visual SQL:

Open a function, stored procedure, trigger, or event in the SQL Editor (see Opening a Procedural Object).
Move the cursor to the desired location in SQL Editor.
Using the context menu, select Visual SQL.

Select one of the four types of SQL statements: select, insert, update, delete. The Select Table dialog appears.

Visual SQL Statements

Statement	Procedure
select	Choose one or more tables. Click Open. A list of columns for each selected table appears. Choose those columns that you want selected. You can define Where/Having conditions, choose Sort columns, add Compute columns, and so on (see below).
insert	Select a table into which you want values inserted. Click Open. An Insert Column Values dialog appears. In the appropriate column fields, enter the column values you want to insert. You can type in literal values, choose arguments from SQL Editor, or click the select button to invoke Visual SQL again and create a select statement to fill in insert column values. Click OK to close the Insert Column Values dialog.
update	Choose a table that you want to update. Click Open. The Update Column Values dialog displays. Choose the columns you want updated from the list of columns below and enter values in the Value field. You can enter literal values, select arguments from SQL Editor, or build expressions using the logical operator buttons. Click OK to close Update Column Values. You can proceed to define any Where conditions to specify the rows affected by the update.
delete	Choose a table. Click Open. Using the menus on the Where tab, define one or more conditions that identify the rows to be deleted.

While developing your SQL statement, the SQL code to be generated appears under the Preview tab in the the lower half of Visual SQL. You can still add clauses to the statement (see step 5) by using the tabs under the bottom half of the Visual SQL dialog. When you close Visual SQL, the code is inserted into SQL Editor.

You can create a more complex SQL statement by adding and nesting clauses. Other tabs representing different clauses of a SQL statement appear at the bottom of the lower view of Visual SQL. The Where clause applies to select, update, and delete statements. All other clauses apply only to select statements.

Clauses available in Visual SQL

Clause	Description
Where	Determines the rows to be affected for select, update, and delete statements.
Sort	Allows you to sort query results by columns in either ascending or descending order.
Group	Organizes query results into groups based on repeating values in grouping columns.
Having	Used to display or reject rows defined by the group clause, excluding rows that do not meet the search conditions of that clause.
Compute	Allows you to define additional result columns using expressions; when used with a group clause, these expressions may calculate summary values.

For example, selecting the Where tab displays input fields containing Column, Operator, Value, and Logical columns. Click the right end of a Column, Operator, or Logical column to display the drop-down list that shows the available options from which to select. Enter the desired value in the Value column. If there are existing local variables or parameters, you can right-click the Value column and select Argument to choose an existing variable or parameter.

Drop-down lists are available for columns and operators for the Where and Having clauses. Once you have entered a value into the Values column, context menus provide the following options for the Where, Having, and Compute clauses:

Option	Description
Columns	Menu of columns to paste.
Functions	Database-specific functions.
Arguments	Parameters and variables from SQL Editor.
Value	List of column values (enabled only if a column is specified).
Select	Invokes Visual SQL again so you can define a subquery or nested select.
Messaging (ASE)	Invokes Messaging wizard; helps you define a function, which can be used like any other database-specific function, by itself or as part of an expression.

From the context menus, you can also clear a clause of its current values by choosing the Clear option, or you can insert or delete a clause by choosing Insert Clause or Delete Clause, respectively.

Click OK to close Visual SQL and return to SQL Editor.