You can specify expressions containing user-defined functions for Select_Item in SQL SELECT statements. However, note the following recommendations and restrictions when using user-defined functions in SQL SELECT statements:

• Use API and user-defined functions written in C or assembly language instead of performing high-volume manipulations with user-defined functions. The speed at which user-defined functions are executed can limit the speed of operations performed with SQL SELECT.
  
  
• Assume nothing about the Visual FoxPro input/output (I/O) or table environment when using user-defined functions in SQL SELECT. In general, you do not know which work area is selected, the name of the current table, or even the names of the fields being processed. The value of these variables depends on the precise location in the optimization process where the user-defined function is called.
  
  
• Do not change the Visual FoxPro I/O or table environment in user-defined functions called in SQL SELECT. In general, the results can be unpredictable.
  
  
• Use the argument list that is passed to the function when it is called as the only reliable way to pass values to user-defined functions in SQL SELECT.
  
  
• Understand that "forbidden" manipulations might provide results in one version of Visual FoxPro but might not work in later versions.
  
  

Outside of these restrictions, user-defined functions are acceptable in SQL SELECT statements. However, remember that using SQL SELECT in general might slow performance.

For more information about user-defined functions, see User-Defined Procedures and Functions.

You can use aggregate functions with a Select_Item that is a field or an expression involving a field or within a filter condition in the HAVING clause. However, you cannot nest aggregate functions.

The following table lists aggregate functions you can use in SQL SELECT statements.

The following example creates a query that displays output column names as described:

В	Copy Code
CLEAR ALL CLOSE DATABASES OPEN DATABASE (HOME(2) + 'Data\TestData') SELECT AVG(TAlias1.order_amt), MIN(TAlias1.order_amt) ; FROM Orders AS TAlias1

For more information about the aggregate functions you can use with SELECT statements, see MIN(В ) Function, MAX(В ) Function, and the CALCULATE Command, which contains information about the AVG(В ), COUNT(В ) or CNT(В ), and SUM(В ) functions.

The following rules apply when you specify a column with the AS clause to display results in a separate column:

• If Select_Item is a field with a unique name, the output column name is the field's name.
  
  
• If more than one Select_Item has the same name, an underscore (_) and a letter are appended to the end of the output column name.
  
  For example, if a SQL SELECT statement specifies to display the Cust_ID fields for two tables, Customer and Orders, the column names displayed are FieldName_a and FieldName_b, or in this example, Cust_ID_a and Cust_Id_b. The following example creates a query that displays column names as described:
  
  

  В	Copy Code
  CLEAR ALL CLOSE DATABASES OPEN DATABASE (HOME(2) + 'Data\TestData') SELECT TAlias1.cust_id, TAlias2.cust_id ; В В В FROM Customer AS TAlias1, Orders AS TAlias2 ; В В В WHERE TAlias1.cust_id = TAlias2.cust_id

  In a free table, if more than one SQL SELECT item has the same name, and the name has 10 or more characters, the name is truncated to add a number. For example, an output column name such as Department would appear as Department and Departmen2.
  
  
• If Select_Item is an expression, the output column name appears as Exp_1. Additional output column names appear as Exp_2, Exp_3, and so on. The following example creates a query that displays output column names as described:
  
  

  В	Copy Code
  CLEAR ALL CLOSE DATABASES OPEN DATABASE (HOME(2) + 'Data\TestData') SELECT TAlias1.postalcode+"-1234", TAlias2.postalcode+"-5678" ; FROM Customer AS TAlias1, Orders AS TAlias2 ; WHERE TAlias1.cust_id = TAlias2.cust_id

• If an aggregate function, such as COUNT(В ), is used with Select_Item, the output column is named Cnt_FieldName. If more than one Select_Item is used with aggregate functions, the output columns are named AggregateFunction_FieldName with the aggregate function names abbreviated as needed. The following example creates a query that displays output column names as described:
  
  

  В	Copy Code
  CLEAR ALL CLOSE DATABASES OPEN DATABASE (HOME(2) + 'Data\TestData') SELECT COUNT(order_id), SUM(order_net)FROM Orders

  For more information about aggregate functions, see Aggregate Functions.
  
  

When performing UNION operations between SQL SELECT statements using the UNION clause, be aware of the following considerations and restrictions:

• You cannot use UNION to combine subqueries.
  
  
• The SELECT statements must have the same number of columns in their query output.
  
  
• Only the final SELECT statement can have an ORDER BY clause, which must refer to output columns by number. If an ORDER BY clause is included, it affects the entire result.
  
  
• When two columns of different data types are involved in a UNION operation, the data type with the lower precedence is converted to the data type with the higher precedence.