WebSphere V5.0 : Building Expressions

In this tutorial you will learn about, WebSphere V5.0 : SQL Source pane, Tables pane ,Design pane, Other helpful features, Building expressions, Function, CASE - Search or Simple type, Cast function, Constant, Subquery, Build up expressions by operators and Using host variables in an SQL statement

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.