OVERVIEW.............The dCLIP Query-Builder System
QUERY MENU...........The Query-Builder Main Menu
BUILD QUERY..........Building a new Query
EDIT QUERY...........Editing a Query
SAVE QUERY...........Saving a Query to the Query Catalog
TAGGING..............Tagging or Untagging Records that match the Query
OVERVIEW
The dCLIP Query-Builder System
Description:
The dCLIP Query Builder provides a method of creating complex
query expressions from a pick-list of available fields,
operators and logical connectors. The Query-Builder was
designed to make it easy and possible for any application user
to design complicated queries to be used for locating,
filtering, counting, tagging, copying, deleting, printing,
etc. records from individual databases or related databases.
Once a query has been created, it may be saved to the
DCQUERY.DBF database to be re-used later at any time. For
example, a query may be created to be used for tagging records
for printing labels. Here's an example of a simple Query
Expression that would be created from the Query Builder:
CUSTOMER-)BALANCE)0.AND.UPPER(CUSTOMER-)CITY)="SAN DIEGO" ;
.AND.CUSTOMER-)STATE="CA"
This Query would be created from a need to find out how many
customers in San Diego, California owe us money. The Query
Builder makes it possible to create this kind of expression
without knowing anything about the Clipper expression
language by displaying a list of database fields and
operators to the user creating the Query.
All query expressions are "LOGICAL" expressions, meaning
that they return a value of .TRUE. or .FALSE. for each
record in the database. These expressions may consists of
a single argument such as:
INVOICE-)SHIP_DATE ) CTOD("10/15/94")
or from multiple arguments such as:
INVOICE-)SHIP_DATE ) CTOD("10/15/94") .AND. ;
INVOICE-)HOW_SHIP = "U.P.S"
The query builder is basically a function named DC_QUERY()
and will pass the created expression back to the calling
program for use by the program in the manner required.
Examples:
1. The Query Builder function may be called to create a
condition for locating like so:
cQuery := DC_QUERY()
LOCATE FOR &cQuery
2. The Query Builder may be called to create a condition
for printing mailing labels like so:
cQuery := DC_QUERY()
LABEL FORM mylabels TO PRINT FOR &cQuery
3. The Query Builder may be called to tag or untag records
for later printing a report like so:
DC_QUERY()
REPORT FORM myreport TO PRINT FOR dc_tagged()
See Also:
QUERY
dc_query()
QUERY MENU
The Query-Builder Main Menu
Description:
The Query Builder menu is accessed by entering the command
QUERY from the dot-prompt or by the DC_QUERY() function in
an application.
The menu will display a listing of all queries in the
DCQUERY.DBF database that match the Alias of the currently
selected database. If the DCQUERY.DBF database does not
exist, it will be created in the directory established by
the SET DCLIP=(path) command in your DOS environment or your
DCLIP.SYS file.
Use the cursor pad keys or the mouse to select any existing
Query in the database to transfer to the Query Window.
Double-Clicking the mouse or pressing (ENTER) will copy the
selected Query Expression into the Query Window at the
bottom of the screen. A Query may also be placed in the
Query Window by building a new query expression.
The Menu Selections include:
1. Build a New Query
2. Edit the Selected Query
3. Append to the Selected Query
4. Save the Selected Query to the Query Catalog.
5. Delete a Query from the Query Catalog.
6. Count Records that match the Selected Query.
7. Browse Records that match the Selected Query.
8. Tag or Untag Records that match the Selected Query.
9. Count Records that have been tagged.
BUILD QUERY
Building a new Query
Description:
To build a new Query expression, select (B) from the EDIT
menu. A sub-menu will appear with the following options:
1. FIELD PICK-LIST
Choose this option if the Query is based on the contents
of a field in either the parent database or one of the
child databases. A pick-list will be displayed with a
listing of all parent and child fields from which to
choose.
After choosing a field, you will then be given an
option of several operators to use to determine how the
contents of the field will be tested.
a. EQUAL TO (STARTS WITH) =
b. NOT EQUAL TO ()
c. LESS THAN (
d. GREATER THAN )
e. LESS THAN OR EQUAL TO (=
f. GREATER THAN OR EQUAl TO )=
g. BETWEEN RANGE RAN
h. EXACTLY EQUAL TO ==
i. EMPTY E
j. NOT EMPTY !E
k. CONTAINS $
l. DOES NOT CONTAIN !$
m. SOUNDS LIKE SL
n. SPECIAL DATE FUNCTIONS DAT
aa. SELECT A YEAR
bb. SELECT A MONTH
cc. SELECT A DAY OF THE MONTH
dd. SELECT A DAY OF THE WEEK
ee. SELECT CURRENT DATE
2. DELETED (*)
Choose this option if the Query is based on the status of
the DELETED flag for each record.
3. TAGGED (û)
Choose this option if the Query is based on the status
of the TAG (logical) field in the parent database or if
the query is based on a set of tags in the DCTAGS array.
4. Expression
Choose this option if the Query is based on the return
value of a logical expression or function.
After Selecting the type of query to perform you must then
select a logical "CONNECTOR". For example, if you are
creating a query that finds all records for customers in
2 specific Zip Codes you would use the .OR. connector,
meaning that the Query would be TRUE if either argument in
the expression is true, like so:
CUSTOMER-)ZIP_CODE="90210" .OR. CUSTOMER-)ZIP_CODE="99999"
In the next example, a Query expression can be created that
uses at least one of each of the above options and uses all
.AND. connectors, meaning that the Query is TRUE only if
ALL the arguments are TRUE, like so:
CUSTOMER-)ZIP_CODE="90210".AND.!DELETED().AND.TAG.AND.;
!CRANKY()
This query may be used to print mailing labels for all
TAGGED and UNDELETED customers who are not CRANKY and who
live in Beverly Hills, California. The CRANKY() function
could be a User-defined function that returns a value of
.TRUE. or .FALSE. depending on whether or not an "X" appears
in a specified position of another field such as a STATUS
field.
EDIT QUERY
Editing a Query
Description:
A Query can be edited in one of two ways:
STRING EDIT
All Queries are saved as an Expression "String" which, when
evaluated, returns a logical .TRUE. or a logical .FALSE.
based on the contents of data in the currently selected
parent and child database record(s).
This expression string is expressed in the Query Window
and may be edited by using the dCLIP memo editor. It is
recommended that the user have knowledge in the proper
construction of a CLIPPER expression when editing Query
expressions otherwise an invalid expression may result
and an error will be displayed.
To STRING EDIT a Query select the desired option from the
EDIT menu.
BROWSE EDIT
Often times, it is necessary to re-use a complex query
expression with different values. For example, let's say
we have a Query saved in the database that looks like so:
DC_RANGE(CUSTOMER-)ZIP_CODE,"80000","90000') .AND. ;
INVOICE-)SHIP_DATE)DATE()-7 .AND. ;
INVOICE-)HOW_SHIP="U.P.S"
Using the BROWSE-style editor, the Zip Code range in
this Query expression can be easily changed by simply
selecting the VALUE to change in the browse window, then
pressing (ENTER) to edit the value.
The Browse-style editor breaks down a Query Expression into
its individual elements for easy modification, then rebuilds
the expression from the edited elements.
To BROWSE EDIT a Query select the desired option from the
EDIT menu.
SAVE QUERY
Saving a Query to the Query Catalog
Description:
After a Query Expression has been Built, Appended to, or
Edited it can be saved to the DCQUERY.DBF "Query Catalog"
file so it may be re-used again.
Each Query Expression that has been saved to the catalog
is saved with a Description of the Query and also is
assigned the ALIAS of the currently selected database.
Only Queries that match the Alias of the currently selected
database are displayed in the Query Pick-list.
To Save the Query that is currently displayed in the Query
Window, choose the appropriate selection from the FILE
menu.
To re-use a Query, select the desired Query in the Query
pick-list window, then double-click the mouse or press
(ENTER). The query expression will be transferred to the
Query Window.
TAGGING
Tagging or Untagging Records that match the Query
Description:
The Query Builder supports two methods of tagging records
that match the Query expression:
1. TEMPORARY TAGS
A public array named DCTAGS will allow up to 4096 records
to be tagged for later displaying, printing, copying, etc.
based on the value returned by the DC_TAGGED() function.
When a record is "tagged", the record number is added to
the DCTAGS array. When it is "untagged", the record number
is removed from the DCTAGS array. This array is scanned
to determine if a record is tagged when using Query
Expressions that include the DC_TAGGED() function. When
the application is quit, this array is cleared, therefore
this method of tagging should be used only for quick and
temporary tagging. This is the default tagging mode if
the currently selected database does not contain a logical
field named TAG. If your database does not contain a TAG
field, then the DCTAGS array will be used for tagging.
Here's an example of how to use a Query expression that
uses the tag array:
cQuery : DC_Query() // Build the query using the
// Tagged option and a field
? cQuery
DC_TAGGED().OR.CUST_NAME="MICROSOFT"
COPY TO TEMP FOR &cQuery
2. PERSISTENT TAGS
A better method of tagging records is to insure that your
database contains a logical field named TAG. This TAG
field is then used by the Query Builder tagging system to
set the value to a logical .TRUE. or a logical .FALSE.
depending on the mode selected. This method allows as
many tags as there are records in the database and is much
more memory efficient. Persistent tags are also stored in
the actual database so they are remembered when the
application is quit and re-started.
When a record is "tagged", the TAG field is set to a logical
.TRUE. When it is "untagged", the TAG field is set to a
logical .FALSE. Here's an example of how to use a Query
expression with persistence tags:
cQuery : DC_Query() // Build the query using the
// Tagged option and a field
? cQuery
TAG.OR.CUST_NAME="MICROSOFT"
COPY TO TEMP FOR &cQuery
TAGGING RECORDS
Records are tagged by selecting TAG from the TAG menu. This
selection will scan the entire database and set the tag on
all records that match the current Query expression in the
Query Window. This tagging system can be used for a variety
of database applications, but probably the most popular
requirement is that of compiling a mailing list from a
database. With the combination of record tagging and
different Query expressions, a mailing list can be quickly
compiled to include only the records desired.
Let's use the example of building a mailing list that includes
only customers in Zip Codes 90000 thru 99999, then later we
decide we want to exclude all Cranky and Bad Address
customers.
1. Clear all Tags
2. Create the following Query Expression:
DC_RANGE(CUSTOMER-)ZIP_CODE,"90000","99999")
3. TAG all records that match the above Query.
4. Create the following Query Expression:
CRANKY.OR.BADADDRESS
5. UNTAG all records that match the above Query.
6. COUNT all Tagged Records to determine the total number
of mailing labels.
7. Print the Mailing labels like so:
LABEL FORM CUSTOMER TO PRINT FOR TAG