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 4
The WHERE Clause
© 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 fourth in a planned series of articles on using Local SQL with Paradox/BDE applications. This article will focus on the use of the WHERE clause in simple SQL SELECT statements.


Introduction

In a previous article, we discussed the general form of the SQL SELECT statement, including the most common clauses and predicates. We used the SQL SELECT and FROM clauses to query and return all rows of a table.

The WHERE clause of the SQL SELECT statement is used to determine which rows of the base table(s) are to be included in the result set. The WHERE clause is followed by a list of conditions which is evaluated for each row of the table(s) being queried. For rows in which the condition list evaluates to True, the row is included in the result set. For rows in which the condition list evaluates to False, the row is excluded from the result set.

As with the last article, our examples will use the DBDEMOS database, which contains the Biolife example database.


WHERE Example

A common type of condition found in a WHERE clause is one which tests for equality between a column value and some given literal expression. From our example database, we could find all customers based in California with the following query:
SELECT Company
  FROM customer
 WHERE State = 'CA'
Executing this query produces an ANSWER table consisting of a single column, Company, listing the company names of all of our California-based companies. The result set is equivalent to the result set produced by the following QBE query:
customer | Company     | State |
         | CheckPlus   | CA    |
In our SQL example, we used a test for an exact comparison (State = 'CA'). We can also test using other comparison operators. The below query lists the Item Description and List Price of all of our inventory items that list for more than $45.00.
SELECT Description, ListPrice
  FROM parts
 WHERE ListPrice > 45
Which is equivalent to the QBE query:
parts | Description | ListPrice     |
      | CheckPlus   | CheckPlus >45 |
How about if we wanted to list items which were greater than $45 but less than $100? In QBE, this would look like:
parts | Description | ListPrice          |
      | CheckPlus   | CheckPlus >45,<100 |
In SQL, it would look like:
SELECT Description, ListPrice
  FROM parts
 WHERE ListPrice > 45
   AND ListPrice < 100
Note the use of multiple WHERE conditions, separated by "AND". If we wanted items which were less than $45 or greater than $100, our QBE would look like:
parts | Description | ListPrice             |
      | CheckPlus   | CheckPlus <45 OR >100 |
Our SQL would look like:
SELECT Description, ListPrice
  FROM parts
 WHERE ListPrice < 45
    OR ListPrice > 100
Again, we used multiple WHERE conditions, separated by "OR" this time.


More Complex WHERE Clauses

The below query lists orders that totaled more than $3000 or orders that were paid with Visa and totaled more than $2000.
SELECT orderno, PaymentMethod, itemstotal
  FROM orders
 WHERE itemstotal > 3000
    OR (itemstotal >2000 AND PaymentMethod = 'Visa')
Note the use of parentheses to group the terms of the WHERE clause.


Special Operators

Local SQL has special operators to handle common query requirements.

BETWEEN

Consider a SQL query that lists the description and list price of all of our parts which cost between $45 and $100, inclusive. We could write this query as:
SELECT Description, ListPrice
  FROM parts
 WHERE ListPrice >= 45
   AND ListPrice <= 100
Or, we could use the BETWEEN operator:
SELECT Description, ListPrice
  FROM parts
 WHERE ListPrice BETWEEN 45 AND 100
Note that the BETWEEN operator is inclusive of the start and end criteria.

IN

Consider a SQL query that lists the names of customers from California, Hawaii or Florida:
SELECT name
  FROM customer
 WHERE State = 'CA'
    OR State = 'HI'
    OR State = 'FL'
Or, we could use the IN operator:
SELECT name
  FROM customer
 WHERE State IN ('CA','HI','FL')
IN can also reference columns of other tables or queries. We’ll explore that capability in a future article.


Summary

The WHERE clause of a SQL SELECT statement restricts the result set (ANSWER table) to rows meeting specific requirements by evaluating criteria lists. Where the criteria list evaluates to TRUE, the row is returned. The criteria lists typically consist of comparisons between table columns and literal values or calculated expressions. There are several SQL operators which may also be used.


Local SQL Part 5


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.