SELECT – Restricted

This statement restricts the data returned by comparing the data in the table against the conditions specified in the WHERE clause.

Syntax

SELECT * FROM tablename WHERE fieldname-expression [AND|OR fieldname-expression]

Parameters

tablename

Specifies the name of the table from which to retrieve data.

fieldname-expression

Can take one of the following forms:

[NOT] fieldname operator constant
fieldname IS [NOT] NULL
fieldname IS [NOT] TRUE
fieldname IS [NOT] FALSE
fieldname [NOT] LIKE “string%”

Value	Description
fieldname	Specifies the name of a field in the table to use in a comparison operation.
operator	Can be one of the following values:
	Operator	Description
	=	Equal*
	>	Greater than
	>=	Greater than or equal
	<	Less than
	<=	Less than or equal
	<>	Not equal*
constant	Specifies a numeric string or date, enclosed by quotation marks.
string%	String constant followed by the percent (%) or asterisk (*) wildcard character. A wildcard character can also be used by itself to match everything. Use a wildcard character only at the end of the string constant.

*. Using this operator on a field of type FLOAT produces an error.

Return Values

One of the following error values can be returned:

E_OUTOFMEMORY
DB_E_ERRORSINCOMMAND
DB_E_NOTABLE
DB_E_BADCOLUMNID
DB_E_CANTCONVERTVALUE
DB_E_DATAOVERFLOW

Remarks

An error results if a SQL statement uses the Equal (=) or Not Equal (<>) operator to compare a field of data type FLOAT.

Parentheses can be used to group expressions and establish precedence.

The following additional rules apply to the WHERE statement:

When used, no type conversion is done.

BETWEEN is not supported.
IN is not supported.
LIKE only works against varchar data types.
WHERE clauses against unsigned data are not supported.
The varbinary data types are supported only with the IS [NOT] NULL operator.
Floating point comparisons are approximate. Testing for equality against floating point values is not supported and will return an Invalid field comparison error.

Example

The following code example shows how to use the restricted SELECT statement to count the number of system tables.

Dim rs
Set rs = CreateObject("adoce.recordset")
rs.open "select * from msystables where tablename like 'msys%'"
MsgBox rs.RecordCount
rs.Close
Set rs = Nothing