DB2 Server for VSE & VM: Interactive SQL Guide and Reference

Interactive Select

interactive-select-statement >>-| fullselect |--+----------------------------------------------+-> | .-,-----------------------------. | | V .-ASC--. | | '-ORDER BY------+-column_name-+---+------+--+--' '-integer-----' '-DESC-' >----+---------------+----------------------------------------->< '-WITH--+-RR-+--' +-CS-+ '-UR-' fullselect |--+-subselect----+---------------------------------------------> '-(fullselect)-' .------------------------------------------. V | >--------+------------------------------------+--+--------------| '--+-UNION-----+---+-subselect----+--' '-UNION ALL-' '-(fullselect)-' subselect .-ALL------. |--SELECT----+----------+---+-*----------------------------+----> '-DISTINCT-' | .-,-----------------------. | | V | | '----+-expression---------+--+-' +-table_name.*-------+ +-view_name.*--------+ '-correlation_name.*-' .-,-----------------------------------------. V | >----FROM------+-table_name-+---+------------------+---+--------> '-view_name--' '-correlation_name-' >-----+--------------------------+------------------------------> '-WHERE--search_condition--' >-----+-------------------------------+-------------------------> | .-,--------------. | | V | | '-GROUP BY-----column_name---+--' >-----+---------------------------+-----------------------------| '-HAVING--search_condition--'

The interactive-select statement retrieves data from a table or view. The data retrieved can only be viewed; you cannot change it. For more information about the terminology used in this diagram, refer to the DB2 Server for VSE & VM SQL Reference manual.

If a SELECT statement contains a placeholder in the WHERE, GROUP BY, ORDER BY, or HAVING clauses, FORMAT information is saved until the next SELECT statement is entered. Formatting information is saved permanently if the SELECT statement is stored. However, if the SELECT or FROM clauses contain placeholders or parameters, FORMAT information is not saved. In addition, FORMAT information is not saved if you change any data in the SELECT or FROM clauses by using the CHANGE command.

When you enter a SELECT statement from a terminal, the row length of the information you can see at one time is limited by the terminal screen size.

The number of bytes per row includes the bytes used as column separators.

UNION

combines two or more queries into a single query by merging the rows returned by each query. Duplicate rows are eliminated.

ALL

combines the results of two or more queries without eliminating the duplicate rows.

ALL

specifies that duplicate values are to be selected. If ALL is specified when you select all department numbers from the PROJECT table, each and every department number listed in the department number column is selected. This is the default.

DISTINCT

specifies that duplicate values are not to be selected. DISTINCT can be used only once for each SELECT statement. You can use it to eliminate duplicates from the SELECT result:

   select distinct deptno from project

You can also use it to eliminate duplicates from a column function:

   select count (distinct deptno) from project

If you specify DISTINCT, the maximum number of columns you can put in the expression list is 16. In addition, the length of the encoded key derived from the expression list must not exceed 255 characters. That is, the sum of the lengths of the columns in the expression list, plus approximately 25% of the lengths of those columns that are of varying-length character type, must not exceed 255 EBCDIC or 127 DBCS characters.

*

indicates that the data in all the columns is to be selected.

Note:

If the table contains more than 45 columns, only the first 45 columns will be displayed. To see the other columns in the table, you can create a view. A view may contain no more than 140 columns. If more columns are needed, you can create additional views.

expression

is a definition of the data desired. An expression may be a column name, a constant, a character expression, a special register, a column function, a scalar function, an arithmetic expression, or a labeled duration.

You can specify a list of expressions, separating each with a comma, and the items are retrieved in the same left-to-right order as they appear in the list. The value of USER is interpreted as a CHAR(8) string whose value is the user ID of the user currently connected. The maximum number of columns that you can specify in the list is 45.

You can use the following operators to connect numeric data types:

+

(plus, add)

-

(minus, subtract)

*

(times, multiply)

/

(divided by)

If you use these operators for numeric data types, see the DB2 Server for VSE & VM SQL Reference manual for information about data conversion.

You can use the concatenation operator, CONCAT, to join two or more compatible operands to form a string. An operand may be a column, a name, a constant, or an expression.

An operand can be the result of an expression. For example, if the USER special register is used, it is treated as CHAR(8). If the CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP, or CURRENT TIMEZONE special registers are used, the values are treated as the character representation of the value in the format defined by the system. A datetime value can be concatenated with a character string because the datetime data types are compatible with character data types.

When varying-length operands are concatenated, only the actual length of the operand is concatenated.

The result of the expression is the concatenation of the operand expressions. The resulting data type is null if either operand is nullable. The resulting data type is character if both operands are CHARACTER. If both operands are GRAPHIC, the result data type is GRAPHIC. If both operands are fixed length, the resulting data type is fixed length. If either operand is varying length, the resulting data type is a varying length string. The associated defined length is the sum of the defined lengths not exceeding 254 bytes.

In the following example, the employee number and the first name of the employee are concatenated with a hyphen between them:

   select empno concat '-' concat
   firstnme from employee

If a column function is used in an expression, all expressions in the list must contain a column function unless grouping is being performed. An example is:

   select min(edlevel),avg(bonus) from employee

table_name.*

view_name.*

correlation_name.*

identifies the table or view to which the column belongs. The asterisk (*) can be replaced with a column name. These prefixes are especially useful for differentiating columns that have the same name, but that belong to different tables or views. The correlation_name can be used to simplify a query, or to join a table to itself. See "correlation_name" below for more information.

FROM table_name

FROM view_name

identifies the table or view from which data is to be selected.

table_name

view_name

is the name of the table or view.

You can further qualify the table or view by specifying the owner of the table or view. You must separate the owner's name from the table name or view name with a period. The owner's name is unnecessary for tables or views that you own. You must have the SELECT privilege or DBA authority to select information from tables or views owned by other users.

correlation_name

is the name you define as an alternative name for the table or view to be selected. It can be any string up to 18 characters long, and must begin with a letter.

WHERE search_condition

is one or more conditions to apply in selecting data.

GROUP BY column_name

A query can have the column functions SUM,

AVG, MAX, MIN, and COUNT applied to groups of rows that have matching values in a column. Rows can also be grouped by matching values in more than one column. The definition of the groups is specified with a GROUP BY clause.

column_name

is the name of one or more columns, separated by commas, to be used when forming a group.

For example, the maximum, minimum, and average activity staff for each project in the PROJ_ACT table could be selected by the following query:

   select projno,max(acstaff),min(acstaff),avg(acstaff) -
   from proj_act -
   group by projno

When a query uses the grouping feature, it returns only one result row for each group. Therefore, the items selected by such a query must be properties of the groups, not properties of individual rows. The expression list may contain columns that are also in the GROUP BY clause together with column functions on any columns. It may not contain any non-grouped column without a column function. If the column function COUNT(*) is used, it evaluates to the number of rows in the group.

If any rows have a null value in a grouped column, ISQL groups the null values in those columns together. The null values returned may be due either to unknown column values or to arithmetic exception errors.

A grouping query may have a standard WHERE clause that serves as a filter, keeping only those rows which satisfy the search_condition. The WHERE clause filters out the non-qualifying rows before the groups are formed and the column functions are computed.

The following example query finds the average and minimum activity staff for each project, considering only activities whose starting date is 1 January 1982:

   select projno,avg(acstaff),min(acstaff) -
   from proj_act -
   where acstdate = '1982-01-01' -
   group by projno

HAVING search_condition

is one or more conditions that apply to groups. ISQL returns a result only for those groups that satisfy the condition. The HAVING clause may contain one or more group-qualifying conditions connected by ANDs and ORs. Each group-qualifying condition compares some property of the group, such as AVG(ACSTAFF), with another group property or with a constant.

The following example query lists the maximum and minimum activity staff for various projects in the PROJ_ACT table, considering only projects that have more than three activities:

   select projno,max(acstaff),min(acstaff) -
   from proj_act -
   group by projno -
   having count(*) > 3

One of the functions in a HAVING clause may specify DISTINCT (for example, COUNT(DISTINCT PROJNO)). However, DISTINCT may be used only once in a query. It may not be used in both the expression list and the HAVING clause.

It is possible, though unusual, for a query to contain a HAVING clause but no GROUP BY clause. In this case, the entire table is treated as one group. If the HAVING condition is true for the table as a whole, the selected result, which must consist entirely of column functions, is returned.

ORDER BY

orders, or sorts, the rows to be retrieved by the column(s) specified. A maximum of 16 columns may be specified in the ORDER BY clause.

column_name

refers to a column name in the expression list. For example, to order a query primarily by the values in the ACTNO column and secondarily by the values in the PROJNO column, you would type:

   select actno,projno,acstdate,acendate -
   from proj_act -
   order by actno,projno

integer

refers to the items in the expression list. For example, to order a query primarily by the first item and secondarily by the third item of the list, you would type:

   select projno,acstdate,acendate,acstaff -
   from proj_act -
   order by 1,3

These items may be columns or more complex expressions such as BONUS+COMM.

ASC

DESC

indicates the order in which results are returned: either ascending (ASC) or descending (DESC). The default is ASC. For example, to indicate ascending order on item 3 and descending order on item 5, you could type:

   order by 3,5 desc

because ASC is the default.

In contrast, to indicate descending order on item 3 and item 5, you would type:

   order by 3 desc,5 desc

Blanks sort first in ascending order, last in descending order, and are ignored if the data type is VARCHAR or VARGRAPHIC.