5 Search Conditions

SQL syntax

[NOT] 
{BetweenPredicate | ComparisonPredicate | InPredicate |
    LikePredicate | NullPredicate | InfinitePredicate | NaNPredicate |
QuantifiedPredicate |(SearchCondition)} 
[{AND | OR} [NOT] 
{BetweenPredicate | ComparisonPredicate | InPredicate |
    LikePredicate | NullPredicate | QuantifiedPredicate | (SearchCondition)}
] [...]

Parameters

Description

• Predicates in a search condition are evaluated as follows:

  • Predicates in parentheses are evaluated first.

  • NOT is applied to each predicate.

  • AND is applied next, left to right.

  • OR is applied last, left to right.

  Figure 5-1 shows the values that result from logical operations. A question mark (?) represents the NULL value.

Figure 5-1 Values that result from logical operations

Description of "Figure 5-1 Values that result from logical operations"

• When the search condition for a row evaluates to NULL, the row does not satisfy the search condition and the row is not operated on.

• You can compare only compatible data types.

  • TT_TINYINT, TT_SMALLINT, TT_INTEGER, TT_BIGINT, NUMBER, BINARY_FLOAT and BINARY_DOUBLE are compatible.

  • CHAR, VARCHAR2, BINARY, and VARBINARY are compatible, regardless of length.

  • CHAR, VARCHAR2, NCHAR, NVARCHAR2, TT_TIME, DATE and TIMESTAMP are compatible.

• See Chapter 3, "Expressions" for information on value extensions during comparison operations.

• See "Numeric data types" for information about how TimesTen compares values of different but compatible types.

SQL syntax

RowValueConstructor {CompOp ALL | NOT IN} (Subquery)

The syntax for RowValueConstructor:

RowValueConstructorElement | (RowValueConstuctorList) | Subquery

The syntax for RowValueConstructorList:

RowValueConstructorElement [{, RowValueConstructorElement} ... ]

The syntax for RowValueConstructorElement:

Expression | NULL

The syntax for CompOp:

{= | <> | > | >= | < | <= }

Parameters

Description

• The ALL predicate, which returns zero or more rows, uses a comparison operator modified with the keyword ALL. See "Numeric data types" for information about how TimesTen compares values of different but compatible types.

• If RowValueConstructorList is specified only the operators = and <> are allowed.

Examples

Examples of NOT IN with subqueries:

SELECT * FROM customers 
WHERE cid NOT IN
(SELECT cust_id FROM returns)
AND cid > 5000;

SELECT * FROM customers 
WHERE cid NOT IN
(SELECT cust_id FROM returns)
AND cid NOT IN
(SELECT cust_id FROM complaints);

SELECT COUNT(*) From customers 
WHERE cid NOT IN
(SELECT cust_id FROM returns)
AND cid NOT IN
(SELECT cust_id FROM complaints);

Select all books that are not from exclBookList or if the price of the book is higher than $20.

SELECT * FROM books 
 WHERE id NOT IN (SELECT id FROM exclBookList) OR books.price>20;

The following query returns the employee_id and job_id from the job_history table. It illustrates use of expression list and subquery with the NOT IN predicate.

Command> SELECT employee_id, job_id FROM job_history 
       > WHERE (employee_id, job_id)
       > NOT IN (SELECT employee_id, job_id FROM employees);
< 101, AC_ACCOUNT >
< 101, AC_MGR >
< 102, IT_PROG >
< 114, ST_CLERK >
< 122, ST_CLERK >
< 176, SA_MAN >
< 200, AC_ACCOUNT >
< 201, MK_REP >
8 rows found.

SQL syntax

RowValueConstructor {CompOp ALL | NOT IN} ValueList

The syntax for RowValueConstructor:

RowValueConstructorElement | (RowValueConstructorList) | 

The syntax for RowValueConstructorList:

RowValueConstructorElement[{, RowValueConstructorElement} ... ]

The syntax for RowValueConstructorElement:

Expression | NULL

The syntax for CompOp:

{= | <> | > | >= | < | <= }

The syntax for one element in the ValueList (no parentheses necessary):

ConstantExpression

The syntax for more than one element in the ValueList:

({ConstantExpression} [,...] )

The syntax for an empty ValueList:

( )

Parameters

Description

• If X is the value of Expression, and (a,b, ..., z) represents the elements in ValueList, and OP is a comparison operator, then the following is true:

  • X OP ALL (a,b,...,z) is equivalent to X OP a AND X OP b AND...AND X OP z.

• If X is the value of Expression and (a,b,..., z) are the elements in a Value­List, then the following is true:

  • X NOT IN (a,b,...,z) is equivalent to NOT (X IN (a,b,...,z)).

• All character data types are compared in accordance with the current value of the NLS_SORT session parameter.

• See "Numeric data types" for information about how TimesTen compares values of different but compatible types.

• NOT IN or NOT EXISTS with ALL can be specified in an OR expression.

• IN and EXISTS with ALL can be specified in an OR expression.

• When evaluating an empty ValueList, the result of Expression NOT IN is true.

• If RowValueConstructorList is specified only the operators = and <> are allowed.

Examples

To query an empty select list for a NOT IN condition:

SELECT * FROM t1 WHERE x1 NOT IN ();

For ValueList examples, see the Examples section in "ANY / IN predicate (value list)".

SQL syntax

RowValueConstructor {CompOp ANY | IN} (Subquery)

The syntax for RowValueConstructor:

RowValueConstructorElement | (RowValueConstructorList) | Subquery

The syntax for RowValueConstructorList:

RowValueConstructorElement[{, RowValueConstructorElement} ... ]

The syntax for RowValueConstructorElement:

Expression | NULL

The syntax for CompOp:

{= | <> | > | >= | < | <= }

Parameters

Description

The ANY predicate, which returns zero or more rows, uses a comparison operator modified with the keyword ANY. See "Numeric data types" for information about how TimesTen compares values of different but compatible types.

Examples

This example retrieves a list of customers having at least one unshipped order:

SELECT customers.name FROM customers 
WHERE customers.id = ANY 
(SELECT orders.custid FROM orders 
WHERE orders.status = 'unshipped');

This is an example of an IN predicate with subquery. It SELECTs customers having at least one unshipped order:

SELECT customers.name FROM customers 
WHERE customers.id IN 
(SELECT orders.custid FROM orders 
WHERE orders.status = 'unshipped');

This example uses an aggregate query that specifies a subquery with IN to find the maximum price of a book in the exclBookList:

SELECT MAX(price) FROM books WHERE id IN (SELECT id FROM exclBookList);

This example illustrates the use of a list of expressions with the IN predicate and a subquery.

SELECT * FROM t1 WHERE (x1,y1) IN (SELECT x2,y2 FROM t2);

This example illustrates the use of a list of expressions with the ANY predicate and a subquery.

SELECT * FROM t1 WHERE (x1,y1) < ANY (SELECT x2,y2 FROM t2);

The following example illustrates the use of a list of expressions with the ANY predicate.

Command> columnlabels on;
Command> SELECT * FROM t1;
X1, Y1
< 1, 2 >
< 3, 4 >
2 rows found.
Command> SELECT * FROM t2;
X2, Y2
< 3, 4 >
< 1, 2 >
2 rows found.

SQL syntax

RowValueConstructor {CompOp {ANY | SOME} | IN} ValueList

The syntax for RowValueConstructor:

RowValueConstructorElement | (RowValueConstructorList) |

The syntax for RowValueConstructorList:

RowValueConstructorElement[{, RowValueConstructorElement} ... ]

The syntax for RowValueConstructorElement:

Expression | NULL

The syntax for CompOp:

{= | <> | > | >= | < | <= }

The syntax for one element in the ValueList (no parentheses necessary):

ConstantExpression

The syntax for more than one element in the ValueList:

({ConstantExpression} [,...] )

The syntax for an empty ValueList:

( )

Parameters

Description

• If X is the value of Expression, and (a,b, ..., z) represents the elements in ValueList, and OP is a comparison operator, then the following is true:

  • X OP ANY (a,b,...,z) is equivalent to X OP a OR X OP b OR...OR X OP z.

• If X is the value of Expression and (a,b,..., z) are the elements in a Value­List, then the following is true:

  • X IN (a,b,...,z) is equivalent to X = a OR X = b OR...OR X = z.

• All character data types are compared in accordance with the current value of the NLS_SORT session parameter.

• See "Numeric data types" for information about how TimesTen compares values of different but compatible types.

• When evaluating an empty ValueList, the result of Expression IN is false.

Examples

Select all item numbers containing orders of 100, 200, or 300 items.

SELECT DISTINCT OrderItems.ItemNumber 
FROM OrderItems
WHERE OrderItems.Quantity = ANY (100, 200, 300)

Get part numbers of parts whose weight is 12, 16, or 17.

SELECT Parts.PartNumber FROM Parts 
WHERE Parts.Weight IN (12, 16, 17);

Get part number of parts whose serial number is '1123-P-01', '1733-AD-01', :SerialNumber or :SerialInd, where :SerialNumber and :SerialInd are dynamic parameters whose values are supplied at runtime.

SELECT PartNumber FROM Purchasing.Parts 
WHERE SerialNumber
IN ('1123-P-01', '1733-AD-01',:SerialNumber, :SerialInd);

The following example queries an empty select list for IN condition.

SELECT * FROM t1 WHERE x1 IN ();

The following example uses a list of expressions with IN.

SELECT * FROM t1 WHERE (x1,y1) IN ((1,2), (3,4));

The next three examples, using ttIsql, show the use of constant expressions. Assume a table T with a single column named X of type NUMBER, with the following data:

Command> select * from t;
< 1 >
< 2 >
< 3 >
< 4 >
< 5 >
5 rows found.

This first example uses constant expressions in a query:

Command> select x from t where x in (abs(1-2), to_number('1')+2, 3);
< 1 >
< 3 >
2 rows found.

This second example also uses dynamic parameters:

Command> select x from t where x = any (1+?, 1+3, ?);

Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.

Enter Parameter 1 '_QMARK_1' (NUMBER) > 1
Enter Parameter 2 '_QMARK_2' (NUMBER) > 5
< 2 >
< 4 >
< 5 >
3 rows found.

This third example also uses NULL:

Command> select x from t where x in (null, 1, 3+1, 2);
< 1 >
< 2 >
< 4 >
3 rows found.

For the next example, consider a table T_DATE with a single column named MYDATE of type DATE, with the following data:

Command> select * from t_date;
< 2013-08-13 00:00:00 >
< 2013-08-14 00:00:00 >
< 2013-08-15 00:00:00 >
3 rows found.

The example uses constant expressions for dates:

Command> select mydate from t_date where mydate in (date '2013-08-12'+1, 
date '2013-08-12'+2);
< 2013-08-13 00:00:00 >
< 2013-08-14 00:00:00 >
2 rows found.

For the next example, consider a table MYCHARS with a single column named COL1 of type VARCHAR2(32), with the following data:

Command> select * from mychars;
< abc >
< def >
< ghi >
3 rows found.

The example uses a function call that returns a constant expression:

Command> select col1 from mychars where col1 in (ltrim('abcdef', 'abc'));
< def >
1 row found.

The following example illustrates the use of a list of expressions for the IN predicate. The query returns the DEPARTMENT_NAME for departments with DEPARTMENT_ID = 240 and LOCATION_ID = 1700.

Command> SELECT department_name FROM departments 
       > WHERE (department_id, location_id) IN ((240,1700));
< Government Sales >
1 row found.

SQL syntax

Expression1 [NOT] BETWEEN Expression2 AND Expression3

Parameters

Description

• BETWEEN evaluates to FALSE and NOT BETWEEN evaluates to TRUE if the second value is greater than the third value.

• Consult the following table if either Expression2 or Expression3 is NULL for BETWEEN or NOT BETWEEN:

  Expression2	Expression3	BETWEEN	NOT BETWEEN
  <= Expression1	NULL	NULL	NULL
  > Expression1	NULL	FALSE	TRUE
  NULL	>= Expression1	NULL	NULL
  NULL	< Expression1	NULL	NULL

  
  

• Expression2 and Expression3 constitute a range of possible values for which Expression2 is the lowest possible value and Expression3 is the highest possible value within the specified range. In the BETWEEN predicate, the low value must be specified first.

  Comparisons are conducted as described in "Comparison predicate".

• The BETWEEN predicate is not supported for NCHAR types.

Examples

Parts sold for under $250.00 and over $1500.00 are discounted 25 percent.

UPDATE Purchasing.Parts 
SET SalesPrice = SalesPrice * 0.75
WHERE SalesPrice NOT BETWEEN 250.00 AND 1500.00;

SQL syntax

RowValueConstructor CompOp RowValueConstructor2

The syntax for RowValueConstructor:

RowValueConstructorElement | (RowValueConstructorList) | ScalarSubquery

The syntax for RowValueConstructorList:

RowValueConstructorElement[{, RowValueConstructorElement} ... ]

The syntax for RowValueConstructor2 (one expression)

Expression

The syntax for RowValueConstructor2 (list of expressions)

((Expression[,...]))

The syntax for CompOp:

{= | <> | > | >= | < | <= }

Parameters

Description

• All character data types are compared in accordance with the current value of the NLS_SORT session parameter.

• If RowValueConstructorList is specified only the operators = and <> are allowed.

• See "Numeric data types" for information about how TimesTen compares values of different but compatible types.

• If either side of a comparison predicate evaluates to UNKNOWN or NULL, this implies that neither the predicate nor the negation of the predicate is TRUE.

• The NULL value itself can be used directly as an operand of an operator or predicate. For example, the (1 = NULL) comparison is supported. This is the same as if you cast NULL to the appropriate data type, as follows: (1 = CAST(NULL AS INT)). Both methods are supported and return the same results.

Examples

Retrieve part numbers of parts requiring fewer than 20 delivery days:

SELECT PartNumber FROM Purchasing.SupplyPrice 
WHERE DeliveryDays < 20;

The query returns the last_name of employees where salary=9500 and commission_pct=.25.

Command> SELECT last_name FROM employees 
       > WHERE(salary,commission_pct) = ((9500,.25));
< Bernstein >
1 row found.

The query returns the last_name of the employee whose manager_id = 205. The employee's department_id and manager_id is stored in both the employees and departments tables. A subquery is used to extract the information from the departments table.

Command> SELECT last_name FROM employees 
       > WHERE (department_id, manager_id) =
       > (SELECT department_id, manager_id FROM departments 
       > WHERE manager_id = 205);
< Gietz >
1 row found.

SQL syntax

[NOT] EXISTS (Subquery)

Parameters

The EXISTS predicate has the following parameter:

Description

• When a subquery is introduced with EXISTS, the subquery functions as an existence test. EXISTS tests for the presence or absence of an empty set of rows. If the subquery returns at least one row, the subquery evaluates to true.

• When a subquery is introduced with NOT EXISTS, the subquery functions as an absence test. NOT EXISTS tests for the presence or absence of an empty set of rows. If the subquery returns no rows, the subquery evaluates to true.

• If join order is issued using the ttOptSetOrder built-in procedure that conflicts with the join ordering requirements of the NOT EXISTS subquery, the specified join order is ignored, TimesTen issues a warning and the query is executed.

• The following table describes supported and unsupported usages of EXISTS and NOT EXISTS in TimesTen.

  Query/subquery description	Not Exists	Exists
  Aggregates in subquery	Supported	Supported
  Aggregates in main query	Supported	Supported
  Subquery in OR clause	Supported	Supported
  Join ordering using the ttOptSetOrder built-in procedure	Limited support	Supported

  
  

Examples

Get a list of customers having at least one unshipped order.

SELECT customers.name FROM customers
WHERE EXISTS (SELECT 1 FROM orders 
WHERE customers.id = orders.custid 
AND orders.status = 'unshipped');

Get a list of customers having no unshipped orders.

SELECT customers.name FROM customers
WHERE NOT EXISTS (SELECT 1 FROM orders 
WHERE customers.id = orders.custid 
AND orders.status = 'unshipped');

SQL syntax

Expression IS [NOT] INFINITE

Parameters

Description

• An IS INFINITE predicate evaluates to TRUE if the expression is positive or negative infinity.

• An IS NOT INFINITE predicate evaluates to TRUE if expression is neither positive nor negative infinity.

• The expression must either resolve to a numeric data type or to a data type that can be implicitly converted to a numeric data type.

• Two positive infinity values are equal to each other. Two negative infinity values are equal to each other.

• Expressions containing floating-point values may generate Inf, -Inf, or NaN. This can occur either because the expression generated overflow or exceptional conditions or because one or more of the values in the expression was Inf, -Inf, or NaN. Inf and NaN are generated in overflow or division by 0 conditions.

• Inf, -Inf, and NaN values are not ignored in aggregate functions. NULL values are. If you want to exclude Inf and NaN from aggregates (or from any selection), use both the IS NOT NAN and IS NOT INFINITE predicates.

• Negative infinity (-INF) sorts lower than all other values. Positive infinity (INF) sorts higher than all other values, but lower than NaN ("not a number") and the NULL value.

• For more information on Inf and NaN, see "INF and NAN".

SQL syntax

Expression IS [NOT] NAN

Parameters

Description

• An IS NAN predicate evaluates to TRUE if the expression is "not a number."

• An IS NOT NAN predicate evaluates to TRUE if expression is not "not a number."

• The expression must either resolve to a numeric data type or to a data type that can be implicitly converted to a numeric data type.

• Two NaN ("not a number") values are equal to each other.

• Expressions containing floating-point values may generate Inf, -Inf, or NaN. This can occur either because the expression generated overflow or exceptional conditions or because one or more of the values in the expression was Inf, -Inf, or NaN. Inf and NaN are generated in overflow or division by 0 conditions.

• Inf, -Inf, and NaN values are not ignored in aggregate functions. NULL values are. If you want to exclude Inf and NaN from aggregates (or from any selection), use both the IS NOT NAN and IS NOT INFINITE predicates.

• NaN ("not a number") sorts higher than all other values including positive infinity, but lower than the NULL value.

• For more information on Inf and NaN, see "INF and NAN".

SQL syntax

{ColumnName | Constant | Expression | LOBDataType} IS [NOT] NULL

Parameters

Examples

Use IS NULL to identify the president of the company, who is the only person without a manager.

Command> SELECT * FROM employees 
 > WHERE manager_id IS NULL;
< 100, Steven, King, SKING, 515.123.4567, 1987-06-17 00:00:00, AD_PRES, 24000, 
<NULL>, <NULL>, 90 >
1 row found.

SQL syntax

Source [NOT] LIKE Pattern 
 [ESCAPE {'EscapeChar' | {? | :DynamicParameter} }]

The syntax for Pattern is as follows:

Expression [ || Expression ] [ ... ]

Parameters

Description

• As long as no escape character is specified, the _ or % symbols in the pattern act as wild card characters. If an escape character is specified, the wild card or escape character that follows is treated literally. If the character following an escape character is not a wild card or the escape character, an error results.

• When providing a combination of expressions, columns, character strings, dynamic parameters, or function results to form the pattern, you can concatenate items together using the || operator to form the final pattern.

• Case is significant in all conditions comparing character expressions that use the LIKE predicate.

• If the value of the expression, the pattern, or the escape character is NULL, the LIKE predicate evaluates to NULL.

• The LIKE predicate may be slower when used on a multibyte character set.

• For more information on searching within a national character string within NCHAR, NVARCHAR, or NCLOB, see "Pattern matching for strings of NCHAR, NVARCHAR2, and NCLOB data types".

Examples

Find each employee whose last name begins with 'Sm'.

Command> SELECT employee_id, last_name,first_name FROM employees
       > WHERE last_name LIKE 'Sm%'
       > ORDER BY employee_id,last_name,first_name;
< 159, Smith, Lindsey >
< 171, Smith, William >
2 rows found.

Find each employee whose last name begins with 'SM'. This query returns no results because there are no employees whose last_name begins with upper case 'SM'.

Command> SELECT employee_id, last_name,first_name from employees
       > WHERE last_name LIKE 'SM%' 
       > ORDER BY employee_id,last_name,first_name;
0 rows found.

However, by upper casing the source value of the last name column, you can find all names that begin with 'SM'.

Command> SELECT employee_id, last_name, first_name FROM employees 
 > WHERE UPPER(last_name) LIKE ('SM%');
< 159, Smith, Lindsey >
< 171, Smith, William >
2 rows found.

Use a dynamic parameter denoted by ? to find each employee whose last name begins with 'Sm' at execution time.

Command> SELECT employee_id, last_name,first_name FROM employees
       > WHERE last_name like ?
       > ORDER BY employee_id,last_name,first_name;
 
Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.
 
Enter Parameter 1 '_QMARK_1' (VARCHAR2) > 'Sm%'
< 159, Smith, Lindsey >
< 171, Smith, William >
2 rows found.

Use a bind variable denoted by :a to find each employee whose last name begins with 'Sm' at execution time.

Command> SELECT employee_id, last_name,first_name FROM employees 
       > WHERE last_name LIKE :a 
       > ORDER BY employee_id,last_name,first_name;
 
Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.
 
Enter Parameter 1 'A' (VARCHAR2) > 'Sm%'
< 159, Smith, Lindsey >
< 171, Smith, William >
2 rows found.

For each employee whose last name begins with 'Smit', find the last name of the manager. Display the first name and last name of the employee and the last name of the manager.

Command> SELECT e1.first_name || ' ' || e1.last_name||' works for '||e2.last_name
       >    FROM employees e1, employees e2
       >    WHERE e1.manager_id = e2.employee_id
       >    AND e1.last_name like 'Smit';
< Lindsey Smith works for Partners >
< William Smith works for Cambrault >
2 rows found.

This query pattern references the last_name column as the pattern for which to search:

Command> SELECT e1.first_name || ' ' || e1.last_name||
> ' works for ' || e2.last_name
> FROM employees e1, employees e2
> WHERE e1.manager_id = e2.employee_id
> AND 'Smith' like e1.last_name;
< Lindsey Smith works for Partners >
< William Smith works for Cambrault >
2 rows found.

The pattern can be a column or the result of a function. The following uses the UPPER function on both the source last_name column as well as the 'ma' search string for which you are searching:

Command> SELECT last_name, first_name FROM employees
       > WHERE upper(last_name) LIKE UPPER('ma%');
< Markle, Steven >
< Marlow, James >
< Mallin, Jason >
< Matos, Randall >
< Marvins, Mattea >
< Mavris, Susan >
6 rows found.

The following query demonstrates using a dynamic parameter to request the pattern.

Command> SELECT first_name || ' ' || last_name 
 > FROM employees WHERE last_name like ?;
 
Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.
 
Enter Parameter 1 '_QMARK_1' (VARCHAR2) > 'W%'
< Matthew Weiss >
< Alana Walsh >
< Jennifer Whalen >
3 rows found.

The following query demonstrates combining a character string with a dynamic parameter in the pattern.

Command> SELECT first_name || ' ' || last_name 
 > FROM employees WHERE last_name like 'W' || ?;
 
Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.
 
Enter Parameter 1 '_QMARK_1' (VARCHAR2) > '%'
< Matthew Weiss >
< Alana Walsh >
< Jennifer Whalen >
3 rows found.