Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Interactive Paradox Articles  |  Beyond Help Articles  |  Tips & Tricks Articles  


Local SQL Part 3
SELECT .. FROM
© 2001 Lawrence G. DiGiovanni
Digico, Inc.
www.digicoinc.com

Preface

Local SQL is a very powerful yet commonly underutilized tool that is provided with Paradox (as part of the Borland Database Engine). It exposes the power and flexibility of the SQL sublanguage to both Paradox developers as well as interactive end-users.

This is the third in a planned series of articles on using Local SQL with Paradox/BDE applications. This article describes how to use the SELECT and FROM clauses of the SQL select statement.


Introduction

As we discussed in the previous article, the SQL SELECT statement consists of a number of required and optional clauses. The only two required clauses are the SELECT clause and the FROM clause. The FROM clause tells SQL which table or tables to query, and the SELECT clause tells SQL which columns will be in the result set (i.e., ANSWER table).

As we’ll later see, the SQL language also provides the ability to create tables and populate them with data. We will approach that subject in a future article, but for now, we will work on a predefined database, which will be used as an example in this and future articles.

The database we will use is the DBDEMOS database, which contains the Biolife example database. This database ships and typically installs with the Borland Database Engine. This sample database describes a sales order-entry system. The CUSTOMER table lists the customer names and addresses. In the following examples, you are encouraged to paste these queries directly into a SQL query in Paradox or the Database Desktop.


Example: Single Table SELECT

Consider the following SQL SELECT statement:
SELECT company, city, state
  FROM customer
This query creates a three-column result set (an ANSWER table) consisting of all rows of CUST. The columns of the ANSWER table have the same name and field type as they do in the CUST table. The answer table is equivalent to one produced by the following QBE query:
customer | Company    | City       | State      |
         | CheckPlus  | CheckPlus  | CheckPlus  |
Why CheckPlus and not Check? SQL SELECT statements don’t eliminate duplicates by default. For example, let’s look at the following SQL SELECT statement, which is the same as the earlier one, but we’ve left off company from the select-list:
SELECT city, state
  FROM customer
When this query is executed, Christiansted, St. Croix shows up twice. That’s because there are two rows in CUST with Christiansted, St. Croix, and SQL SELECT statements don’t, by default, eliminate duplicates. If you want to eliminate duplicates, use the DISTINCT predicate:
SELECT DISTINCT company, city, state
  FROM customer
Now there is only one row with Christiansted, St. Croix in the result set.


Calculated Fields

In addition to simple column names, we can also include more complicated expressions in our select list, including arithmetic operations and string concatenation involving columns. This same general capability is available in QBE using the CALC keyword. For example, our PARTS table shows both cost and list price for each item sold. In order to find the difference for each item, we could use a query:
SELECT Description, ListPrice - Cost
  FROM parts
Or, if we wanted the percent markup for each item:
SELECT Description, (ListPrice - Cost)/Cost
  FROM parts
Note we grouped our operations using parentheses, since standard arithmetic rules apply for order of operation. Also notice that in our ANSWER table resulting from the second example, the column is expressed as a currency value. This is because two currency values were used in the expression. We’ll see how to convert this "on the fly" in a later article.

As noted above, we can also perform string concatenation. Recall our first example query on the customer table. We could essentially format the result as follows:
SELECT Company, City + ', ' + State
  FROM customer
Which would produce a two-column result set consisting of Company and an expression of the form City, State. The above example uses the plus (+) symbol as the concatenation operator, which is valid. It is clearer to use the concatenation operator (||) (two vertical bars) for clarity sake, to distinguish between concatenation and arithmetical addition as shown below.
SELECT Company, City || ', ' || State
  FROM customer

Renaming Result Set Columns on the Fly: AS

In each of the above examples, the column created by our arithmetic or string concatenation was named for the expression. This is unwieldy at best. Like QBE, SQL allows us to rename our columns on the fly. Consider the following modification to the previous query:
SELECT Company, City || ', ' || State AS Location
  FROM customer
The addition of the AS Location causes our query to name this column LOCATION, which is much easier to reference in code or subsequent queries.


Functions

In addition to arithmetical expressions, Local SQL provides a number of functions that may also be included in the select-list. These functions fall into two categories: Aggregate functions and simple (non-aggregate) functions.


Functions: Aggregate Functions

Aggregate functions (AVG, COUNT, MAX, MIN, SUM) operate on multiple rows and perform their calculation across those rows. Exactly which rows are included is a function of the WHERE, GROUP BY, and HAVING clauses, and we’ll detail how they affect aggregate functions later. For now, we’ll show how they operate on an otherwise unqualified query, in which all rows are used to calculate.

For instance, suppose we want to know how many customers we have. We could simply execute the following query:
SELECT COUNT(Company)
  FROM customer
This query results in a single row, single column ANSWER table containing the total number of rows in our customer table.

Aggregate functions can also operate on column expressions. For instance, the total value of our on-hand inventory can be found in the PARTS table as the sum of Quantity on Hand times ListPrice. We can express that as the following SQL query:
SELECT SUM(onHand * ListPrice)
  FROM parts
This query also results in a single row, single column ANSWER table, this time showing us the total value of our on-hand inventory.

We’ll get back to aggregate functions in detail in a later article.


Functions: Simple Functions

Simple Functions (scalar functions) are generally included in most SQL dialects as an extension to the SQL Standard. They operate on column values in a single row and produce a single value which is included in the result set. Local SQL has several such functions, including:

CAST Convert values from one data type to another. For example, convert a number to a character.
EXTRACT Extract the year, month, or day from a date as an integer.
SUBSTRING Extract a portion of a character column.
UPPER Convert a character column value to all uppercase letters.
LOWER Convert a character column value to all lowercase letters.
TRIM Removes any leading spaces, trailing spaces, or both from a character column value.

Note that these functions are not available with QBE.

Suppose we need to generate a list of all company names, but instead of mixed caps, we need them in all upper case. We can generate a result set containing a capitalized list of all company names using the following query:
SELECT Upper(Company) Company
  FROM Customer
Our ANSWER table will have a single column containing the Company name for each company, all in uppercase characters. In the above example, we explicitly named the new column ‘COMPANY’.

These functions are described in detail in the LocalSQL.HLP file. They will be explored in detail in a later article.


Summary

The SQL SELECT statement creates a result set (i.e., an ANSWER table) consisting of specific rows and columns of a table. The result set can consist of simple columns derived from the queried table, calculated expressions or the result of a SQL function. There are two types of SQL functions, aggregate functions, which operate across multiple rows, and simple functions, which operate on single rows. Result set columns may have different names assigned to them during the SQL query using the AS operator in the query.


Local SQL Part 4


Discussion of this article


 Feedback |  Paradox Day |  Who Uses Paradox |  I Use Paradox |  Downloads 


 The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation.
 Paradox is a registered trademark of Corel Corporation.


 Modified: 15 May 2003
 Terms of Use / Legal Disclaimer


 Copyright © 2001- 2003 Paradox Community. All rights reserved. 
 Company and product names are trademarks or registered trademarks of their respective companies. 
 Authors hold the copyrights to their own works. Please contact the author of any article for details.