7.2.1 Specification format and rules for query specifications

(a) {ALL|DISTINCT}

Specifies whether to exclude duplicate rows from the retrieval results.

ALL:

The retrieval results are output as-is, including duplicate rows.

DISTINCT:

If there are duplicate rows in the retrieval results, the retrieval results are output with all duplicates eliminated.

For details about differences in retrieval results when DISTINCT is specified, see 1.10.1 Example: Retrieve customers who purchased products.

Note the following points:

• If DISTINCT is specified, a work table might be created. If the size of the work table DB area where the work table is to be created has not been estimated correctly, it might result in performance degradation. For details about estimating the size of the work table DB area, see the HADB Setup and Operation Guide. For details about work tables, see Considerations when executing an SQL statement that creates work tables in the HADB Application Development Guide.

• If hash execution is used as the method for processing SELECT DISTINCT, a hash table area of an appropriate size is required. The size of the hash table area is specified in the adb_sql_exe_hashtbl_area_size operand in the server definition or client definition. For details about the method for processing SELECT DISTINCT, see Method for processing SELECT DISTINCT in the HADB Application Development Guide.

If neither ALL nor DISTINCT is specified, the system assumes that ALL is specified.

(b) SELECT deduplication method specification

If a SELECT deduplication method specification is used, a processing method other than hash execution is used as the method for processing SELECT DISTINCT. For details about the method for processing SELECT DISTINCT, see Method for processing SELECT DISTINCT in the HADB Application Development Guide.

Note that, normally, there is no need to specify this. If the SELECT deduplication method specification is omitted, HADB determines the method for processing SELECT DISTINCT.

(c) Selection list

selection-list ::= {* | selection-expression[,selection-expression]...}

The selection list specifies the retrieval results to be output.

*:

Specify this to output all columns of the table to be output in the retrieval results.

If * is specified, all columns from all tables specified in the FROM clause will be output in the order in which the tables were specified in the FROM clause. The order of the columns in each table will be the order specified when the table was defined.

selection-expression[, selection-expression]...:

Specifies the retrieval results to be output.

(d) Table expression

The table expression specifies the tables from which output is to be retrieved. You can also specify the conditions for retrieving from the tables (search conditions), and the conditions for selecting groups when performing grouping. For details about table expressions, see 7.4 Table expression.

(e) Selection expression

selection-expression ::= {value-expression [AS-clause]|NULL [AS-clause]|table-specification.*|[table-specification.]ROW}
  AS-clause ::= [AS] column-name

The selection expression specifies the retrieval results to be output.

You cannot specify an external reference column in a selection expression. For details about external reference columns, see (a) Common rules for subqueries in (4) Rules in 7.3.1 Specification format and rules for subqueries.

value-expression [AS-clause]:

Specify the retrieval results to be output in the form of a value expression.

Specify the AS clause if you want to change the column names in the retrieval results.

For details about column names and column ordering in the retrieval results, see 6.9 Derived column names.

Note that for the first query specification (except the query specification in the WITH clause of the SELECT statement), the column name in the AS clause can include a half-width (left or right) parenthesis. For the second and subsequent query specifications, the column name in the AS clause cannot include a half-width parenthesis.

Do not specify a character string in the EXPnnnn_NO_NAME format as a column name in the AS clause. Such a column name might duplicate a derived column name that is automatically set by HADB. In this format, nnnn is an unsigned integer in the range from 0000 to 9999.

■ Notes on specifying the GROUP BY clause, the HAVING clause, or a set function

When you specify the GROUP BY clause, the HAVING clause, or a set function, the column specification included in the value expression in a selection expression must meet any of the following conditions:

• It specifies a grouping column name.

  Example of a correct SQL statement:

  SELECT "C1" FROM "T1" GROUP BY "C1" HAVING "C1">100

  In the preceding example, the grouping column name specified in the GROUP BY clause is specified in the value expression in a selection expression.

• It specifies the argument to a set function.

  Example of a correct SQL statement:

  SELECT COUNT("C2") FROM "T1" HAVING MAX("C1")>100

  In the preceding example, the argument to a set function is a column specification.

  Example of an SQL statement that generates an error:

  SELECT COUNT("C1")+"C1" FROM "T1"

  In the preceding example, a column specification is used in a location other than the argument to a set function.

• It specifies the same value expression as the value expression included in a grouping specification (value expression that includes a column specification).

  Example of a correct SQL statement:

  SELECT "C1"+"C2" FROM "T1" GROUP BY "C1"+"C2"

  In the preceding example, the same value expression that is included in the grouping specification in the GROUP BY clause is specified as the value expression in a selection expression.

  Note that if a column specification having a table specification is specified in a selection expression and the name of the column is the same as an existing grouping column, the grouping column cannot be referenced.

  Example of an SQL statement that generates an error:

  SELECT "T1"."C2" FROM "T1" GROUP BY "C1"+1 AS "C2"

  In the preceding example, because a column specification having a table specification ("T1."C2") is specified in a selection expression, grouping column "C1"+1 AS "C2" cannot be referenced even though the column name is the same. Therefore, the preceding SQL statement will result in an error.

NULL [AS-clause]:

Specify this if you want null values to be output to the retrieval results.

To add column names to the retrieval results, specify the AS clause.

The following rules apply:

• NULL can be specified in the selection expression in the outermost query specification of the SELECT statement.

• NULL cannot be specified in the selection expression of a query specification subject to a set operation.

  Example of an SQL statement that generates an error:

  SELECT NULL FROM "T1" UNION SELECT "C1" FROM "T1"

• NULL cannot be specified in the selection expression of the query specification in the WITH clause.

For the retrieval results, the following rules apply:

• The data type of the result of NULL will be INTEGER.

• The NOT NULL constraint does not apply to the result of NULL (the null value is allowed).

table-specification.*:

If this is specified, the retrieval results will consist of all of the columns in the specified table. The order of columns in the retrieval results will be the same as the order of columns in the specified table.

If this is specified and you want to specify a GROUP BY clause, HAVING clause, or set function in the query specification, specify the column specification in the selection expression as follows:

• Grouping column

[table-specification.]ROW:

If the preceding specification is included, the entire row is retrieved to one area as a single entity. ROW means the entire row.

Regardless of the data types of the columns that make up the row, the ROW that is retrieved is stored in a CHAR type variable. Be sure to remove any leading or trailing spaces. The data length of the retrieved row is its row length (sum of the data lengths of the columns that make up the row). For details about how to calculate the row length, see the ROWSZ calculation formula in Determining the number of pages for storing each type of row in the HADB Setup and Operation Guide.

The rules for specifying ROW are as follows:

• It can be specified only for a FIX table.

• If ROW is specified, none of the following can be specified in the query specification:

  • Set function

  • Window function

  • GROUP BY clause

  • SELECT DISTINCT

  • Set operation

• If you specify LEFT OUTER JOIN as the joined table mode in a FROM clause, you cannot specify ROW for the table on the right.

• If you specify RIGHT OUTER JOIN as the joined table mode in a FROM clause, you cannot specify ROW for the table on the left.

• If you specify FULL OUTER JOIN as the joined table mode in a FROM clause, you cannot specify ROW for the table on either side.

• You cannot specify ROW in a query specification in a WITH clause.