MDAC 2.5 SDK - Visual FoxPro ODBC Driver
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:
Although using user-defined functions in the SELECT clause has obvious benefits, you should also consider the following restrictions:
Apart from these restrictions, user-defined functions are acceptable in the SELECT clause. However, remember that using SELECT might slow performance.
The following field functions are available for use with a select item that is a field or an expression involving a field:
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:
FieldName1 Comparison FieldName2
FieldName1 is the name of a field from one table, FieldName2 is the name of a field from another table, and Comparison is one of the operators described in the following table.
Operator | Comparison |
= | Equal |
== | Exactly equal |
LIKE | SQL LIKE |
<>, !=, # | Not equal |
> | More than |
>= | More than or equal to |
< | Less than |
<= | Less than or equal to |
When you use the = operator with strings, it acts differently, depending on the setting of SET ANSI. When SET ANSI is set to OFF, Visual FoxPro treats string comparisons in a manner familiar to Xbase users. When SET ANSI is set to ON, Visual FoxPro follows ANSI standards for string comparisons. See SET ANSI and SET EXACT for additional information about how Visual FoxPro performs string comparisons.
FilterCondition specifies the criteria that records must meet to be included in the query results. You can include as many filter conditions in a query as you want, connecting them with the AND or OR operator. You can also use the NOT operator to reverse the value of a logical expression, or you can use EMPTY( ) to check for an empty field. FilterCondition can take any of the forms in the following examples:
Example 1 FieldName1 Comparison FieldName2
customer.cust_id = orders.cust_id
Example 2 FieldName Comparison Expression
payments.amount >= 1000
Example 3 FieldName Comparison ALL (Subquery)
company < ALL ;
(SELECT company FROM customer WHERE country = "UK")
When the filter condition includes ALL, the field must meet the comparison condition for all values generated by the subquery before its record is included in the query results.
Example 4 FieldName Comparison ANY | SOME (Subquery)
company < ANY ;
(SELECT company FROM customer WHERE country = "UK")
When the filter condition includes ANY or SOME, the field must meet the comparison condition for at least one of the values generated by the subquery.
The following example checks to see whether the values in the field are within a specified range of values:
Example 5 FieldName [NOT] BETWEEN Start_Range AND End_Range
customer.postalcode BETWEEN 90000 AND 99999
The following example checks to see whether at least one row meets the criteria in the subquery. When the filter condition includes EXISTS, the filter condition evaluates to True (.T.) unless the subquery evaluates to the empty set.
Example 6 [NOT] EXISTS (Subquery)
EXISTS ;
(SELECT * FROM orders WHERE customer.postalcode =
orders.postalcode)
Example 7 FieldName [NOT] IN Value_Set
customer.postalcode NOT IN ("98052","98072","98034")
When the filter condition includes IN, the field must contain one of the values before its record is included in the query results.
Example 8 FieldName [NOT] IN (Subquery)
customer.cust_id IN ;
(SELECT orders.cust_id FROM orders WHERE orders.city="Seattle")
Here the field must contain one of the values returned by the subquery before its record is included in the query results.
Example 9 FieldName [NOT] LIKE cExpression
customer.country NOT LIKE "UK"
This filter condition searches for each field that matches cExpression. You can use the percent sign (%) and underscore ( _ ) wildcards as part of cExpression. The underscore represents a single unknown character in the string.
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:
You can also use the UNION clause to simulate an outer join.
When you join two tables in a query, only records with matching values in the joining fields are included in the output. If a record in the parent table doesn't have a corresponding record in the child table, the record in the parent table isn't included in the output. An outer join allows you to include all the records in the parent table in the output, along with the matching records in the child table. To create an outer join in Visual FoxPro, you need to use a nested SELECT command, as in the following example:
SELECT customer.company, orders.order_id, orders.emp_id ;
FROM customer, orders ;
WHERE customer.cust_id = orders.cust_id ;
UNION ;
SELECT customer.company, 0, 0 ;
FROM customer ;
WHERE customer.cust_id NOT IN ;
(SELECT orders.cust_id FROM orders)
Note Be sure to include the space immediately preceding each semicolon. Otherwise, you'll receive an error.
The section of the command before the UNION clause selects records from both tables that have matching values. The customer companies that do not have associated invoices are not included. The section of the command after the UNION clause selects records in the customer table that do not have matching records in the orders table.
Regarding the second section of the command, note the following:
Note The placeholders must be the same type as the fields they represent. If the field is a date type, the placeholder should be { / / }. If the field is a character field, the placeholder should be the empty string ("").
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:
ASC specifies an ascending order for query results, according to the order item or items, and is the default for ORDER BY.
DESC specifies a descending order for query results.
Query results appear unordered if you don't specify an order with ORDER BY.
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.
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