4.4.1 Specification format and rules for the SELECT statement

(1) Specification format

SELECT-statement ::= query-expression [ORDER-BY-clause] [LIMIT-clause]
 
  ORDER-BY-clause ::=  ORDER BY sort-specification-list

A SELECT statement consists of a query expression followed by clauses (an ORDER BY clause or a LIMIT clause). The configuration of an example SELECT statement is illustrated in the following figure.

Figure 4‒1: Configuration of an example SELECT statement

For details about how to retrieve rows using a SELECT statement, see the following sections in the HADB Application Development Guide:

• Using the JDBC driver: How to retrieve data in Retrieving data (executing the SELECT statement)

• Using CLI functions: Referencing data in How to use the CLI functions

To Page Top

(2) Explanation of specification format

query-expression

Specifies a query expression. For details about query expressions, see 7.1 Query expression.

Specify a query specification, or specify a query expression to find the union of tables derived by query specifications.

ORDER-BY-clause

ORDER-BY-clause ::= ORDER BY sort-specification-list

Specify if you want to sort the results of the query expression in ascending or descending order. If the ORDER BY clause is omitted, the results of the query expression are not sorted in ascending or descending order.

The sort specification list specifies the sort keys and the sorting order of the results. For details about the sort specification list, see 7.24 Sort specification list.

Note the following points:

• When the sort key is character string data, results are sorted in sort code order or bytecode order according to the sort order for character string data specified in the server definition, client definition, or connection attributes.

• When character string data is sorted in sort code order, it is sorted using the ISO/IEC 14651:2011 standard sort codes <S0000> to <S2FFFF> and subcodes <T0000> to <TFFFF> assigned to each character. Characters not assigned a sort code are sorted relative to each other in bytecode order.

• When character string data is sorted in sort code order, it is sorted as Unicode (UTF-8) bit patterns, with illegal characters treated as one-byte characters and returned at the end of the result.

• If an ORDER BY clause is specified, a work table might be created. If the size of the work table DB area where the work table will 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.

LIMIT-clause

Specifies the maximum number of rows to be retrieved from the results of the query expression.

For details about the LIMIT clause, see 7.9 LIMIT clause.

To Page Top

(3) Privileges required at execution

To execute the SELECT statement, both of the following privileges are required:

• The CONNECT privilege

• The SELECT privilege on all of the tables specified in the query specification of the SELECT statement

To Page Top

(4) Rules

1. The total number of tables, derived tables, and table function derived tables specified in all table references in a SELECT statement cannot exceed 2,048. However, if the SQL statement includes the following items, the total number check is performed for the SQL statement after those items are equivalently exchanged into internal derived tables:

   • Viewed tables

     If a viewed table is specified in a CREATE VIEW statement, the total number check is performed after the viewed table specified in the CREATE VIEW statement is equivalently exchanged into a derived table.

   • Query name

   • Archivable multi-chunk table

   Note

   For details about equivalent exchange of archivable multi-chunk tables, see Equivalent exchange of SQL statements that search archivable multi-chunk tables in the HADB Application Development Guide.

   The following shows an example of counting the number of tables, derived tables, and table function derived tables specified in an SQL statement.

   Example

   WITH "Q1" AS (SELECT * FROM "T6","T7")
   SELECT * FROM
     "T1",                                                ...[a]
     "T2" LEFT OUTER JOIN "T3" ON "T2"."C1"="T3"."C1",    ...[b]
     (SELECT * FROM "T4","T5") W1,                        ...[c]
     "Q1",                                                ...[d]
     TABLE(ADB_CSVREAD(MULTISET['/tmp/data.gz'],'COMPRESSION_FORMAT=GZIP;'))
     AS W2 ("C1" INTEGER)                                 ...[e]
     "V1",                                                ...[f]
     "V2",                                                ...[g]
     "T001"                                               ...[h]

   [Explanation]

   1. A table (T1) is specified. Here, therefore, the number of specified tables is 1.

   2. A joined table consisting of tables T2 and T3 is specified. Here, therefore, the number of specified tables is 2 (the total number of tables specified for the joined table).

   3. A derived table is specified, and the derived query for this derived table includes two tables (T4 and T5). Here, therefore, the number of specified tables is 3 in total.

   4. A query name is specified. The query name is equivalently exchanged into a derived table, and the derived query for this derived table includes two tables (T6 and T7). Here, therefore, the number of specified tables is 3 in total.

   5. A table function derived table is specified. Here, therefore, the number of specified tables is 1.

   6. A viewed table (V1) is specified. The viewed table is equivalently exchanged into a derived table, and the derived query for this derived table includes two tables (T8 and T9). Here, therefore, the number of specified tables is 3 in total.

   7. A viewed table (V2) is specified. The viewed table is equivalently exchanged into a derived table. The derived query for this derived table includes a viewed table (V1), which is equivalently exchanged into a derived table. The derived query for this derived table includes two tables (T8 and T9). Here, therefore, the number of specified tables is 4 in total.

   8. An archivable multi-chunk table is specified. The archivable multi-chunk table is equivalently exchanged into a derived table. The derived query for this derived table includes four tables. Here, therefore, the number of specified tables is 5 in total. T001 is the archivable multi-chunk table that is equivalently exchanged into a derived table.

   In the case of the preceding example, the total number of tables, derived tables, and table function derived tables specified in the SQL statement is 22.

   Note that V1 and V2 are viewed tables that are defined in the following CREATE VIEW statements:

   CREATE VIEW "V1" AS SELECT * FROM "T8","T9"
   CREATE VIEW "V2" AS SELECT * FROM "V1"

2. If the set operations specified in the SELECT statement are all UNION, a maximum of 1,023 set operations can be specified. However, if the specified set operations include EXCEPT or INTERSECT, no more than 63 set operations can be specified.

3. A maximum of 63 outer joins (FULL OUTER JOIN) can be specified in the SELECT statement.

4. The names of the query expression result columns and derived columns are called retrieval item column names . When a query expression result column or derived column has no name (the length of the column name is 0), its retrieval item column name is set as follows:

   EXPnnnn_NO_NAME

   Legend: nnnn: Unsigned integer in the range from 0001 to 1000

   Example:

   SELECT "C1",MAX("C2"),MIN("C2")
       FROM "T1" GROUP BY "C1"

   When the preceding SELECT statement is executed, the retrieval item column names will be C1, EXP0001_NO_NAME, and EXP0002_NO_NAME.

5. Note that when you search an archivable multi-chunk table, you must consider the specification of search conditions in the SELECT statement. For details, see Considerations when searching an archivable multi-chunk table in the HADB Application Development Guide. Make sure that you read the preceding section when you specify a SELECT statement that searches an archivable multi-chunk table.

To Page Top

(5) Examples

Example 1

From the sales history table (SALESLIST), retrieve the customer ID (USERID), product code (PUR-CODE), and date of purchase (PUR-DATE) for customers who purchased product code P002 on or after September 6, 2013.

SELECT "USERID","PUR-CODE","PUR-DATE"
    FROM "SALESLIST"
        WHERE "PUR-DATE">=DATE'2013-09-06'
        AND "PUR-CODE"='P002'

Example 2

From the employee table (EMPLIST), determine the average age (AGE) of the employees in each section (SCODE).

SELECT "SCODE",AVG("AGE")
    FROM "EMPLIST"
        GROUP BY "SCODE"

Several basic examples of the SELECT statement are shown in 1. SELECT Statement Examples. See also this chapter.

For an example of the SELECT statement in which the ORDER BY clause is specified, see (1) Examples of specifying a sort specification list in an ORDER BY clause in 7.24.4 Examples.

For an example of the SELECT statement in which the LIMIT clause is specified, see (4) Examples in 7.9.1 Specification format and rules for LIMIT clauses.

To Page Top