TOCPREVNEXTINDEX

AcuODBC User's Guide
Version 6.0

5.3.1 SELECT Command

The SELECT command (which is also called a query) allows you to extract rows from a table, according to optional selection rules. Syntax for a SELECT statement can be:

Syntax
    SELECT [DISTINCT] <field-list-1>   
    FROM <table-list>   
    [WHERE <condition>]   
    [ORDER BY <field-list-2>]   

This section begins by presenting a simple SELECT statement and builds on it to develop more complex queries.

The SELECT and FROM clauses

At its most basic, a SELECT command indicates which fields you want to view in the specified table.

Syntax

    SELECT [DISTINCT] <field-list-1>   
    FROM <table-name>   

where <field-list> is the list of fields that must be selected, written according to the following syntax:

    <field-name-1>[, <field-name-2>] . . .   

All the fields in this list must belong to <table-name>.

If you want to select all the fields in a table, replace <field-list-1> with the character "*" (asterisk), which means "all the fields in <table-name>."

<table-name> is the name of a table containing fields specified in <field-list>.

Example

Using the sample data from the "clients" table, enter the following statement to view a list of pet owners and the cities in which they live:

    SELECT owner, state_province   
    FROM clients   


Note: The capitalization is not necessary for proper syntax. The words are shown capitalized here for emphasis. Some applications require that a semicolon (;) appear at the end of the query; other do not. Check your program documentation for details.

The following view is a result of this query:

For information on queries to more than one table, see section 5.3.2, "JOIN Statements."

Use the SELECT DISTINCT command to view only one instance of each value in a column. For example, to see which cities are represented in the "clients" table, without any duplicates, enter the following query:

    SELECT DISTINCT state_province   
    FROM clients   

The following view is a result of this query. Note that California and Arizona each appear only once.

You can use a column alias to give a column a different name in the view you are requesting. For example, the "clients" table contains a column named "street." If you want to call this column "street_address" when you view the results of a query, use the following command:

    SELECT owner, street AS street_address, city   
    FROM clients   

The following view is a result of this query:

The WHERE clause

The WHERE clause imposes conditions on the SELECT command, enabling you to focus your query. This section discusses those conditions and provides some examples.

When you include the WHERE clause, the format of the SELECT command becomes:

    SELECT [DISTINCT] <field-list-1>   
    FROM <table-name>   
    [WHERE <condition>]   

<condition> is a logical expression that identifies a useful set of rows from the rows in the table. The logical expression can contain column values, literals, and operators as follows:

Note that alphanumeric strings of different lengths are compared as though the shorter one were filled out with blanks at the right end.

Example 1 - WHERE clause

Following the earlier example, enter the following statement to view a list of pet owners who live in San Diego:

    SELECT owner, state_province   
    FROM clients   
    WHERE state_province = 'California'   


Note: Because it is an alphanumeric string, "California" is enclosed in single quotation marks.

The following view is a result of this query:

Compare this with the view created using just the SELECT and FROM clauses.

Example 2 - WHERE clause with LIKE condition

Include the LIKE condition for pattern matching or to view values that contain a specified string. For example, to see a list of owners who live in cities that contain the string "go", enter the following command:

    SELECT owner, city   
    FROM clients   
    WHERE city like '*go*'   

The following view is a result of this query:


Note: Characters for wildcards and pattern matching vary between applications. Check the documentation for your ODBC-enabled application for details.

Example 3 - WHERE clause with BETWEEN condition

Include the BETWEEN condition to find values within a specified range. For example, for a list of owners with a client_id between 600 and 699 (inclusive), enter the following query:

    SELECT owner, client_id   
    FROM clients   
    WHERE client_id between 600 and 699   

The following view is the result of this query:

Note that eight owners do not appear in this view.

Example 4 - WHERE clause with IN condition

Use the IN condition to view entries that fall within a list of values. For example, for a list of owners who reside in either Illinois or New Jersey, enter the following query:

    SELECT owner, state_province   
    FROM clients   
    WHERE state_province in ('Illinois', 'New Jersey')   

Note that Illinois and New Jersey are each enclosed in single quotation marks because they are alphanumeric literals, and note that the range of possible values is enclosed in parentheses.

The following view is a result of this query:

Example 5 - WHERE clause with NOT IN condition

Use the NOT IN condition when you want to view entries that fall outside of a range of values. For example, for a list of owners who do not reside in either Illinois or New Jersey, enter the following query:

    SELECT owner, state_province   
    FROM clients   
    WHERE state_province not in ('Illinois', 'New Jersey')   

The following view is a result of this query:

Example 6 - WHERE condition with logical operators

Conditions can be joined with the logical operators "AND" and "OR." According to normal precedence rules, AND conditions are evaluated before OR conditions. Using parentheses, you can override these rules. Enter the following statement to see the names and address of owners who live in either California or Canada:

    SELECT owner, city, state_province, country   
    FROM  clients   
    WHERE state_province = 'California' or country = 'Canada'   

The following view is the result of this statement:

Note that if you do not include the WHERE clause, your query returns data for all the rows in the table.

Functions

AcuODBC supports operations functions for summarizing data in columns (aggregate functions) and across rows. The "accounts" table contains information on patient accounts for six months.

You can determine, for example, either the amount outstanding in a month, or the amount owed on a given patient. This section provides a partial listing of the supported functions and some simple examples. For more details on row and aggregate functions, consult any standard SQL reference.

Standard Aggregate Functions

AcuODBC supports a series of standard SQL aggregate functions that are specified inside a SELECT command on selected fields. The field names to which these functions apply must follow the name of the function and be enclosed in parentheses.

The standard aggregate functions are:

Function
Description
COUNT(*)
Returns the number of rows of a table.
COUNT (DISTINCT)
Counts the number of unique values in a column.
COUNT(column)
Returns the number of non-null values in the column.
SUM
Computes the sum of the non-null values in a column.
AVG
Computes the arithmetic average of the non-null values in a column.
MAX
Returns the highest non-null value in a column.
MIN
Returns the lowest non-null value in a column.

AcuODBC supports two additional column functions:

UPPER
Returns column values in upper case (for conditions only).
LOWER
Returns column values in lower case (for conditions only).

Example 1 - SUM function

You may want to see the accounts total for a particular month. You can accomplish this by entering a statement containing the SUM aggregate function. For example, enter the following statement to obtain the sum of the January accounts:

    SELECT sum(January) AS January_Total   
    FROM accounts   

Your application returns the following:

Note that in this case, including the AS clause creates a column with a new name.

Example 2 - COUNT(*) function

The COUNT(*) function returns the number of rows in a table. To see the number of rows in the clients table, enter the following query:

    SELECT count(*) as rows   
    FROM pets   

Your application returns the following:

The sample table has only 19 rows. This function is quite useful, however, when tables are larger.

Example 3 - COUNT(column) function

You've just seen that the COUNT(*) function returns the number of rows in a table. Use the COUNT(column) function to see the number of non-null values in the specified column. For example, in the "pets" table, one item in the "treatment" column has no value; it is NULL.

To see the number of non-null values in this column, enter the following command:

    SELECT count(treatment) AS non_null_rows   
    FROM pets   

Your application returns the following:

You can see that, in this case, the value returned is smaller than that returned from the COUNT(*) function.

String Functions

AcuODBC supports the following string (or character or alphanumeric) functions inside a SELECT statement. The table below illustrates some commonly used functions with SQL/92 fn syntax, which is the only syntax recognized by AcuODBC directly. See section 5.6, "Functions Supported by AcuODBC," for a complete list.

Function
Format in AcuODBC
Description
CONCAT
{fn CONCAT('string1', 'string2')}
Concatenates two strings together.
LCASE
{fn LCASE('string')}
Changes a string to lower case.
LENGTH
{fn LENGTH('string')}
Returns the length of a string.
LOCATE
{fn LOCATE('character')}
Locates a character in a string.
LTRIM
{fn LTRIM('string')}
Removes leading spaces from a string.
RTRIM
{fn RTRIM('string')}
Removes trailing spaces from a string.
SUBSTRING
{fn SUBSTR('string')}
Locates a string within a string.
UCASE
{fn UCASE('string')}
Changes a string to upper case.

Your application may accept other syntax. See your program documentation for details.

Example

Depending on your application, enter one of the following SQL commands to trim right and left trailing spaces to combine "street" and "city" in a new column called "street_city". Note that the first command is SQL/92 syntax and should work in most applications. The second command is specific to Microsoft Access.

    SELECT street, city,    
    {fn rtrim(street)} + ', ' + {fn ltrim(city)} AS street_city   
    FROM clients   

or

    SELECT street, city,    
    rtrim(street) + ', ' + ltrim(city) AS street_city   
    FROM clients   

Note that the plus sign is used here to combine strings. This example is an alternative to using the CONCAT function.

The following view is a result of this statement:

Arithmetic Expressions

AcuODBC supports the use of arithmetic expressions inside a SELECT command, including the following:

Function
Example
Expressions in the SELECT list
    SELECT col1 + col2   
    FROM table1 
Expressions in functions
    SELECT max(col1 + col2)   
    FROM table1 
Expressions as part of a BETWEEN predicate
    SELECT col1, col2   
    FROM table1    
    WHERE col1 BETWEEN col2 + 5 and col2 + 10   
Expressions as part of comparisons
    SELECT col1, col2   
    FROM table1   
    WHERE col1 + col2 > 10   
Expressions as the test in an IN clause
    SELECT col1   
    FROM table1    
    WHERE col1 + col2 IN (10, 20, 30, 40)   

Example

Using an arithmetic expression, you can add the contents of each cell in a row to determine the total charges for a patient in these months:

    SELECT patient_id, January, February, March, April, May, June,   
    (january+february+march+april+may+june) AS Patient_Total   
    FROM accounts   

The following view, including a new column with each patient's total for six months, is a result of this query:

Note that because the statement did not contain the WHERE clause, AcuODBC returned data for every row in the table. However, you can include the WHERE clause when using expressions as well. For example, enter the following query to see the data and totals for patients with a patient_id greater than 100:

    SELECT patient_id, January, February, March, April, May, June,   
      (january+february+march+april+may+june) AS Patient_Total   
    FROM accounts   
    WHERE patient_id > 200   

The following view is a result of this statement:

The ORDER BY clause

Include the ORDER BY clause to return rows in a specific order in response to your query. Including both the WHERE and ORDER BY clauses, the syntax of the SELECT command is now:

    SELECT [DISTINCT] <field-list-1>   
    FROM <table-name>   
    [WHERE <condition>]   
    [ORDER BY <field-list-2>]   

where <field-list-2> is a field or list of fields contained in <table-name>. For example, to see the list of California pet owners ordered by city, enter the following query:

    SELECT *   
    FROM clients   
    WHERE state_province = 'California'   
    ORDER BY city   

Your application returns the following view:

Use multiple entries in the <field-list> to refine the sort order. For example, enter the following statement to see the list of owners ordered by city, and then ordered by "client_id" because three owners live in San Diego.

    SELECT *   
    FROM clients   
    WHERE state_province = 'California'   
    ORDER BY city, client_id   

The following view is the result of this statement:

The GROUP BY clause

AcuODBC supports the SQL GROUP BY clause.

For example, to see how many times each animal type is represented among the patients, enter the following query:

    SELECT animal_type, count(animal_type) AS number_of_patients   
    FROM pets   
    GROUP BY animal_type   

Your application returns the following view:

Here you can see that this roster of patients lists four birds, six cats, five dogs, two reptiles, and two rodents.

For detailed instructions on the SELECT statement and its clauses, refer to an ODBC or SQL reference manual, such as the ODBC Programmer's Reference.


Acucorp, Inc.
Voice: (800) 262-6585 (U.S.A. and Canada)
Voice: (858) 689-4500
Fax: (858) 689-4550

Please share your comments on this manual
or on any Acucorp documentation with the
Acucorp Communications Department.
TOCPREVNEXTINDEX
1988-2003 Acucorp, Inc.
All rights reserved.