MDAC 2.5 SDK - Visual FoxPro ODBC Driver


 

SELECT - SQL Command

Retrieves data from one or more tables.

The Visual FoxPro ODBC Driver supports the native Visual FoxPro language syntax for this command. For driver-specific information, see Driver Remarks.

Syntax

SELECT [ALL | DISTINCT]
   [Alias.] Select_Item [AS Column_Name]
   [, [Alias.] Select_Item [AS Column_Name] ...]
FROM [DatabaseName!]Table [Local_Alias]
   [, [DatabaseName!]Table [Local_Alias] ...]
[WHERE JoinCondition [AND JoinCondition
…]
   [AND | OR FilterCondition [AND | OR FilterCondition ...]]]
[GROUP BY GroupColumn [, GroupColumn ...]]
[HAVING FilterCondition]
[UNION [ALL] SELECTCommand]
[ORDER BY Order_Item [ASC | DESC] [, Order_Item [ASC | DESC] ...]]

Note   A subquery, referred to in the following arguments, is a SELECT within a SELECT and must be enclosed in parentheses. You can have up to two subqueries at the same level (not nested) in the WHERE clause. (See that section of the arguments.) Subqueries can contain multiple join conditions.

Arguments

[ALL | DISTINCT]
   [Alias.] Select_Item [AS Column_Name]
   [, [Alias.] Select_Item [AS Column_Name] ...]

The SELECT clause specifies the fields, constants, and expressions that are displayed in the query results.

ALL, by default, displays all the rows in the query results.

DISTINCT excludes duplicates of any rows from the query results.

Note   You can use DISTINCT only once per SELECT clause.

Alias. qualifies matching item names. Each item you specify with Select_Item generates one column of the query results. If two or more items have the same name, include the table alias and a period before the item name to prevent columns from being duplicated.

Select_Item specifies an item to be included in the query results. An item can be one of the following:

User-Defined Functions with SELECT

Although using user-defined functions in the SELECT clause has obvious benefits, you should also consider the following restrictions:

You cannot nest field functions.

AS Column_Name

Specifies the heading for a column in the query output. This is useful when Select_Item is an expression or contains a field function and you want to give the column a meaningful name. Column_Name can be an expression but cannot contain characters (for example, spaces) that aren't permitted in table field names.

FROM [DatabaseName!]Table [Local_Alias]
   [, [DatabaseName!]Table [Local_Alias] ...]

Lists the tables containing the data that the query retrieves. If no table is open, Visual FoxPro displays the Open dialog box so that you can specify the file location. Once open, the table remains open after the query is complete.

DatabaseName! specifies the name of a database other than the one specified with the data source. You must include the name of the database containing the table if the database is not specified with the data source. Include the exclamation point (!) delimiter after the database name and before the table name.

Local_Alias specifies a temporary name for the table named in Table. If you specify a local alias, you must use the local alias in place of the table name throughout the SELECT statement. The local alias doesn't affect the Visual FoxPro environment.

WHERE JoinCondition [AND JoinCondition ...]
   [AND | OR FilterCondition [AND | OR FilterCondition ...]]

Tells Visual FoxPro to include only certain records in the query results. WHERE is required to retrieve data from multiple tables.

JoinCondition specifies fields that link the tables in the FROM clause. If you include more than one table in a query, you should specify a join condition for every table after the first.

Important   Keep the following information in mind when creating join conditions:

GROUP BY GroupColumn [, GroupColumn ...]

Groups rows in the query based on values in one or more columns. GroupColumn can be one of the following:

HAVING FilterCondition

Specifies a filter condition that groups must meet to be included in the query results. HAVING should be used with GROUP BY and can include as many filter conditions as you like, connected with the AND or OR operator. You can also use NOT to reverse the value of a logical expression.

FilterCondition cannot contain a subquery.

A HAVING clause without a GROUP BY clause acts like a WHERE clause. You can use local aliases and field functions in the HAVING clause. Use a WHERE clause for faster performance if your HAVING clause contains no field functions.

[UNION [ALL] SELECTCommand]

Combines the final results of one SELECT with the final results of another SELECT. By default, UNION checks the combined results and eliminates duplicate rows. Use parentheses to combine multiple UNION clauses.

ALL prevents UNION from eliminating duplicate rows from the combined results.

UNION clauses follow these rules:

ORDER BY Order_Item [ASC | DESC] [, Order_Item [ASC | DESC] ...]

Sorts the query results based on the data in one or more columns. Each Order_Item must correspond to a column in the query results and can be one of the following:

Remarks

SELECT is an SQL command that is built into Visual FoxPro like any other Visual FoxPro command. When you use SELECT to pose a query, Visual FoxPro interprets the query and retrieves the specified data from the tables. You can create a SELECT query from within either the Command window or a Visual FoxPro program (as with any other Visual FoxPro command).

Note   SELECT does not respect the current filter condition specified with SET FILTER.

Driver Remarks

When your application sends the ODBC SQL statement SELECT to the data source, the Visual FoxPro ODBC Driver converts the command into the Visual FoxPro SELECT command without translation unless the command contains an ODBC escape sequence. Items enclosed in an ODBC escape sequence are converted to Visual FoxPro syntax. For more information on using ODBC escape sequences, see Time and Date Functions and in the Microsoft ODBC Programmer's Reference, see Escape Sequences in ODBC.

See Also

CREATE TABLE - SQL

INSERT - SQL

SET ANSI

SET EXACT