The following examples illustrate the use of user-defined functions with SELECT - SQL:
Example 1
Example 1 displays the names of all companies in customer (one field from one table).
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT customer.company ;
FROM customer
Example 2
Example 2 displays the contents of three fields from two tables and joins the two tables based on the cust_id field. It uses local aliases for both tables.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT a.company, b.order_date, b.shipped_on ;
FROM customer a, orders b ;
WHERE a.cust_id = b.cust_id
Example 3
Example 3 displays only records with unique data in the specified fields.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT DISTINCT a.company, b.order_date, b.shipped_on ;
FROM customer a, orders b ;
WHERE a.cust_id = b.cust_id
Example 4
Example 4 displays the country, postalcode, and company fields in ascending order.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT country, postalcode, company ;
FROM customer ;
ORDER BY country, postalcode, company
Example 5
Example 5 stores the contents of fields from two tables in a third table.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT a.company, b.order_date, b.shipped_on ;
FROM customer a, orders b ;
WHERE a.cust_id = b.cust_id ;
INTO TABLE custship.dbf
BROWSE
Example 6
Example 6 displays only records with an order date earlier than 02/16/1994.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT a.company, b.order_date, b.shipped_on ;
FROM customer a, orders b ;
WHERE a.cust_id = b.cust_id ;
AND b.order_date < {^1994-02-16}
Example 7
Example 7 displays the names of all companies from customer with a postal code that matches a postal code in the orders table.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT company FROM customer a WHERE ;
EXISTS (SELECT * FROM orders b WHERE a.postalcode = b.postalcode)
Example 8
Example 8 displays all records from customer having a company name that begins with an uppercase C and is an unknown length.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT * FROM customer a WHERE a.company LIKE "C%"
Example 9
Example 9 displays all records from customer having a country name that begins with an uppercase U and is followed by one unknown character.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT * FROM customer a WHERE a.country LIKE "U_"
Example 10
Example 10 displays the names of all cities in customer in uppercase and names the output column CityList.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT UPPER(city) AS CityList FROM customer
Example 11
Example 11 demonstrates how you can perform a query on data that contains percentage signs (%). A backslash (\) is placed before the percentage sign to indicate that it should be treated as a literal, and the backslash is specified as the escape character in the ESCAPE clause.
Because the sample tables included with Visual FoxPro do not contain the percentage sign character, this query returns no results.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT * FROM customer;
WHERE company LIKE "%\%%" ESCAPE "\"
Example 12
Example 12 demonstrates how you can perform a query on data that contains underscores (_). A backslash (\) is placed before the underscore to indicate that it should be treated as a literal, and the backslash is specified as the escape character in the ESCAPE clause.
Because the sample tables included with Visual FoxPro do not contain the underscore character, this query returns no results.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT * FROM customer;
WHERE company LIKE "%\_%" ESCAPE "\"
Example 13
In example 13, the Escape character uses itself as a literal. The dash is both the escape character and a literal. The query returns all rows where the company name contains a percentage sign followed by a dash.
Because the sample tables included with Visual FoxPro do not contain the percentage sign character, this query returns no results.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT * FROM customer;
WHERE company LIKE "%-%--%" Escape "-"