Notes on clauses, columns, and aliases

This topic discusses the xfODBC driver’s support for the following:

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: 

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'

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.

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)

ANY

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'
Note

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.

Returned null values

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: 

SELECT or_customer, SUM(or_customer) FROM orders GROUP BY or_customer
    ORDER BY 1

SELECT cust_name FROM customers UNION ALL
SELECT vend_name FROM vendors ORDER BY 1

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:

SELECT * FROM orders GROUP BY or_odate

Additionally, a subquery can’t have a GROUP BY clause if an outer query uses *.

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"

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:

FOR UPDATE OF

The FOR UPDATE OF clause instructs the xfODBC driver to open files for update, and if the clause is part of a transaction, it locks selected rows. If the xfODBC user you used to access the xfODBC data does not have read/write access, the FOR UPDATE OF clause will cause an error. (For information on assigning read/write access to users, see Managing access with users, groups, and table settings.)

sel_statement where_clause FOR UPDATE OF [column_list]

where sel_statment is a SELECT statement and where_clause is a WHERE clause. (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:

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:

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:

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:

CREATE VIEW [user_name.]view_name (view_col [, ...]) AS sel_stmnt