Creating SQL statements
You can create an SQL statement using either the SQL query builder or the SQL query wizard. Both tools support the creation of SELECT, UPDATE, INSERT and DELETE statements. The SQL query builder also supports WITH (DB2 only) and FULLSELECT statements. Any statement you create is stored in a file with the extension .sqx.
To create an SQL statement using the SQL query builder:
Switch to the Data perspective.
Switch to the Data Definition view by selecting the Data Definition tab.
Expand the project or folder that contains your database, then expand the database until you see the Statements folder.
Right-click the Statements folder.
Click New > and the type of statement you wish to create.
Enter a name for your statement and click OK.
Your statement will be added to the Statements folder and will open in the SQL query builder. Complete the statement using the SQL query builder:
SQL query builder
The SQL query wizard and SQL query builder both provide a visual interface for creating and executing SQL statements. You can create a simple query using the SQL query wizard, or you can use the SQL query builder that supports a wider range of statements. Statements generated by these tools are saved in a file with the extension .sqx.
The SQL query builder contains a number of panes and tabs. The options available depend on the type of statement you create. The following screen capture shows the basic layout of the SQL query builder:
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 query directly in this pane, or use the features provided by the tool to build your query. Content assist is available from the pop-up menu of the SQL source pane. If you modify your query in this pane, the statement syntax will be checked and the interface will be updated when you switch the focus to another pane.
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.
Design pane
The options in the design pane will vary depending on the type of statement that you are creating. When there is more than one set of options available, they will 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.
Other helpful features
An expression builder wizard that guides you through the creation of complex expressions can be launched from anywhere in the SQL query builder where an expression can be entered.
The Outline view, which is to the right of the SQL query builder in the default Data perspective, shows the components of a WITH or FULLSELECT statement.
Building expressions
Using the Expression Builder wizard you can build complex expressions or subqueries. An expression specifies a value. It can be a simple value, consisting of only a constant or a column name, or it can be more complex.
The types of expressions that can be built using the Expression Builder wizard are:
Function
A function is an operation that is denoted by a function name followed by a pair of parentheses enclosing the specification of zero or more arguments. A function returns a value.
Functions are classified as column functions, scalar functions, row functions or table functions.
The argument of a column function is a collection of like values (a column). The function returns a single value (possibly null), and can be specified in an SQL statement where an expression can be used.
The argument(s) of a scalar function are individual scalar values, which can be of different types. The function returns a single value (possibly null), and can be specified in an SQL statement wherever an expression can be used.
The argument of a row function is a structured type. The function returns a row of built-in data types and can only be specified as a transform function for a structured type.
The argument(s) of a table function are individual scalar values, which can be of different types. The function returns a table, and can be specified only within the FROM clause of a SELECT statement.
CASE - Search or Simple type
CASE expressions allow an expression to be selected based on the evaluation of one or more conditions. A CASE expression contains one or more when clauses of either Search or Simple type. A Search type CASE expression has no expression following the CASE keyword and each of its when clauses is a condition that is evaluated such as i<8. A Simple type CASE expression has an expression following the CASE keyword. The expression is compared with the expression following each when clause. The value of the case-expression is the value of the result-expression following the first when clause in the case-expression that evaluates to true. If a when clause does not evaluate to true, the else clause determines the value of the case-expression.
Cast function
A function used to convert instances of a data type (origin) into instances of a different data type (target). A cast function takes the following form CAST(expr AS datatype). The result of the expression expr is converted to the type datatype. For example, CAST (XMLTEST.EMPLOYEE.SALARY AS INTEGER) converts the values in the column SALARY to type integer.
Constant
A constant specifies a value. A constant can be a string or a number. Numeric constants can be an integer, floating-point, or decimal. A string constant can be a character string constant, a hexadecimal constant or a host variable name. A host variable name is denoted by a name preceded by a colon such as :var and it is replaced by a value when the statement is executed.
Subquery
A subquery is a SELECT, WITH, or FULLSELECT statement nested within another SQL statement. The expression value is the result of the subquery.
Build up expressions by operators
An expression can be built by applying operators to columns or expressions such as x + y where x is a column and y is an expression.
Using host variables in an SQL statement
Host variables can be used in an SQL statement to represent a value that will be substituted in at execution time. The host variable syntax is a symbol followed by a string. For example, :companyname. The symbol used depends on the what syntax your database vendor uses to denote variables.
For Sybase, SQLServer and MySQL, you would use @NAME where NAME is the name of the host variable.
For all others database vendors, you would use :NAME where NAME is the name of the host variable.
You can use a host variable in your SQL statement in place of an expression. The Expression Builder can be used to create a host variable or you can simply type a host variable in place of an expression in the SQL query builder views.
When you execute your statement in the SQL builder you will be prompted to substitute a value of the appropriate type for the host variable.
