Querying databases

A query is a way to retrieve information from a database. Queries are usually in the form of a question. For example, you can find out which customers placed orders in a given month, the total amount billed for all orders, and which orders haven't been paid.

You can query one table or several tables at a time to get the information you need. Paradox finds the records that meet the conditions you specify and presents the results to you in an Answer table.. 

There are four different types of query building methods. For more information about the different types of query building methods, see "Query building methods."

In this section, you'll learn about

  creating, opening, running, and saving a Query By Example query
  
  viewing SQL syntax
  
  cascading and tiling query images
  
  adding and removing tables in a QBE query
  
  using example elements in QBE queries
  
  linking tables for a QBE query
  
  selecting and re-arranging fields in a QBE query
  
  modifying the Answer table
  
  creating new Answer table fields
  
  creating editing and saving a live query view QBE
  
  changing update handling
  
  changing remote query settings
  
  using reserved words to perform queries
  
  changing auxiliary table options
  
  undoing changes using the Changed table
  
  using summary operators in a query
  
  performing group calculations
  
  counting unique values
  
  defining a set of records in a query
  
  defining selection conditions
  
For more information about Querying databases, see "Reference: Querying databases."

Creating, opening, running, and saving a Query By Example query

The types of queries you can create with Paradox are almost limitless. Query operators and calculation statements can be used to extract just the information you need. No matter what kind of query you're creating, the technique you use to create the query has very little variation. You can create queries that are based on tables, data models, forms, reports, and even another query.

As the information in the database increases or changes, you may need to run the same query on it several times. After you have created and saved one or more queries, you can open and run them to obtain updated information. After Paradox runs a query, it either displays an Answer table or changes data in a table, depending on the kind of query.

To create a QBE query

1	Click File  New  Query.

2	Choose the drive and folder where the table is stored.

3	Double-click a table.

4	In the dialog box, enable the check box beside each field you want to see displayed in the Answer table.

5	Click Query  Run query.

Paradox displays the data in an Answer table.

To open a QBE query

1	Click File  Open  Query.

2	Choose the drive and folder where the query is stored.

3	Select a query.

4	Click Open.

To run a QBE query

  With the query open, click Query  Run query.
  
To save a QBE query

1	With the query open, click File  Save.

2	In the Save file as dialog box, enter the name that you want to save the query as the File name box.

3	Click Save.

To create a QBE query based on a table

1	Click File  New  Query.

2	Choose the drive and folder where the table is stored.

3	Select a table.

4	Click Open.

5	Enable the check box beside each field you want to display in the Answer table.

6	Click Query  Run query.

Note

  If the query contains more than one table, you must link the tables with example elements before you run the query. For more information about linking tables with example elements, see the "To link tables for a QBE query using example elements" procedure.
  
Tip

  You can create a QBE query based on more than one table by holding down CTRL and clicking multiple tables.
  
To create a query based on a data model, form, or report

1	Click File  New  Query.

2	From the Files of type list box, choose one of the following:

  Data models
  
  Forms
  
  Reports
  
3	Choose the drive and folder where the form is stored.

4	Select a form.

5	Click Open.

6	Enable the check box beside each field you want to display in the Answer table.

7	Click Query  Run query.

Note

  When you create a query based on a form or report, Paradox places an image of each table used in the data model of the chosen form or report in the query window. It also places example elements (and inclusion operators, if necessary) to join the tables according to the data model.
  
To create a query based on another query

1	Click File  New  Query.

2	Choose the drive and folder where the query is stored.

3	Choose Queries from the Files of type list box.

4	Double-click a query.

5	Make any changes to the query image.

Note

  When you create a query based on another query, Paradox places a copy of the query in the query window. Any existing selection conditions, example elements, (and inclusion operators, if necessary) are included in the query images.
  
Tip

  If you want to create similar queries, you can modify an existing query and save it under a new name.
  
Viewing SQL syntax

Whether you create a query on local or remote data, Paradox can translate the QBE statement into valid SQL syntax. You can view this SQL syntax.

If you prefer to write SQL syntax rather then to create QBE statements, you can use the SQL Editor to write SQL statements to be run against local (Paradox or dBASE) or remote (SQL) tables. The only restriction is that QBE must be able to interpret the SQL syntax correctly.

To view SQL syntax

  Click View  Show SQL.
  
Cascading and tiling query images

A query image has the same fields, in the same order, as the table it represents, but no data. Paradox allows you to cascade or tile these query images.

By default, the tables will be tiled until the view is changed manually.

To cascade a query image

  Click View  Cascade tables.
  
To tile a query image

  Click View  Tile tables.
  
Adding and removing tables in a QBE query

A simple query is based on only one table, but to create a complex query you can add more tables to your query.

By removing tables from a complex query, you can simplify your queries.

To add tables to a query

1	Click Edit  Insert table.

2	Select a table.

3	Click Open.

Note

  When you create a query using more than one table, you must link them. For more information about linking tables, see the "To link tables for a QBE query using example elements" procedure.
  
Tip

  You can create a QBE query based on more than one table by holding down CTRL and clicking multiple tables.
  
To remove tables from a query

1	Click Edit  Remove table.

2	Choose a table from the Table to remove list.

Tip

  You can remove multiple tables by holding down CTRL and clicking the tables to remove.
  
Using example elements in QBE queries

You can create an example element to represent values in the field in which it is placed. In Paradox, example elements are used in two ways:

  In single-table queries, you can use example elements with query operators to perform calculations with the values in a particular field. An example element represents each value in turn from that field in the selection condition.
  
  In multi-table queries, you can use example elements "to link tables" by common fields. Use example elements to specify that two fields contain common data although their field names differ. Each example element acts as a place marker and means if a record selected from Table A has a value in this field, link it with all the records from Table B that have the same value in the corresponding field.
  
When you use example elements to link tables, you can add as many selection conditions as you want. The only requirement of a multi-table query is that all tables in the query window be linked to each other.

You can use example elements in a date condition or you can also use an example element in a range.

Before you create an example element, see "Rules for creating example elements."

To create an example element

1	Click the field in the query image for which you want to create an example element.

2	Press F5.

3	In the field, type the word you want to use as an example element.

Notes

  The text for an example element displays in red.
  
  You can use example elements in all fields except BLOB fields.
  
To use an example element in a date condition

1	Click the query image, press TAB to the date field.

2	Press F5.

3	In the date field, type in the example element.

4	Press the DOWN ARROW.

Pressing the down arrow will create a second line to the query image.

5	On the second line of the query image, enable the check boxes that you want to display in the Answer table.

6	Press TAB to the date field on the second line of the query image.

7	Press F5.

8	In the date field, type in the date condition, a comma, and the same example element you used in the date field on the first line of the query image.

An example of a date condition would be: date , 01/07/95.

9	Click Query  Run query.

Note

  You must have a date field in the query image for this type of query to work.
  
To use an example element in a range

1	TAB to the field you want to query with a range.

2	Press F5.

3	In the field you want to query with a range, type in the example element.

4	Press the DOWN ARROW.

Pressing the down arrow will create a second line to the query image.

5	On the second line of the query image, enable the check boxes that you want to display in the Answer table.

6	TAB to the field that you are querying with a range on the second line of the query image.

7	Press F5.

8	Type the appropriate "comparison operator" along with the same example element you typed in the field you want to query with a range in the first line of the query range.

Note

  For more information about comparison operators, see "Matching a range of values comparison operators."
  
Linking tables for a QBE query

When you query more than one table, you must use example elements to link the tables under a common field. These linking fields are fields in each table that contain the same kind of information.

Linking to all records in a table

Sometimes you want all records from one table in a QBE query to display in the Answer table even if they are not matched in the joined table. This is called an inclusive link and it uses the inclusion operator (!). For more information about inclusive links, see "Querying with inclusive links."

When you use the inclusion operator in one of two tables, that table is the master table. The other table is the lookup table. Paradox first retrieves all records from the master table. It then looks for and retrieves any matching records in the lookup table. The resulting Answer table contains all records from the master table but only matched records from the lookup table.

Before you link tables, there are rules that you must follow. See "Rules for linking tables."

To link tables for a QBE query using example elements

1	Click the first field you want to link.

2	Press F5.

3	Type the example element in the field.

The example element displays in red.

4	Click the next field you want to link.

5	Press F5.

6	Type the example element.

The example element used in this step must be identical to the one used in step 3.

7	Enable the check box beside one of the fields in one of the tables to display that field in the Answer table.

Notes

  Linking fields do not need to have the same field name, but they must be of compatible types. You cannot, for example, link a number field in one table to an alpha field in another.
  
  The fields that are to be linked cannot be memo, formatted memo, graphic, OLE, or binary fields.
  
To link to all records in a table

1	Click File  New  Query.

2	Hold down CTRL, and select two tables.

3	Click Join tables.

4	Click the linking field of both tables of the query images.

5	Type the inclusion operator (!) after the example element (join1) in the primary key of the query image.

6	Enable the check box beside the primary key and the field that you want to query of the parent table query image.

7	Enable the check box beside the linking field of the child table query image.

8	Click Query  Run query.

Note

  For this query to work, the two tables that you use must have a referential integrity relationship. For more information about referential integrity relationships, see "Defining referential integrity."
  
Selecting and rearranging fields in a QBE query

After you choose the tables you want to query, you must specify what fields you want to see in the Answer table. Each field of a query image has a check box. The column on the far left under the table name also has a check box. You can also control the order of the fields that display in the QBE query image.

To select a QBE query field

1	Click File  Open  Query.

2	Double-click a query.

3	Enable the check box corresponding to the fields you want to see in the Answer table.

You can also	

Place a CheckPlus, CheckDescending, or GroupBy check mark	Right-click the field's check box, and choose the type of check mark you want from the menu.

Notes

  When you use CheckPlus in any field of the query image, it overrides any Checks or CheckDescending you have placed in any other field. This is because Paradox cannot both sort and exclude duplicates.
  
  Although you can place Check and CheckDescendings in BLOB fields, Paradox treats them as CheckPluses in these fields. Paradox cannot sort or distinguish unique from duplicate values in these field types.
  
  The GroupBy checkmark does not display in the Answer table.
  
Tip

  To choose all fields in a table, enable the check box in the left-most column of the query image (under the table name).
  
To move fields in a QBE query image

1	Click File  Open  Query.

2	Select a query.

3	Click Open.

4	Drag a column heading to a new location in the query image.

Note

  The position of the fields in the query image does not affect the sort order of the Answer table.
  
Tip

  To move a field to the end, click a column, and press CTRL + R.
  
Modifying the Answer table

The structure of the Answer table closely reflects the structure of the query example: the left-most field checked in the first image becomes the left-most field of the Answer table, and so on. You can change the field order to better suit your needs.

When run, most queries display an Answer table, which is placed in the private directory. You can change the properties of the Answer table before you run the query, you can rename an Answer table after you run a query, and you can specify different names for fields in the Answer table.

You can change the sort order of the Answer table before you run the query. By default, Paradox sorts first under the left-most field in the Answer table, followed by the next-to-left-most field, and so on. You can set the Answer table to sort first under any of the fields, without changing the order in which they are displayed.

To change Answer table properties

1	Click Query  Properties.

2	Click the Answer tab.

3	Enable one of the following options:

  Live query view  creates a live query view instead of an Answer table.
  
  dBASE  generates an Answer table as a dBASE table.
  
Notes

  For the Answer, Sort, and Structure tabs to display in the Query properties dialog box, you must have at least one box checked in the query image. See "Selecting and rearranging fields in a QBE query" for more information about checking boxes on the query image.
  
  If you would like to specify a new location for your answer table, in the Query properties dialog box, type a new name and location for the Answer table in the Table name box, or leave it in its default location
  
To rename the Answer table

1	Click Query  Run query.

2	Click Format  Rename table.

3	Type a new name for the table in the To box.

Note

  By default, Paradox names the result of a query Answer.db and places it in the private directory. If you rename this table, Paradox does not treat it as a temporary table.
  
Tip

  When typing in a new name for the Answer table, you can type the full path if you want to save the table to a different folder (other than the private folder).
  
To change the Answer table field order

1	Click Query  Properties.

2	Click the Structure tab.

3	Select a field from the Answer fields list.

4	Click the up arrow or down arrow buttons to move the field.

Notes

  In order for the Answer, Sort, and Structure tabs to be present in the Query properties dialog box, you must have at least one box checked in the query image.
  
  To undo changes made to the answer table fields order, see "To undo changes made to the Answer table field order."
  
To undo changes made to the Answer table field order

  In the Query properties dialog box, click Undo.
  
To specify a different name for Answer table fields

1	Click a field in the query image.

2	Type As, followed by a space.

3	Type the name you want the field to be called in the Answer table into the same field.

To sort the Answer table

1	Click Query  Properties.

2	Click the Sort tab.

3	Select the field that you want to sort from the Answer fields box.

4	Click the RIGHT ARROW to move the fields from the Answer fields list to the Sort order list.

Add the fields in the order you want the Answer table sorted.

You can also	

Remove a field from the Sort order list	Click the field name, and click the LEFT ARROW button.

Change the order of the fields in the Sort order list	Choose a field and use the UP ARROW and DOWN ARROW buttons to move the field up or down in the list.

Notes

  In order for the Answer, Sort, and Structure tabs to be present in the Query properties dialog box, you must have at least one box checked in the query image.
  
  Since Sort is a query property, sort information is saved with the query and applies only to that query.
  
  Paradox sorts numbers and other non-alphabetic characters according to the sort order you installed. Alphanumeric "10" sorts before "2" although it is numerically larger.
  
Tip

  By default, Paradox sorts records in the Answer table in ascending order based on the values in the fields you enable, from left to right. To specify that values be sorted in descending order, right-click the check box beside the appropriate field, and choose CheckDescending.
  
Creating new Answer table fields

You can create new Answer table fields that contain a constant value (number, date, or alphanumeric) rather than the result of a calculation. Paradox names the new field in the Answer table the same name as the constant value. If the new field is alphanumeric, it has as many character spaces as necessary to hold the constant value.

You can also create a new blank field.

For more information about using the CALC reserved word, see "Rules for using CALC."

To create an Answer table field with a number or date constant

1	Click any field in the query image.

2	Type the reserved word CALC, a space, and the constant number or date value.

For example: CALC 123

To create an Answer table field with an alphanumeric constant

1	Click any field in the query image.

2	Type CALC, a space, double quotation marks, the alphanumeric constant (with respect for case), and end with double quotation marks.

For example: CALC "Constant"

To create a blank field in an Answer table

1	Click in a field of the query image.

2	Type CALC blank.

Note

  The field that you select must be the same type as the field you want to create.
  
Creating, editing, and saving a live query view (QBE)

Although you can edit the Answer table, any changes you make are not reflected in the original table or tables that you have queried. If you want to create an Answer table that does update the original table when you change it, you must create a live query view instead of an Answer table.

You can edit the live query view, however, when you create a live query view, Paradox generates an answer set that is a limited, direct view into the table you queried. The view is limited by the selection conditions you specify in the query. When you edit the live query view, you are really editing the table you queried and using this limited direct view to see only the data you want from that table.

After creating and editing the live query view, you can save it.

There are rules that you must follow when creating a live query view. To view these rules, see "Rules for live query views."

To create a live query view (QBE)

1	Click Query  Properties.

2	Click the Answer page.

3	Enable the Live query view option.

Notes

  In order for the Answer, Sort, and Structure tabs to be present in the Query properties dialog box, you must have at least one box checked in the query image.
  
  Paradox will not produce a live query view if you performed an INSERT, DELETE, CHANGETO, or CALC query.
  
Tip

  All selected fields in the query image must be selected using the CheckPlus option. To change to CheckPlus, right-click the check box, and choose CheckPlus from the list.
  
To edit the live query view (QBE)

1	Click View  Edit data.

2	Edit the data in the live view as needed.

To delete a record from the live query view (QBE)

1	Click in the row.

2	Press CTRL + DELETE.

Note

  Deletions from Paradox tables cannot be undone.
  
To save a live query (QBE)

1	Click File  Save as.

2	Choose the drive and folder where you want to save the file.

3	Type a name for the live-query in the File name box.

Changing update handling

You can change table update handling for the current work session. For example, when using Paradox on a network, multiple users can make changes concurrently to a shared table in a shared data directory. You can choose whether you want the Answer table to reflect changes made to the source table(s) of the query while the query is running.

To change table update handling for the current work session

1	Click Tools  Settings  Preferences.

2	Click the Query tab.

3	Enable one of the following options found in the Table update handling area:

  Restart query on changes-makes Paradox restart the query when it detects a change to the source table(s).
  
  Lock all tables to prevent changes-locks all tables in the query and prevents any changes to the tables while Paradox runs the query. Paradox releases the locks when it finishes running the query.
  
  Ignore source changes-allows other users to make changes to the source table(s) while Paradox runs the query and prevents Paradox from restarting the query if they make changes.
  
Note

  If someone else is already using the table(s) you want to lock and query, a lock will not be able to be placed. You'll see a message informing you that the table is already locked.
  
Changing remote query settings

When you create a query that uses data from a remote database server, you can choose whether you want Paradox to process the query locally (on the hard drive) or remotely (on the server). Or, you can let Paradox decide how the query can be run most efficiently.

The auxiliary table preferences and properties let you specify whether to create these tables for queries that change data.

To change the default remote query settings

1	Click Tools  Settings  Preferences.

2	Click the Query tab.

3	Enable one of the following remote query options:

  Query may be local or remote-makes Paradox attempt to run the query remotely (on the server). If this fails, Paradox runs the query locally (on your computer).
  
  Run query remotely-makes Paradox request that the server run the query and send back only the answer data.
  
  Run query locally-makes Paradox run the query locally. Paradox requests all data in queried tables from the server and runs the query on the desktop system.
  
Note

  Whether you create a query on local (Paradox or dBASE) or remote (SQL) data, Paradox can translate the QBE statement into valid SQL syntax. This is done automatically when you query remote data.
  
To change remote query settings for the current query

1	Click Query  Properties.

2	Click the Query tab.

3	Enable one of the following options found in the Queries against remote tables area:

  Query may be local or remote-makes Paradox attempt to run the query remotely (on the server). If this fails, Paradox runs the query locally (on your computer).
  
  Run query remotely-makes Paradox request that the server run the query and send back only the answer data.
  
  Run query locally-makes Paradox run the query locally. Paradox requests all data in queried tables from the server and runs the query on the desktop system.
  
Note

  Settings made through the Query properties dialog box, are saved with the query.
  
Using reserved words to perform queries

You can use "reserved words" to perform queries.

DELETE queries

Performing a DELETE query removes selected records, but not specific field values, from a table. DELETE queries are effective when the records to be deleted have something in common that you can specify in one or more selection conditions.

Instead of producing an Answer table, a DELETE query produces a temporary table, which includes only the records deleted. You can undo a DELETE query.

CHANGETO queries

Use CHANGETO queries to change specific field values in a table based on conditions you specify in a query. For example, if you want to change the customer phone number for a record that had the customer ID of 321, you can use the CHANGETO keyword. CHANGETO provides you with a global search-and-replace capability. It is particularly useful when you want to change many values that have common fields. You can perform multi-table CHANGETO queries.

INSERT queries

Use an INSERT query to insert records from one or more sources into a single target table. INSERT queries let you map which values from the source(s) to insert into fields in the target table.

With INSERT you can insert records from one table type into another; for example, from dBASE to Paradox or Paradox to dBASE tables. For example, you can put, any numeric data into any numeric field type (Paradox or dBASE), alphanumeric or character data into any alpha or character field, dates into date fields.

To perform a DELETE query

1	Click File  New  Query.

2	Choose the drive and folder where the table is stored.

3	Select a table.

4	Click Open.

5	Type the word DELETE in the left-most column (under the table name).

6	Type any selection conditions.

Caution: If you do not type any selection conditions, Paradox deletes all the records from the table.

7	Click Query  Run query.

Tip

  You can also enter the word DELETE in the column by pressing the SPACEBAR, and then choosing DELETE from the menu.
  
To undo a DELETE query

1	With the query open, click Tools  Utilities  Add.

2	From the Add records in dialog box, choose PRIV: from the Alias list box.

3	Choose Deleted.db. from the list of files displayed.

4	Click OK.

5	From the Add records in Deleted.db to dialog box, click the Alias list box

6	Choose WORK:.

7	Choose the table that you want to add the deleted records back into.

8	Click Add.

If successful, the message "Records successfully appended" will be displayed in the status bar.

To perform a CHANGETO query

1	Type the value you want to change in the appropriate field of the query image.

2	After the value you want to change, type a comma ( , ).

3	After the comma, type CHANGETO and a space.

4	After CHANGETO and the space, type the new value to which you want to change the current value.

Your CHANGETO query should look something like this: 123,CHANGETO 321

5	Click Query  Run query.

Notes

  Instead of producing an Answer table, a CHANGETO query produces a temporary table called Changed.db, which contains a copy of the records as they existed before you changed them.
  
  The CHANGETO operator must be on the same line in the query image as any selection conditions.
  
Tips

  You can combine several operations in a single query. If you do, Paradox performs all Deletes first, then all CHANGETOs, then all INSERTs.
  
  As with all of Paradox's operators, you can type CHANGETO in uppercase or lowercase.
  
To perform a multi-table CHANGETO query

1	Click File  New  Query.

2	In the Select file dialog box, hold down CTRL, and click both the parent and child tables.

3	Click Open.

4	Click Join.

5	Click the matching fields of both query images.

6	In the query image of the parent table, type CHANGETO and a space before the example element in each field that you want to change.

7	Click Query  Run query.

Paradox changes the values of the appropriate fields of the master table to match those of the detail table.

Note

  You can create a CHANGETO query to change the records in one table to match the records in another table that is linked through referential integrity.
  
To perform an INSERT query

1	Click File  New  Query.

2	Choose the drive and folder where the table is stored.

3	Hold down CTRL and select the source and target tables.

4	Click Open.

5	Link all tables using example elements.

6	For each source table, specify any selection conditions.

7	In the target table, place the word INSERT in the left-most column (under the table name) by doing one of the following:

  Type the letter "i".
  
  Right-click the column and click INSERT.
  
  Press the SPACEBAR, and click INSERT.
  
Do not check any of the fields on the same line as the INSERT operator. If you do, you will get an error.

8	Click Query  Run query.

Notes

  When you perform an INSERT query, Paradox inserts the records from the source into the target table for every field you specified. The source table is not affected by the INSERT query.
  
  If the target table is new, you must create it before you create the query.
  
  Instead of producing an Answer table, an INSERT query produces a temporary table called Inserted.db, which includes only the records inserted
  
Tip

  You can combine several operations in a single query. If you do, Paradox performs all DELETEs first, then all CHANGETOs, then all INSERTs.
  
Changing auxiliary table options

The INSERT, DELETE, and CHANGETO queries generate more than an Answer table. For example, CHANGETO queries create a Changed table and INSERT queries create an Inserted table. It takes time to create these extra tables, and you may not want to create them.

You can specify whether Paradox creates Auxiliary tables for queries that change data (INSERT, DELETE, and CHANGETO queries). Auxiliary table preferences and properties let you specify whether to create these tables for queries that change data.

To change the default auxiliary table options

1	With a query open, click Tools  Settings  Preferences.

2	Click the Query tab.

3	Enable one of the following options found in the Auxiliary table options area:

  Fast queries (no auxiliary tables)-stops Paradox from generating auxiliary tables when you run queries that change data. When you generate only Answer tables, queries will run faster.
  
  Generate auxiliary tables-produces auxiliary tables when you run queries that change data.
  
To change auxiliary table options for the current query

1	With a query open, click Query  Properties.

2	Click the Query tab.

3	Enable one of the following options found in the Auxiliary table options area:

  Fast queries (no auxiliary tables) -stops Paradox from generating auxiliary tables when you run queries that change data. When you generate only Answer tables, the queries will run faster.
  
  Generate auxiliary tables -produces auxiliary tables when you run queries that change data.
  
Note

  Settings made through the Query properties dialog box, are saved with the query.
  
Undoing changes using the Changed table

Using a CHANGETO query produces a temporary table called Changed.db, which contains a copy of the records as they existed before you changed them. Paradox saves Changed.db in the private directory, overwrites it each time you run a CHANGETO query, and deletes it when you exit the program. You can undo changes that you have made by using the Changed.db.

To undo changes using the Changed table

1	Run a DELETE query (as outlined in the "To perform a DELETE query" procedure) on the table whose records you accidentally changed, by using the new field value(s)-the one(s) you changed to-as the selection condition(s). This removes the incorrect records.

2	With Changed.db as the source table, and the original table as the target table, perform an INSERT query (as outlined in the "To perform an INSERT query" procedure) to insert the deleted records back into the original table.

Note

  This action restores the queried table back to its original state. If you are reinserting records into an unkeyed table, Paradox inserts them at the end of the table. Therefore, the records may not be in their original order.
  
Using summary operators in a query

You can use Paradox to answer questions about groups of records. To do this you can select records based on characteristics of a group such as a group count, sum, or average. You can also choose to select records based on a group maximum or minimum.

To select records based on a group count

1	Enable the check boxes beside the fields you want to see in the Answer table.

2	Type COUNT [comparison operator] [numeric value] in a numeric field.

For example, if you want to count all numbers greater than or equal to 5, you would type: "COUNT > = 5".

3	Click Query  Run query.

Notes

  All of the summary operators except COUNT perform their operations by default on all of the values in a group. COUNT counts only unique values in a group by default. To change the default behavior, apply one of the "summary operator modifiers."
  
  For more information about comparison operators, see "Matching a range of values comparison operators."
  
To select records based on a group sum

1	Click File  New  Query.

2	Choose the drive and folder where the tables are stored.

3	Hold down CTRL and click the tables you want to query.

4	Click Open.

5	Click Join.

6	Click the common field of each query image.

7	Enable the check boxes beside the fields you want to see appear in the Answer table.

8	Type SUM [comparison operator] [numeric value] in a numeric field.

For example, if you want to display all records that have a sum that is greater than 5,000, type: SUM > 5000.

9	Click Query  Run query.

Notes

  The tables that you select to query must be related.
  
  For more information about comparison operators, see "Matching a range of values comparison operators."
  
To select records based on a group average

1	Click File  New  Query.

2	Choose the drive and folder where the table is stored.

3	Hold down CTRL and select the tables that you want to query.

4	Click Open.

5	Click Join Tables.

6	Click the common field of each query image.

7	Enable the check boxes beside the fields you want to see appear in the Answer table.

8	Type AVERAGE [comparison operator] [numeric value] in a numeric field.

For example, if you want to display all records that have an average less than 50000, type: AVERAGE < 50,000.

9	Click Query  Run query.

Notes

  The tables that you select to query must be related.
  
  For more information about comparison operators, see "Matching a range of values comparison operators."
  
To select records based on a group maximum

1	Click File  New  Query.

2	Choose the drive and folder where the table is stored.

3	Hold down CTRL and click the tables that you want to query.

4	Click Open.

5	Click Join.

6	Click the common field of each query image.

7	Enable the check boxes beside the fields you want to see appear in the Answer table.

8	Type MAX [comparison operator] [numeric value] in the numeric field that you are querying in the query image.

For example, if you want to display all records that have a maximum value that is less than or equal to 200,000, type: MAX <=200000.

9	Click Query  Run query.

Notes

  The tables that you select to query must be related.
  
  For more information about comparison operators, see "Matching a range of values comparison operators."
  
To select records based on a group minimum

1	Click File  New  Query.

2	Choose the drive and folder where the table is stored.

3	Hold down CTRL and click the tables that you want to query.

4	Click Open.

5	Click Join.

6	Click the common field of each query image.

7	Enable the check boxes beside the fields you want to see appear in the Answer table.

8	Type MIN [comparison operator] [numeric value] in a numeric field.

For example, if you want to display all records that have a maximum value that is less than or equal to 200,000, type: MIN <=200000.

9	Click Query  Run query.

Notes

  The tables that you select to query must be related.
  
  For more information about comparison operators, see "Matching a range of values comparison operators."
  
Performing group calculations

You can use the reserved word CALC along with the summary operator SUM to perform group calculations on the values in a particular field of a table or you can perform a group calculation on the entire table.

Before using CALC, see "Rules for using CALC."

To perform a group calculation

1	Click File  New  Query.

2	Choose the drive and folder where the table is stored.

3	Double-click a table.

4	Enable the check box beside the field you want to query.

5	Type CALC SUM in the numeric field.

6	Click Query  Run query.

Note

  If you do not check any fields in a query, Paradox performs the summary operation or summary calculation on all the records in a table-the whole table being the group.
  
To perform a group calculation on an entire table

1	Click File  New  Query.

2	Choose the drive and folder where the table is stored.

3	Double-click a table.

4	Type CALC SUM in the numeric field.

5	Click Query  Run query.

Note

  If you do not check any fields in a query, Paradox performs the summary operation or summary calculation on all the records in the table-the whole table is the group.
  
Counting unique values

Use the reserved word CALC along with the summary operator COUNT to count only unique values in a table.

For more information about using CALC, see "Rules for using CALC."

To count unique values

1	Click File  New  Query.

2	Choose the drive and folder where the table is stored.

3	Double-click a table.

4	Type CALC COUNT in the key field.

5	Click Query  Run query.

Note

  You cannot use COUNT in Paradox BLOB fields and dBASE memo fields. In these field types, CALC COUNT counts all values, even if you specify the UNIQUE operator. For more information about the summary operator modifier UNIQUE, see: "QBE Query operators."
  
Defining a set of records in a query

In general, a set is a collection of objects. In Paradox, a set is a specific group of records that you intend to query. You can define a set of records to answer a question that may otherwise take two or more queries.

Before you query a set, see "Guidelines for querying sets."

For more information about summary and comparison operators, see "QBE query operators."

To define a set of records in a query

1	Click File  New  Query.

2	Choose the drive and folder where the table is stored.

3	Double-click a table.

4	In the query image, enter selection conditions.

5	Right-click the left-most field of the query image(s), and click Set.

6	Type in example elements.

Note

  Defining a set of records in a query is very much like selecting the records to be included in the Answer table. A set definition is a query within a query.
  
Defining selection conditions

Selection conditions are used to narrow the scope of the information you retrieve with a query. You use combinations of operators, "reserved words", and symbols to define the type of information you are looking for. For more information about using reserved words or symbols in a selection condition, see "Using reserved words or symbols in selection conditions."

You can perform the following types of queries using special operators:

Exact matches

You can define a selection condition that retrieves only records that have a specific value in a field. Paradox includes in the Answer table only records with that value in that field. Exact matches are case-sensitive. You can specify exact matches for as many different fields as you want.

LIKE

You can use the LIKE operator in a QBE query image to match inexact alphanumeric values. This is particularly useful for finding values that contain typographical errors or alternate spellings.

TODAY

You can use the TODAY operator on date fields in a QBE query image to find a specific date or a specific set of dates.

BLANK

The BLANK operator finds records with no value in a specified field. In some cases, the absence of a value is a useful piece of information. You may also want to find records with a blank field so you can fill in information that was unavailable when the record was entered.

AS

You can use the AS operator to change field names in the Answer table.

NOT

The NOT operator finds records that do not have a specified value in a particular field.

AND

The comma ( , ) acts as an AND operator and specifies that all conditions must be met for a match to occur. Since a value in a single field cannot be two or more values at the same time, the AND conditions you specify in a single field can be any kind except exact match conditions.

OR

You can set logical OR operations in a QBE query. That is, you can retrieve records that meet either of two (or any of several) conditions.

To create an exact match selection condition

1	Check off all the fields that you want to see displayed in the Answer table.

2	In the query image, click the field you want to query.

3	Type the value you are looking for into the field.

4	Click Query  Run query.

Notes

  You cannot specify exact matches for BLOB fields. You must use the wildcard operator (..) to specify selection conditions in memo and formatted memo fields. To see a list of the wildcard operators available in Paradox, see "QBE query operators."
  
  Exact matches of logical fields include uppercase or lowercase T and F and any combination of uppercase and lowercase letters of the entire words True and False.
  
  Exact matches are case-sensitive
  
To create an inexact match selection condition

1	Check off all the fields that you want to see displayed in the Answer table.

2	In the query image, click the field you want to query.

3	Type LIKE into the field.

4	Click Query  Run query.

Notes

  You cannot use LIKE on BLOB fields or dBASE memo fields.
  
  Although you can use LIKE in number and date fields, you will get better results by using the wildcard operators .. and @ to specify a number or date pattern. For more information about using a wildcard to match a pattern, see "Using wildcards to match a pattern."
  
  A pattern matches if at least one-half to two-thirds of the characters match.
  
Tip

  The first character of the pattern you specify with the LIKE operator must match exactly (though case does not matter). "LIKE California" does not match Kalifornia.
  
To find the current date

1	Check off all the fields that you want to see displayed in the Answer table.

2	In the query image, click the field you want to query.

3	Type TODAY in the appropriate date field of the query image.

4	Click Query  Run query.

Notes

  The field you use the TODAY operator on must be a date field.
  
  In date fields of a QBE query image, the TODAY operator always stands for the current date.
  
  For more information about comparison operators, see "Matching a range of values comparison operators."
  
Tip

  You can combine comparison operators with the TODAY operator.
  
For example:

Expression	Meaning

< TODAY	Finds dates earlier than today's date

< TODAY - 90	Finds dates earlier than 90 days ago

TODAY + 30	Finds dates 30 days ahead of today's date

To use the BLANK operator

1	Check off all the fields that you want to see displayed in the Answer table.

2	In the query image, click the field you want to query.

3	Type BLANK in the appropriate field of the query image.

4	Click Query  Run query.

Notes

  When you use "comparison operators" or sort by a field that has blank values, blank fields are considered to have a lower value than any non-blank value.
  
  Searching for blank field values is entirely different from leaving a field blank in a query image. Using the BLANK operator specifies that you want to see only those records that have no value in the specified field. However, when you leave the field of a query image blank, Paradox does not consider the field at all when selecting records.
  
Tip

  You can combine NOT with BLANK to find all records that have any value in the specified field.
  
To specify a different name for an Answer table field

1	Check off all the fields that you want to see displayed in the Answer table.

2	In the query image, click the field you want to query.

3	Type As, followed by a space and the name you want the field to be called.

To specify a non-match

1	Check off all the fields that you want to see displayed in the Answer table.

2	In the query image, click the field you want to query.

3	Type NOT, followed by a space and the name you do not want to match.

4	Click Query  Run query.

Notes

  The case of the NOT operator does not matter.
  
  If the selection condition you specify after NOT is an exact match condition, you must type the condition with exactly the same capitalization and spelling as the matching value displays in the table. Values in logical fields are an exception to this rule.
  
  NOT can precede exact values, ranges, wildcard patterns, or other selection conditions. In fact, you can precede any valid Paradox selection condition with NOT. For more information about using a wildcard to match a pattern, see "Using wildcards to match a pattern."
  
To create an AND selection condition

1	Check off all the fields that you want to see displayed in the Answer table.

2	In the query image, click the field you want to query.

3	Type the entire AND expression on the same line of the field.

4	Click Query  Run query.

You can also	

Specify AND conditions in different fields	Type the conditions on the same line of the QBE query image, and place each condition in its respective field. You must type all of the values you want to see-exactly as they display in the table-in the appropriate fields of the query image.

Notes

  You can use the AND operator in all field types including BLOBs. When you query a memo or formatted memo field, you must use the wildcard operator (..) in addition to any other selection conditions or operators you use.
  
  If you have the United States number format set, spaces are not necessary between the conditions and the AND ( , ) operator. If you have the international number format set, a space is necessary on one side of the comma.
  
Tips

  Type a comma ( , ) to separate AND conditions in a single field of a QBE query image.
  
  If you want to type a comma in a query without Paradox interpreting it as the AND operator, enclose the comma in quotation marks.
  
  To match a value that includes a comma (like Acme, Inc.) you must enclose the value in quotation marks or Paradox will interpret the comma as an AND operator. For example, you would type "Acme, Inc".
  
To create an OR selection condition

1	Check off all the fields that you want to see displayed in the Answer table.

2	In the query image, click the field you want to query.

3	Type the conditions in the field, separated by the OR operator.

4	Click Query  Run query.

You can also	

Specify OR conditions in different fields	Type the conditions on separate rows of the query image. To add additional rows to the query, press the INSERT key (works only if you have changed the current row).

Notes

  You can use the OR operator in all field types, including BLOBs. When you query a memo or formatted memo field, you must use the wildcard operator (..) in addition to any other selection conditions or operators you use.
  
  You can't use the OR operator on example elements The condition Qty or Price, where Qty and Price are example elements, returns an error message. This is because an example element stands for all the values in the field. You can't specify that either Qty or Price can represent all the values in the field.
  
  OR conditions are case sensitive.
  
Tip

  Sometimes you use the OR query when you are asking an "and" question. For example, if you want all records in CA and HI, you must query for CA OR HI because no single record has both values.
  
Reference: Querying databases

The following topic contains reference information about querying your database.

QBE Query operators

There are eight types of Paradox query operators:

Reserved symbols

Check type	Properties

Check	Displays all unique values for the selected field in ascending order (A to Z or 0 to 9). When used with a summary operator,a check mark specifies that the records can be divided into groups based on the values in the checked field.

CheckPlus	Displays all values in a field, including duplicates, without sorting. The values sort in the Answer table in the same order they are displayed in the queried table.

CheckDescending	Displays unique values sorted in descending order (Z to A or 9 to 0).

GroupBy	Groups records by a field's value in the Answer table.

Reserved words

Operator	Meaning

CALC	Calculate a new field

INSERT	Insert records with specified values

DELETE	Remove records with specified values

CHANGETO	Change specified values in fields

SET	Define specific records as a set for comparisons

Arithmetic operators

Operator	Meaning

+	Addition or alphanumeric string concatenation

-	Subtraction

*	Multiplication

/	Division

( )	Group arithmetic operations

Comparison operators

Operator	Meaning

=	Equal to (optional)

>	Greater than

<	Less than

>=	Greater than or equal to

<=	Less than or equal to

Wildcard operators

Operator	Meaning

..	Any series of characters

@	Any single character

Special operators

Operator	Meaning

LIKE	Similar to

NOT	Does not match

BLANK	No value

TODAY	Today's date

OR	Specify OR conditions in a field

, (comma)	Specify AND conditions in a field

AS	Specify the name of a field in an Answer table

! (exclamation mark)	Display all values in a field, regardless of matches

Summary operators

Operator	Meaning

AVERAGE	Average the values in a group

COUNT	Count the number of values in a group

MAX	Find the maximum value of a group

MIN	Find the minimum value of a group

SUM	Total the values in a group

ALL	Calculate summary based on all values in a group, including duplicates

UNIQUE	Calculate summary based on unique values in a group

Set comparison operators

Operator	Meaning

ONLY	Display records that match only members of the defined set

NO	Display records that match no members of the defined set

EVERY	Display records that match every member of the defined set

EXACTLY	Display records that match all members of the defined set and no others

Summary operator modifiers

Operator	Meaning

ALL	Considers all values in a group, including duplicates. You must use ALL with COUNT, in the format COUNT ALL, to make COUNT count all values in a group, including duplicates.

UNIQUE	Considers only unique values in a group. You must use UNIQUE with all summary operators except COUNT to make them perform their operation on unique values in a group instead of on all values.

Operator precedence in QBE queries

Paradox evaluates operators in queries in a specific order.

In expressions that contain more than one operator, the operators are evaluated in the order of precedence shown in the following table:

Precedence	Operator

1	( )

2	* /

3	+ -

4	= <> < <= > >=

5	NOT

6	OR

7	, (comma)

Any expression contained in parentheses is evaluated first, and inner levels of parentheses are evaluated before outer levels. When two or more operators of equal precedence are in a single expression, they are evaluated from left to right.

Rules for entering numbers in QBE queries

When you type a number into a number field (Paradox number, short integer, long integer, or money field and dBASE number or floating number fields) of a query image, do not type:

  dollar signs
  
  parentheses to signify a negative value
  
  thousand separators (neither a comma nor a period) when you specify a pattern match with the .. or @ wildcard operators. For more information about using a wildcard to match a pattern, see "Using wildcards to match a pattern."
  
But you can type:

  decimal separators (a period in United States convention and a comma in international convention)
  
  the minus symbol to signify a negative value
  
  thousand separators when specifying an exact match numeric selection condition
  
Paradox determines when a comma or a period is a whole number or a decimal separator, first based on whether you have a United States or international number convention set, and second, based on the symbol's position and context. Ambiguity arises when a comma may be Paradox's AND operator, which is a comma, and when a period may be part of Paradox's wildcard operator (..), which is two periods in a row.

If the meaning of a comma or period is not clear, you must help Paradox understand the symbol's meaning by using double quotation marks or spaces. The meaning of a comma or period meaning will not be recognized as a thousand separator if you are specifying a pattern match with the wildcard operators (.. or @); therefore, do not type thousand separators when you specify a numeric pattern with wildcard operators.

If you are using the United States number format

Paradox interprets a single period in a number field as a decimal separator.

Paradox interprets the first two periods in a row as the .. wildcard operator.

In a number field, if Paradox encounters three periods in a row, it interprets them as the .. wildcard operator followed by the decimal separator. To make Paradox interpret the first period as the decimal separator, enclose it in double quotation marks.

Paradox interprets a comma in a number field as a thousand separator if you are specifying an exact match and if the comma is in the proper position to be a thousand separator. To make Paradox interpret a comma as the AND operator where this meaning may not be clear, type a space or any other non-numeric character (except @ or a period) after the AND comma. For example, you can type a "comparison operator."

If you are using the international number format

Paradox interprets the first comma in a number field within a number as the decimal separator.

Paradox interprets a comma followed by a space or any other non-numeric character (except @ or a period) as the AND operator in a number field.

Paradox interprets a single period in a number field as a thousand separator if you are specifying an exact match and if the period is in the proper position within a numeric selection condition.

Using reserved words or symbols in selection conditions

In a QBE query image, to specify an alphanumeric value that contains a period or comma or a Paradox reserved word, the value must be enclosed in double quotation marks. Paradox then recognizes the quoted characters as a value and does not act on their special meaning.

If the value itself contains a double quotation mark, precede the quotation mark with a backslash (\):

  Thomas E. \"Ned\" Lawrence
  
If the value contains a backslash, precede that backslash with another backslash (\\).

You do not need quotation marks to enclose blank spaces in a value. You do need them, however, for all other symbols and operators that have special meanings in Paradox, such as commas, periods, and asterisks.

Using Arithmetic operators in QBE queries

You can use arithmetic expressions in number, date, time, and money fields of a query image.

Operator	Meaning

 +	Addition or string concatenation

-	Subtraction

*	Multiplication

/	Division

( )	Used to group expressions

Use parentheses ( ) to combine and group operations and to indicate which calculations can be performed first. In expressions without parentheses, multiplication and division are performed before addition and subtraction. Operations with equal precedence are calculated from left to right. For more information about operator precedence, see "Operator precedence in QBE queries."

Arithmetic operators are especially useful with the TODAY operator, the CALC operator, and with example elements.

You can use arithmetic expressions with date values and the TODAY operator to

  add a number of days to a date
  
  subtract a number of days from a date
  
  subtract one date from another date to produce a range of days
  
Use arithmetic operators to create arithmetic expressions with field values. You can use any of the arithmetic operators in the number fields-Paradox number, short integer, long integer, BCD, money, dBASE number, and dBASE floating number fields.

Matching a range of values: comparison operators

If you want a QBE query to retrieve records that match a range of values, use comparison operators, also known as range operators. Comparison operators let you specify a range of values in a single field. You can combine comparison operators to construct a limited range of values. 

Equal to (=)

Examples	Match

= 3/17/97	Only March 17,1997

= Ralph	Only Ralph

= False	Only False

Greater than (>)

Examples	Match

> 3/17/97	Dates later than March 17, 1997

> "Ralph"	"Rat", "Rudolph"

> "False"	True, T, Yes, 1

Less than (<)

Examples	Match

< 3/17/97	Dates before March 17, 1997

< "Ralph"	"Charles"

< "True"	False (by convention, False < True)

Greater than or equal to (>=)

Examples	Match

>= 3/17/97	March 17,1997 and later dates

>= "Ralph"	"Ralph", "Raphael", "Randolph"

Less than or equal to (<=)

Examples	Match

<= 3/17/97	March 17, 1997 and earlier dates

<= "Ralph"	"Ralph", "Manny", "Charles"



Example

The following query retrieves all stock that costs more than $1000:

Field type restrictions

You can use comparison operators with alphanumeric values and all number, date, and logical values. You cannot use them with BLOB or dBASE memo values; you can only use the equal to operator (=) with these types.

Rules for using CALC

With arithmetic operators

You can use CALC in any field of a QBE query image. After you type the CALC reserved word, you type the expression for the calculation you want to perform.

Expressions can contain

  constants such as 154 or 12/24/91
  
  example elements such as QTY
  
  arithmetic operators such as + - * / ( )
  
  summary operators such as SUM or MAX
  
  comparison operators such as = < > <= >=
  
Using CALC with alphanumeric values

You can combine (concatenate) alphanumeric values and constants by using CALC and the + operator.

For example

  you can add "Ms. " in front of a list of last names when the value in the Sex field is F
  
  you can use CALC to combine values from the City, State/Prov, and ZIP/Postal Code fields into a single Address field
  
When you use CALC in a query, the Answer table generated by that query contains an additional field for the calculated result. This means that

  when you create tables, there is no need to include fields for any data that can be calculated from the values in other fields
  
  the field of the query image in which you choose to type the CALC expression does not matter
  
  you don't need to check the field in which you enter the CALC expression because the CALC operator always causes Paradox to create a new field in the Answer table. If you do check the field in which you enter the CALC operator, the grouping is changed and the results are altered.
  
Paradox gives the new field a name based on the calculation. You can use the AS operator to give the calculated field another name.

Using the CALC operator with summary operators

In a query, you can calculate new fields for each record, as well as calculate statistics (like total and average) for groups of records.

Use summary operators with the CALC operator to count, summarize, average, and find the minimum or maximum values in the fields of your tables.

Like all CALC queries, those using groups also create a new field in the Answer table. Paradox automatically names the new Answer-table field according to the group calculation. You can use the AS operator to rename the new field.

Performing calculations with QBE queries

The CALC operator performs calculations on the information in tables. Use CALC to

  construct and evaluate mathematical expressions
  
  combine values from two or more fields
  
  combine field values with constants
  
  create a new field with a constant value
  
CALC capabilities

Using the CALC operator, you can

  specify selection conditions to define the records on which to perform calculations
  
  type the CALC expression itself in any field of the query image
  
  use CALC with alphanumeric values and with summary operators
  
  use values from several tables in a calculation
  
  use example elements in the CALC expression to refer both to values in the same table and to values in other tables
  
Rules for creating example elements

Example elements can contain any alphabetic characters (A-Z, a-z), digits (0-9), or both. They must not contain spaces, or any of the following characters:

* ( ) - + / .

Paradox assumes you have completed the example element when you do any of the following:

  Move to another field, line, or query image.
  
  Press SPACEBAR.
  
  Type one of the characters that can't be part of an example element.
  
Subsequent characters you type display in normal text.

Querying with inclusive links

QBE queries that use example elements to link tables together usually retrieve all the records in one table that match records in another table. This type of query represents an exclusive link and is sometimes called an inner join.

To produce an Answer table that includes those records that do not match records in the table to which they are linked, use the Paradox inclusion operator (!). This type of query represents an inclusive link and is sometimes called an outer join.

Add the inclusion operator (!) to an example element in a query to retrieve all of the records in that table, whether or not they match records in another table. You can also add selection conditions to define the set of master records included in the answer:

  Use multiple inclusion operator (!) to retrieve all the records from more than one table.
  
  Use inclusion operator (!) in a query that contains an arithmetic expression.
  
  Use both inclusive and exclusive links in the same query.
  
Selection conditions with inclusive links

You can specify selection conditions for inclusive links just as you can in other queries. This lets you fine tune either the set of master records or the lookup table records to be matched with them.

If you set selection conditions for the master table, the resulting Answer table contains only those records that match the specified selection condition. The Answer table will still contains all of those matching records, whether or not they are matched in the lookup table.

Rules for linking tables

1. You cannot use both an inclusive and an exclusive link in two linked lines.

For any two linked lines in a QBE query, you can use either an inclusive link (!) or an exclusive link to associate them, but you cannot use both. This is because an inclusive link includes all the records from the master table; whereas an exclusive link includes only records whose values in the linked fields match each other. If you use both kinds, Paradox has no way to decide which link to process first. The resulting Answer table would be different depending on the sequence.

You will not violate this rule if you remember that you can use the inclusion operator (!) with any given example element only once per line and twice per query. In other words, you can use only one type of link to associate any two lines in a query.

2. You can use an inclusive and an exclusive link in the same query.

You can use both exclusive and inclusive links in the same query as long as they do not both involve the same pair of lines. When you have both types of link in one query, they are processed in order from least to most inclusive:

1	Exclusive links, which do not retrieve records that are not matched by records in another table, are processed first.

2	Asymmetrical inclusive links (with both master and lookup tables), which retrieve all of the records from the master table but only the matched records from the lookup table(s), are processed next.

3	Symmetrical inclusive links (with only master tables), which include all records from both tables, are processed last.

By processing exclusive links before inclusive links, Paradox guarantees consistent results to its queries. If you want Paradox to process the links in some other order, you must break the question into separate queries.

Rules for live query views

Not all queries can return live query views. A live query view has the following restrictions:

  You can create a live query view only on single-table queries.
  
  You must use the CheckPlus operator. The live query view cannot be sorted; therefore, Check, CheckDescending, and GroupBy checks are not allowed.
  
  You cannot use the Sort answer table button or the Sort settings on a live query view.
  
  You cannot use calculated fields in a live query view.
  
  Multi-line OR queries are not allowed.
  
  The selection conditions you specify in the query must be capable of being expressed as a filter. This means the following query structures are not allowed:
  
	References to one field in the selection condition of another field.

	References to aggregates in the selection condition.

	Use of the @ wildcard operator.

	Use of the ".." wildcard operator before selection conditions. Use of the .. wildcard operator after a selection

condition is allowed, as in the example Canada.., and produces a case-insensitive answer set.

Guidelines for querying sets

To query a set, follow these general steps:

  Define the set.
  
  Define groups to compare to the set.
  
  Select special groups with set comparisons. Use the following set-comparison operators to compare the set to other records or groups of records:
  
  ONLY
  
  NO
  
  EVERY
  
  EXACTLY
  
Using wildcards to match a pattern

Paradox provides two wildcard operators to match patterns of characters in QBE queries. Although the LIKE operator is useful for finding inexact matches in alpha fields, wildcard operators give you more flexibility.

You can type any combination of uppercase and lowercase letters and the query will produce the same results.

The .. wildcard operator

The .. wildcard operator matches any series of any number of characters, including blank spaces. The .. wildcard operator is case-insensitive.

Pattern	Matches

G..	Giant, gigantic, Georgia

g..t	Giant, gross weight

..D	Grand, Elm Road

..e..s	Phillip Edward Wilson, roses

7..5	7485, 70,005

6/../96	6/01/96, 6/25/96

The @ wildcard operator

The @ wildcard operator matches any single character (letter or number). You can use any number of @ characters to specify a pattern. The @ wildcard operator is case-insensitive.

When you know how many characters are in the pattern you're looking for, you can use that number of @ wildcard operators instead of using the .. wildcard operator.

Pattern	Matches

m@@e	Mike, more, made

wom@n	Woman, women

s@@@@	Smith, Smyth, scent

19@2	1922, 1972, 1992

Field type restrictions

You can use these operators in any field except in binary, graphic, OLE, or logical fields.

To retrieve values from a memo or formatted memo field, you must use the .. wildcard operator to specify a pattern-selection condition. Typing an exact match in these field types means typing the entire memo value; to prevent this unnecessary effort, Paradox does not allow it. You can also use the @ wildcard operator to specify a pattern match in these field types, but you must use it in combination with the .. wildcard operator.

Using wildcards with numbers

If the meaning of a comma or period is not clear, you must help Paradox understand the symbol's meaning with double quotation marks or spaces. The meaning of a comma or period will not be clear as a thousand separator if you are specifying a pattern match with the .. or @ wildcard operators; therefore, do not type thousand separators when you specify a numeric pattern with .. or @.

If there is a chance that a decimal or thousand separator will be confused with the .. or @ wildcard operator, use quotation marks. For details, see "Rules for entering numbers in QBE queries."

Paradox considers only significant digits in Paradox number fields when you use wildcard operators. For example, @@@.@ matches 400.70, because the last 0 isn't significant. By contrast, @@@.@@ doesn't match 400.70 for the same reason.

Example

The following query finds all stock having a list price of $18 and any number of cents.

dBASE numbers

A dBASE number field has trailing zeros to the right of the decimal place. Therefore, you must add the .. operator to the end of a numeric pattern, even if you are trying to match the last digits. For example, ...95.. will match all numeric values ending in .95, but ...95 will not match

Using wildcards with dates

When you enter date values for exact matches in a query image, you can use any date format that Paradox supports, including custom formats.

However, when you use a wildcard to find a date, the pattern you define with the wildcard operator must reflect the date format you have set in both the Borland Database Engine (BDE) Configuration Utility and the Regional Settings in the Windows Control Panel. The BDE and Control Panel date settings must match.

Example

If the date format set in both BDE and Control Panel is mm/dd/yy, you can find orders placed in May of 1995 like this:

If you have another date format set, use that format in the wildcard query.

Query building methods

Paradox provides four different methods to create queries for the database:

  Query By Example (QBE) is a popular query building method from earlier versions of Paradox. To perform a QBE query, you give Paradox an example of the result you want. You use selection conditions and example elements to define the query.
  
  The Visual Query Builder (VQB) provides a method to generate SQL queries without typing any SQL code. You create the query by choosing tables, fields, and other options through a graphical interface while the SQL code is generated in the background. You can see the SQL code being generated in the SQL editor as you choose options in the VQB window. For information about the VQB, see "Working with the Visual Query Builder."
  
  Direct coding in the SQL Editor is an advanced method for experienced database developers who have expertise in SQL.   You can also use the SQL Editor to modify queries that were previously created through the Visual Query Builder or the Query Expert. For more information about SQL, see "Using the SQL Editor" and "Using Local SQL."
  
  The Query Expert is the easiest method and will appeal to new database users. It takes you though a step-by-step process to create commonly-used query models. To launch the Query expert, click Tools  Experts, and then double-click the Query icon.
  



