Considerations for SQL SELECT Statements

  • Article

When creating queries and views with SQL SELECT statements, be aware of the following considerations, conventions, and restrictions.

  • User-Defined Functions in SQL SELECT Statements

  • Aggregate Functions

  • Rules Applied to Column Names

  • UNION Operations

User-Defined Functions in SQL SELECT Statements

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.

Aggregate 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.

Aggregate function

Description

AVG( )

Averages a column of numeric data.

COUNT( ) or CNT( )

Counts the number of select items in a column. COUNT(*) counts the number of rows in the query output.

MIN( )

Determines the smallest value of Select_Item in a column.

MAX( )

Determines the largest value of Select_Item in a column.

SUM( )

Totals a column of numeric data.

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

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.

Rules Applied to Column Names

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:

    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:

    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:

    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.

UNION Operations

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

  • All select lists in the statements that are being combined with UNION must have the same number of expressions (column names, arithmetic expressions, aggregate functions, and so on).

  • Corresponding columns in the result sets that are being combined with UNION, or any subset of columns used in individual queries, must be of the same data type, have an implicit data conversion possible between the two data types, or have an explicit conversion supplied. For example, a UNION between a column of datetime data type and one of binary data type will not work unless an explicit conversion is supplied. However, a UNION will work between a column of money data type and one of int data type, because they can be implicitly converted.

    Columns of the xml data type must be equivalent. All columns must be either typed to an XML schema, or untyped. If typed, they must be typed to the same XML schema collection.

  • The column names in the table that result from UNION are taken from the first individual query in the UNION statement. To refer to a column in the result set by a new name (for example, in an ORDER BY clause), the column must be referred to that way in the first SELECT.

  • Corresponding result set columns in the individual statements that are being combined with UNION must occur in the same order, because UNION compares the columns one-to-one in the order given in the individual queries.

    When different data types are combined in a UNION operation, they are converted by using the rules of data type precedence. In the previous example, the int values are converted to float, because float has a higher precedence than int.

Data Type Conversion and Precedence in UNION Operations

Prior to Visual FoxPro 8.0, you needed to perform explicit data type conversion when performing UNION operations in SQL SELECT statements between two fields of different types. However, Visual FoxPro supports implicit data type conversion for the data types that support it.

Explicit data type conversion requires you to use Visual FoxPro conversion functions, such as CTOD( ), while implicit conversions do not require you to use such conversion functions.

When Visual FoxPro combines two columns of different data types in a UNION operation, the data type with the lower precedence is converted to the data type with the higher precedence. For field properties, NULL takes higher precedence over NOT NULL.

The following table shows all the explicit and implicit data type conversions permitted for Visual FoxPro table data types.

Data type

Implicit conversion

Explicit conversion

Character

Character (Binary)

CTOD( ), CTOT( ), VAL( ), CTOBIN( )

Character (Binary)

 

 

Currency

 

MTON( )

Date

DateTime

DTOC( ), DTOS( ), DTOT( )

DateTime

 

TTOC( ), TTOD( )

Double

 

STR( ), VAL( )

Float

Numeric

NTOM( ), STR( ), INT( )

Integer

Numeric, Float, Double, Currency

BINTOC( )

Logical

 

 

Numeric

Float

NTOM( ), STR( ), INT( )

The following table illustrates implicit conversion results from a UNION operation between two fields.

Data type 1

Data type 2

Data type expected

Character (N)

Character (X)

Character (MAX(N,X))

Character (N)

Character Binary (X)

Character Binary (MAX(N,X))

Character (N)

Memo

Memo

Character Binary (N)

Character Binary (X)

Character Binary (MAX(N,X))

Character Binary (N)

Memo

Memo

Currency

Currency

Currency

Date

Date

Date

Date

DateTime

DateTime

DateTime

DateTime

DateTime

Double (N)

Float (X,Y)

Float (MAX(MAX(8,Y),2))

Double (N)

Integer

Double (N)

Double (N)

Numeric (X,Y)

Double (MAX(MAX(8,Y),2))

Double (X)

Double (Y)

Double (MAX(X,Y))

Float (N,M)

Double (X)

Float (20, MAX(M,X))

Float (N,M)

Float (X,Y)

Float (MAX(N,M), MAX(X,Y))

Float (N,M)

Numeric (X,Y)

Float (MAX (N,X), MAX(M,Y))

Integer

Currency

Currency

Integer

Double (X)

Double (X)

Integer

Float (X,Y)

Float (MAX(11,X), Y)

Integer

Integer

Integer

Integer

Numeric (X,Y)

Numeric (MAX(11,X), Y)

Logical

Logical

Logical

Numeric (N,M)

Double (X)

Numeric (20, MAX(M,X))

Numeric (N,M)

Float (X,Y)

Float (MAX(N,X), MAX(M,Y))

Numeric (N,M)

Numeric (X,Y)

Numeric (MAX(N,X), MAX(M,Y))

Varchar (X)

Character (Y)

Varchar (MAX(X,Y))

Varchar Binary (X)

Character Binary (Y)

Varchar Binary (MAX(X,Y))

For more information about data types and conversion functions, see Data and Field Types and Data Conversion Functions.

See Also

Reference

SELECT - SQL Command

Other Resources

General Reference

Working with Queries

Working with Views (Visual FoxPro)