ODBC WHERE clause syntax

A WHERE clause evaluates a search condition that uses a combination of one or more logical statements. The WHERE clause is used to limit the rows selected from the data source.

There are three basic types of values you can compare to field values in the database:

Logical operators

You can use the following logical operators to evaluate expressions:

Operator Name

Operator Symbol

Description

Examples

Equals

=

The values on the left and right sides of the expression are equal.

Price = 10

CUSTID = 'AR001'

Not Equal

<>

The values on the left and right sides of the expression are not equal.

Price <> 9

CUSTID <> 'AR001'

Not Equal

!=

The values on the left and right sides of the expression are not equal.

Price != 11

CUSTID != 'AR001'

Greater Than

>

The value on the left side of the expression is greater than the value on the right side.

Price > 9

Greater Than or Equal To

>=

The value on the left side of the expression is greater than or equal to the value on the right side.

Price >= 10

Less Than

<

The value on the left side of the expression is less than the value on the right side.

Price < 11

Less Than or Equal To

<=

The value on the left side of the expression is less than or equal to the value on the right side

Price <= 10

LIKE

LIKE

The value on the left side of the expression matches a pattern in the right side of the expression. The pattern on the right side of the expression must be a string that includes characters and wildcard characters. Wildcard characters include underscores (_) which represents a single character and the percent sign (%) which represents any combination of characters. You can also include ranges of characters using square brackets [], and you can use the ^ symbol to negate the range of characters.

  • PRODNAME LIKE '_art' returns values that start with any character and end with art. For example, cart and dart.

  • Name LIKE 'B%' returns any values that start with B.

  • Name LIKE '%art%' returns any values that contain the letters art at any location.

  • Name LIKE '[A-C]%' returns any names that start with A, B, or C.

  • NAME LIKE '[^A]%' returns names start with any letter other than A.

IN

IN

The value on the left side of the expression matches a value from the list of values on the right side of the expression. The list of values can be a comma separated list of values, or the result of a subquery.

  • PRODNAME IN ('Pen', 'Paper')

  • SALESID IN (Select SALESID WHERE TotalSales > 1000000)

BETWEEN

BETWEEN

The value on the left side of the expression falls between the range specified on the right side of the expression.

  • Price BETWEEN 10 AND 11

  • ShipDate BETWEEN {d '2011-01-01'} AND {d '2011-12-31'} returns records for all orders shipped in 2011.

NOT

NOT

Negates the value of a logical expression. This operator is useful when it is easier to select the values that you want to exclude than those you want to include.

  • NOT Price = 10

  • PRICE NOT BETWEEN 10 and 11

  • Name NOT Like 'B%'

Linking logical expressions

You can use the following logical statements to link two expressions together:

You can use logical operators to:

Using parentheses

You can use parentheses to control the order of operations and to improve the readability of expressions.

ACL applies the standard rules for operator precedence. You can change the order of precedence by placing expressions within parentheses. The innermost expressions is evaluated first.

Proprietary data types and syntax

Many database vendors have implemented proprietary extensions to the SQL language, and many database management systems store data in non-standard internal formats.

Most database vendors have created proprietary SQL language elements that do not conform to the SQL-92 standard supported by ODBC. However, you can use database-specific SQL statements when you connect to a database using ODBC because ODBC passes SQL statements through to the database without modification.

Because of these inconsistencies between database systems, the exact syntax required to retrieve the results you are interested in may vary depending on the syntax supported by the database you are connecting to. For example, Oracle databases support a ToDate( ) function that allows you to convert a string in a specified format to a standard Oracle date format (dd-mmm-yyyy). The best way to resolve any issues you encounter is to work with the systems administrator or database administrator that manages the database to develop a query that works for the data set you are interested in.



(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback