Hitachi

Hitachi Advanced Database Application Development Guide


5.11.7 Equivalent exchange for search conditions in SQL statements that specify derived queries (transposition to the WHERE clause of a derived query)

Equivalent exchange is performed to move a search condition specified in the WHERE clause of an SQL statement that specifies a derived query to the WHERE clause of the derived query. For details about derived queries, see Derived queries and derived query names in the manual HADB SQL Reference.

The following shows examples of equivalent exchange. In the examples, C1, C2, and C3 are column names.

Organization of this subsection

(1) Examples where equivalent exchange is performed

(a) Example 1 (when derived query is a query specification)

[Figure]

Explanation:

The column specification on the left side of the WHERE clause of the SQL statement that specifies the derived query is a derived column ("D1"."C1"). This column is derived based on the selection expression ("T1"."C1") of the query specification in the derived query. Because the conditions for applying equivalent exchange are satisfied, equivalent exchange is applied to the search condition in the SQL statement that specifies the derived query.

(b) Example 2 (when derived query is a query expression)

[Figure]

Explanation:

The column specification on the left side of the WHERE clause of the SQL statement that specifies the derived query is a derived column ("D1"."C1"). This column is derived based on the selection expression ("T1"."C1","T2"."C1") of the query expression in the derived query. Because the conditions for applying equivalent exchange are satisfied, equivalent exchange is applied to the search condition in the SQL statement that specifies the derived query.

(c) Example 3 (when derived query is a query specification)

[Figure]

Explanation:

The column specification in the OR condition of the logical operation specified in the WHERE clause of the SQL statement that specifies the derived query is a derived column ("D1"."C1","D1"."C2","D1"."C3"). This column is derived based on the selection expression ("T1"."C1","T1"."C2","T1"."C3") of the query specification in the derived query. Because the OR condition of the logical operation satisfies the conditions for applying equivalent exchange, equivalent exchange is applied to the search condition in the SQL statement that specifies the derived query.

(d) Example 4 (when derived query is a query expression)

[Figure]

Explanation:

The column specification in the OR condition of the logical operation specified in the WHERE clause of the SQL statement that specifies the derived query is a derived column ("D1"."C1","D1"."C2","D1"."C3"). This column is derived based on the selection expression ("T1"."C1","T1"."C2","T1"."C3","T2"."C1","T2"."C2","T2"."C3") of the query expression in the derived query. Because the OR condition of the logical operation satisfies the conditions for applying equivalent exchange, equivalent exchange is applied to the search condition in the SQL statement that specifies the derived query.

(2) Format of conditions subject to equivalent exchange

The following shows the format of conditions that are subject to equivalent exchange:

(3) Notes