Notes on clauses, columns, and aliases
This topic discusses the xfODBC driver’s support for the following:
- WHERE
- ORDER BY
- GROUP BY
- HAVING
- FROM
- FOR UPDATE OF
- CASE
- UNION
- Computed columns
- Text columns
- Table aliases
- Column aliases
For information on optimizing clauses, see Creating efficient SQL statements, and for information on SKIP and TOP sub-clauses, see SKIP and TOP. For links to more information on xfODBC's support for SQL, see Appendix B: SQL Support.
WHERE
The WHERE clause enables you to specify selection criteria for an SQL command. You can use the following operators with the WHERE clause:
WHERE Clause Operators |
||
---|---|---|
Name |
Operator |
Examples |
Equal to |
= |
SELECT or_number FROM orders WHERE or_customer = 8 |
IS NULL |
SELECT * FROM orders WHERE or_sdate IS NULL |
|
Greater than |
> |
SELECT or_number FROM orders WHERE or_customer > 8 |
Greater than or equal to |
>= |
SELECT or_number FROM orders WHERE or_customer >= 8 |
Less than |
< |
SELECT or_number FROM orders WHERE or_customer < 8 |
Less than or equal to |
<= |
SELECT or_number FROM orders WHERE or_customer <= 8 |
Not equal to |
<> |
SELECT * FROM plants WHERE in_shape <> 'tree' |
IS NOT NULL |
SELECT * FROM orders WHERE or_sdate IS NOT NULL |
For more complex selection criteria, combine multiple WHERE clauses with AND or OR connectors. For example:
SELECT or_number, or_customer FROM orders WHERE or_customer = 8 AND or_odate = '1993-03-07' SELECT or_number, or_customer FROM orders WHERE (or_customer = 16 AND or_odate = '1995-03-03') OR (or_customer = 8 AND or_odate = '1993-03-07')
Note the following:
- A single quote (apostrophe) in column data must be preceded by another single quote. For example, the following is incorrect:
SELECT cust_gift FROM customers WHERE cust_name = 'Troy's Trees'
The next query, however, correctly handles this by adding a second single quote:
SELECT cust_gift FROM customers WHERE cust_name = 'Troy''s Trees'
- You can use up to 512 OR conditions and up to 512 AND conditions:
WHERE condition_1 OR condition_2 ... OR condition_512 WHERE condition_1 AND condition_2 ... AND condition_512
For information on OR and AND clause optimization, see AND and OR clauses.
- When comparing date and time columns, make sure you use the correct format. See Default formats and types for returned dates and times and Masks for dates and times in SQL statements.
Clauses in WHERE clauses
You can also use the following clauses, some of which use subqueries. In the following, exp is short for “expression,” compare_op is short for “comparison operator,” and char_exp, is short for “character expression.”
ALL
exp compare_op ALL subquery
ALL returns true if all values returned by the subquery cause the full clause to be true. Otherwise, it returns false. Exp is an expression, compare_op is a WHERE clause operator, and subquery is a subquery.
For example, the following returns true if every value produced by the subquery (“SELECT a FROM b”) equals ‘able’:
...WHERE 'able' = ALL (SELECT a FROM b)
exp compare_op ANY subquery
ANY returns true if any value returned by the subquery causes the full clause to be true. Otherwise, it returns false. Exp is an expression, compare_op is a WHERE clause operator, and subquery is a subquery.
For example, the following returns true if one of the values produced by the subquery (“SELECT a FROM b”) equals ‘able’:
...WHERE 'able' = ANY (SELECT a FROM b)
ANY is identical to SOME.
BETWEEN
value_a [NOT] BETWEEN value_b AND value_c
BETWEEN returns true if a given value is in a given range. Value_a, value_b and value_c are value expressions. BETWEEN returns true if value_a >= value_b and value_a <= value_c. Otherwise it returns false.
For example, the following return true:
2 BETWEEN 1 AND 10 10 BETWEEN 1 AND 10 'c' BETWEEN 'a' AND 'm'
The following return false:
11 BETWEEN 1 AND 10 2 NOT BETWEEN 1 AND 10 'c' NOT BETWEEN 'a' AND 'm'
EXISTS
[NOT] EXISTS subquery
EXISTS returns true if the subquery produces rows. Otherwise it returns false.
For example, the following returns all rows specified by the main query (“SELECT * FROM staff”) if there is a deptno value that equals 10. If no deptno value equals 10, nothing is returned.
SELECT * FROM staff WHERE EXISTS (SELECT deptno FROM org WHERE deptno = 10)
IN
search_value [NOT] IN (value_set)
IN returns true if search_value is in value_set. Otherwise it returns false. Value_set can have up to 127 values separated by commas.
For example, the following return true:
2 IN (1,2,3) '3' IN ('1', '2', '3')
The following return false:
4 IN (1,2,3) 2 NOT IN (1,2,3) '3' NOT IN ('1', '2', '3')
LIKE
char_exp [NOT] LIKE search_string[|search_string2][|...][{ESCAPE 'c'}]
or
num_exp [NOT] LIKE arithmetic_expression
LIKE has two forms: it searches for a string (search_string) in a character string expression (char_exp) or tests for equality with an numeric expression (num_exp).
For the first syntax form (string expressions), search_string can include the following:
_ (underscore) |
Any single character. |
/ (slash) |
The escape character for %, _, or / |
% (percent) |
A wildcard character that represents a string of zero or more characters. Null values are not strings, so the following returns rows whose item_desc is non-null: WHERE item_desc LIKE '%' |
{ESCAPE 'c'} |
A definition for an escape character, where c is the escape character that can be used in search_string. The braces are optional. See the note below. For example: char_exp LIKE '100-%' ESCAPE '-' |
To concatenate strings, use the vertical bar ( | ). For example, the following returns all records in the plants table whose in_name column starts with “Easter” and ends with “Vine”:
SELECT * FROM plants WHERE in_name LIKE 'Easter'|'%'|'Vine'
An escape character must either precede an escapable character or be escaped by another escape character. Otherwise it is discarded in the search, causing incorrect query results. (Most likely, no rows will be returned.) No error is generated in this situation. For example, both of the following clauses use escape characters incorrectly. For the first example, the driver will discard the backslashes and look for 1231995. For the second example, the driver will discard the hyphen and look for 123ABC. ...WHERE order_date LIKE '12\3\1995' ...WHERE account_no LIKE '123-ABC' {ESCAPE '-'} The following examples are correct. Each escape character either precedes another escape character or is itself escaped by a preceding escape character: ...WHERE order_date LIKE '12\\3\\1995' ...WHERE order_date LIKE '^%9_' {ESCAPE '^'} |
For the second syntax form (numeric expressions) , arithmetic_expression can contain a simple numeric value, a numeric column, or an expression with arithmetic operators:
* |
Multiplication |
\ |
Division |
+ |
Addition |
- |
Subtraction |
These operators are listed in order of precedence. You can use parentheses to override this order. For example, the following returns all records in the plants table with 18 as the in_itemid value:
SELECT * FROM plants WHERE in_itemid LIKE 3*(5+1)
SOME
SOME is identical to ANY. See ANY.
If an expression returns a null value for a row, xfODBC will return that row. For example, the following returns all plant information where the color is not white, including rows where in_color is null:
SELECT * FROM plants WHERE in_color <> 'white'
If >, >=, <=, or < is used to compare “null” to a literal, no rows will be returned.
For information on how xfODBC interprets null values, see Preventing null updates and interpreting spaces, zeros, and null values.
ORDER BY
The ORDER BY clause sorts the result set. Rows are sorted according to the columns listed in the ORDER BY clause: the first column listed is the primary sort criterion, the second column determines the order within duplicate values in the first, etc. You can specify ascending or descending order by including ASC or DESC. Ascending is the default. Here are some examples:
SELECT * FROM table ORDER BY col_1 SELECT * FROM table ORDER BY col_1 ASC SELECT * FROM table ORDER BY col_1 DESC SELECT * FROM table ORDER BY col_1, col_2 SELECT * FROM table ORDER BY col_1 ASC, col_2 DESC, col_3
Note the following:
- You can use integer column positions in an ORDER BY clause (1 for the first item in the column list, 2 for the second, and so forth). For example:
SELECT or_customer, SUM(or_customer) FROM orders GROUP BY or_customer ORDER BY 1
- For compound queries (queries containing UNION or UNION ALL), ORDER BY clauses must use positions, rather than explicit expressions, and can appear only in the last query (though an ORDER BY will order all rows in the result set). For example:
SELECT cust_name FROM customers UNION ALL SELECT vend_name FROM vendors ORDER BY 1
- See Column aliases for information on restrictions to aliases that can affect ORDER BY clauses.
GROUP BY
The GROUP BY clause enables you to collate rows with identical column values (which may be aggregated values) and return them as a single row. For example, the following statement returns five rows from the sample database even though there are eight rows of data in the table. (Four rows have the same or_price value, so these rows are combined into one row.)
SELECT or_price, SUM(or_qty) FROM orders GROUP BY or_price ORDER BY 2
The next example shows how inline views can include GROUP BY clauses:
SELECT t1.c1, t1.c2, t1.c3 FROM ( SELECT 1 AS c3, COUNT(cust_key) AS c1, cust_state AS c2 FROM customers il1 GROUP BY cust_state ) t1 UNION SELECT t2.c1, t2.c2, t2.c3 FROM ( SELECT 1 AS c3, COUNT(vend_key) AS c1, vend_state AS c2 FROM vendors il2 GROUP BY vend_state) t2
The next example returns an average for in_price for each grouping created by the GROUP_BY clause. So when this is run against the sample database for Connectivity Series, it returns two averages, one for each in_type value (1 and 2).
SELECT in_type, AVG(in_price) FROM plants GROUP BY in_type
GROUP BY is also used to apply aggregate functions to groups of rows. For example, the result set for the following includes a row for each customer, a column for the customer’s number, and a column with the sum of or_qty values for the customer.
SELECT or_customer, SUM(or_qty) FROM orders GROUP BY or_customer
(If the column lists contains only aggregate functions, rows in the result set are treated as a single group for the aggregate functions. For example, “SELECT SUM(or_qty) FROM orders” returns the sum of all or_qty values in each row of the result set.)
Note the following:
- xfODBC supports up to eight GROUP BY columns.
- GROUP BY is not supported for UNION clauses, and it is not supported for SELECT * statements. For example, the following will cause an error (“Non aggregates require a GROUP BY expression”):
SELECT * FROM orders GROUP BY or_odate
Additionally, a subquery can’t have a GROUP BY clause if an outer query uses *.
- All columns in the select list must be in the GROUP BY clause if there is one. (Columns derived from aggregate calculations are the exception. You don’t need to include these in the GROUP BY clause. See the examples above.) Statements that don’t adhere to this will cause “Column(#) is out of range” errors (where # is a meaningless number).
- If the column list includes an aggregate function, all non-aggregate items in the list must be specified in a GROUP BY clause. If all items in a statement’s column list are aggregate functions, rows in the result set are treated as a single group for the aggregate functions.
- Only column names can be specified in a GROUP BY clause (not column numbers or expressions). For example, the following will cause a syntax error:
SELECT in_name FROM plants GROUP BY LCASE(in_name)
To work around this, put the expression in a subquery that creates an alias for the expression, and then use the alias with GROUP BY:
SELECT "Common Name" FROM (SELECT LCASE(in_name) "Common Name" FROM plants) GROUP BY "Common Name"
- See Column aliases for information on restrictions to aliases that can affect GROUP BY clauses.
HAVING
The HAVING clause enables you to place limitations on groups returned by a GROUP BY clause. (This is particularly useful for criteria that include an aggregate function because a WHERE clause cannot contain an aggregate function.) For example:
SELECT or_customer, AVG(or_qty) FROM orders GROUP BY or_customer HAVING AVG(or_qty) > 65
FROM
The FROM clause enables you to specify which tables a query will retrieve data from. You can specify views, base tables, and tables that result from operations that create tables: queries, subqueries (inline views), and so forth. For example, the following example selects all columns from two tables:
SELECT * FROM table1, table2
Note the following:
- You can optimize performance for SQL92 outer joins (full outer, left outer, and right outer) by using inline views, which are SELECT statements in FROM clauses.
- If you use a SELECT statement in a FROM clause (i.e., an inline view), you must enclose the entire SELECT statement in parentheses ().
- You can use table aliases to simplify table references. See Table aliases.
FOR UPDATE OF
The FOR UPDATE OF clause enables you to select rows that match a statement’s selection criteria. If the clause is part of a transaction, it locks selected rows.
sel_statement where_clause FOR UPDATE OF [column_list]
where sel_statment is a SELECT statement and where_clause is a WHERE clause. (, and column_list is an optional list of columns that will be updated. (Column_list is no longer used by xfODBC and has no effect on the statement.)
For example, the following statement locks rows for which in_itemid equals 20:
SELECT in_itemid, in_zone FROM plants WHERE in_itemid = 20 FOR UPDATE OF
A FOR UPDATE OF clause can only be at the end (the last clause) of a SELECT statement.
CASE
CASE evaluates a list of conditions and returns the result for the condition that is true. If no condition is true, the result specified in the ELSE clause is returned or, if there is no ELSE clause, null is returned. The syntax has two forms:
CASE WHEN boolean_1 THEN result_1 [WHEN boolean_2 THEN result_2 ... WHEN boolean_n THEN result_n] [ELSE else_result] END
or
CASE case_exp WHEN value_1 THEN result_1 [WHEN value_2 THEN result_2 ... WHEN value_n THEN result_n] [ELSE else_result] END
where the arguments are as follows:
boolean_*
Boolean expressions. These can be constructed with any operator, clause, etc., that can be used in a WHERE clause (subqueries, IN clauses, BETWEEN clauses, and so forth). See WHERE.
result_*
Expressions whose results are the possible return values for the CASE statement.
case_exp
An expression that’s compared to value_* arguments to determine which result to return.
value_*
Values that are compared for equality to case_exp to determine which result to return.
else_result
An expression that is returned if no boolean_* argument is true or if no value_* argument matches case_exp.
For the second syntax form, the comparison is always a test for equality—i.e., if case_exp = value_2, CASE returns result_2.
The following example uses the first syntax form:
SELECT in_itemid, in_name, CASE WHEN in_color IS NULL THEN 'No color' WHEN CONCAT(in_color, in_shape) = 'blue vine' THEN 'Blue vine' WHEN in_size > 10 THEN 'Large' WHEN in_size BETWEEN 5 AND 10 THEN 'Medium' WHEN in_size IN (1,2,3,4,5) THEN 'Small' ELSE '0' END AS mycol FROM plants
The next example uses the second syntax form:
SELECT in_itemid, in_name, CASE in_size WHEN 10 THEN 'Large' WHEN 5 THEN 'Medium' WHEN 1 THEN 'Small' ELSE 'Other' END AS mycol FROM plants
The next example uses a subquery. The subquery is necessary because aggregate functions (AVG in this case) can’t be used in WHEN clauses. (They can’t be used in WHERE or ORDER BY clauses either.)
SELECT in_maxhigh, CASE WHEN in_maxhigh > (SELECT AVG(in_maxhigh) FROM plants) THEN 'Tall' END FROM plants
Note the following:
- All results, including else_result must have the same data type.
- Conditions are evaluated in order. Boolean_1 is evaluated before boolean_2, value_1 is evaluated before value_2, and so forth.
UNION
The UNION operator combines the results of multiple SELECT statements into one result set.
sel_1 UNION [ALL] sel_2 [...UNION [ALL] sel_n]
where sel_1 through sel_n are SELECT statements.
If you include ALL, duplicate rows are included in the result set. If you omit ALL, duplicate rows are omitted and results are sorted by the first column.
The following returns the cities for all customers and all vendors in alphabetical order. Note, however, that if there’s a duplicate city, only one occurrence of the city will be returned:
SELECT cust_city FROM customers UNION SELECT vend_city FROM vendors
If you add ALL to the same query, all customer and vendor cities will be returned, even duplicates.
SELECT cust_city FROM customers UNION ALL SELECT vend_city FROM vendors
Note the following:
- Columns for all queries must be identical.
- There must be the same number of columns for each query.
- The data types must be compatible.
- Only one ORDER BY clause is allowed with a UNION, and this must follow the final SELECT statement. An ORDER BY clause in a UNION applies to the entire result set.
- GROUP BY clauses are not supported for UNION clauses.
- Using UNION without ALL is more efficient than using SELECT DISTINCT with UNION ALL.
See Creating subqueries and inline views for an example of a UNION clause in an inline view.
Computed columns
An expression can be used within a SELECT statement’s column list. For example:
SELECT or_price + or_price * .1 FROM orders SELECT or_price, '*', or_qty, '=', or_price * or_qty FROM orders SELECT or_price + TO_NUMBER(or_item) FROM orders
Text columns
One or more text columns (text strings enclosed in single quotes) can be added to a SELECT statement’s list. For example:
SELECT or_price, '*', or_qty, '=', or_price * or_qty FROM orders
Table aliases
To make table references simpler, you can assign an alias to a table. (This is also known as a “correlation name” or a “range variable.”) Table aliases last for the duration of a statement.
To create a table alias, add the alias after the table name in a SELECT statement:
SELECT column_list FROM table_name [AS] alias
You can enclose the alias in double quotes (“”) or square brackets ([]) if you want to protect the alias from change—for example, if you want to preserve the case of characters in the alias. If the alias has a space, you must use double quotes or square brackets.
In the following example, cust is the alias for the customer table, and ord is the alias for the orders table.
SELECT cust.cust_key, ord.or_number FROM public.customers cust, public.orders ord WHERE cust.cust_key = ord.or_customer
This could also be written with AS:
SELECT cust.cust_key, ord.or_number FROM public.customers AS cust, public.orders AS ord WHERE cust.cust_key = ord.or_customer
Note the following:
- Table aliases are optional for base tables and views, but are required for tables produced by subqueries.
- Table aliases last only for the duration of the statement’s processing.
- Table aliases defined in inline views can be used only in the inline view.
Column aliases
To make column references simpler, you can assign an alias (correlation name) to a column. Column aliases last for the duration of a statement and are used as the column headings in the result set.
To create a column alias, add the alias after the column name. If the alias has a space or you want to protect it from change (e.g., to preserve case), enclose it in double (not single) quotes (“”) or square brackets ([]). For example:
SELECT customers.cust_zip zipcode, customers.cust_tcode taxcode FROM customers SELECT customers.cust_zip AS zipcode, customers.cust_tcode AS taxcode FROM customers SELECT customers.cust_zip "zipcode", customers.cust_tcode AS "taxcode" FROM customers SELECT customers.cust_zip [zipcode], customers.cust_tcode [taxcode] FROM customers
Note the following:
- If an alias is identical to a column name that is specified in a scalar function, including that alias in a GROUP BY or ORDER BY clause may cause the result set to be sorted in an unexpected way. (The alias or a literal used in the function, rather than the column, may be used as the ORDER BY or GROUP BY criterion.)
- Column aliases from inline views can be used only in the outermost SELECT statement. (They can’t be used in the inline view.)
- Do not use column aliases in a CREATE VIEW statement. To specify column names for a view, include them in the list of view columns, which is (view_col [, ...]) in the following:
CREATE VIEW [user_name.]view_name (view_col [, ...]) AS sel_stmnt