|Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-01
Expressions, 14 of 14
An expression list is a combination of other expressions.
Expression lists can appear in comparison and membership conditions and in
BY clauses of queries and subqueries.
Comparison and membership conditions appear in the conditions of
WHERE clauses. They can contain either one or more comma-delimited expressions, or one or more sets of expressions where each set contains one or more comma-delimited expressions. In the latter case (multiple sets of expressions):
INkeyword in the membership condition.
A comma-delimited list of expressions can contain no more than 1000 expressions. A comma-delimited list of sets of expressions can contain any number of sets, but each set can contain no more than 1000 expressions.
The following are some valid expression lists in conditions:
(10, 20, 40) ('SCOTT', 'BLAKE', 'TAYLOR') ( ('Guy', 'Himuro', 'GHIMURO'),('Karen', 'Colmenares', 'KCOLMENA') )
In the third example, the number of expressions in each set must equal the number of expressions in the first part of the condition. For example:
SELECT * FROM employees WHERE (first_name, last_name, email) IN (('Guy', 'Himuro', 'GHIMURO'),('Karen', 'Colmenares', 'KCOLMENA'))
In a simple
BY clause, you can use either the upper or lower form of expression list:
SELECT department_id, MIN(salary), MAX(salary) FROM employees GROUP BY department_id, salary; SELECT department_id, MIN(salary), MAX(salary) FROM employees GROUP BY (department_id, salary);
SETS clauses of
BY clauses, you can combine individual expressions with sets of expressions in the same expression list. The following example shows several valid grouping sets expression lists in one SQL statement:
SELECT prod_category, prod_subcategory, country_id, cust_city, count(*) FROM products, sales, customers WHERE sales.prod_id = products.prod_id AND sales.cust_id=customers.cust_id AND sales.time_id = '01-oct-00' AND customers.cust_year_of_birth BETWEEN 1960 and 1970 GROUP BY GROUPING SETS ( (prod_category, prod_subcategory, country_id, cust_city), (prod_category, prod_subcategory, country_id), (prod_category, prod_subcategory), country_id );