TCL Command Format

TCL COMMAND FORMAT

The primary TCL commands share a general format, one that allows a very loose ordering of keyword clauses for a more natural usage.

The control keywords ONLY and USING are exceptional in this regard. ONLY must appear just before or after the table specification, while the USING clause may appear only immediately after the table specification. This is illustrated in the format below.

The specifications for the various keywords and information on how to use them may be found later in this chapter.

Command [ONLY] [DICT] tablename [USING alt_tablename] [selection] [sort] [ [control] display [override]...] [format] [modifier] [(options)]

LIST

COUNT

SELECT

Each of these keywords is documented later in this chapter

Note The key column (@ID) of a table is always displayed in a report unless you use the format modifier keyword ID-SUPP.

If the sum of the length of all the display columns in the report is wider than the screen, the View window will allow you to pan to see the text that isn't visible. If you have disabled the View window, the text will wrap. On a printer, text that's too wide for the page will wrap. Screen and printer width is set using the TCL commands SET-CRT and SET-LPTR.

Each of these keywords is documented later in this chapter.

TCL format modifier keywords

Certain keywords in TCL change the format or output specifications of the report.

These format modifiers control such attributes of the report as spacing, column headings, pausing between pages, and printing to the printer.

Using format modifiers

The various format modifier keywords can be inserted anywhere into the TCL command (as long as they are not in the middle of a clause). If more than one format modifier is included in the command, they do not need to be specified together.

The format modifier keywords are:

COL-HDR-SUPP, ID-SUPP, DBL-SPC, LPTR, DET-SUPP, NOPAGE, HDR-SUPP or SUPP

DET-SUPP will cause the report to suppress printing the detail about each row used to calculate a total or average, and print only the total or average and subtotal and subaverage

Examples of format modifier keywords

LIST CUSTOMERS ID-SUPP COMPANY ADDRESS CITY LPTR

This command will print to the printer only the company, address and city columns (no key column) of the CUSTOMERS table.

LIST PHONE_LIST BY NAME COL-HDR-SUPP NAME PHONE DBL-SPC

This report will print out names and phone numbers double-spaced, bar will include no headings, column headings or "xxx: rows processed" message.

SORT INVOICES BY INVOICE_DATE BREAK-ON INVOICE.DATE TOTAL INVOICE.AMOUNT DET-SUPP

This report will sort the invoices into date order, then display the invoices with a break at each new invoice date. Only the subtotals and total will be printed; no individual invoice amounts will appear.

TCL format override keywords

Three keywords in TCL enable you to temporarily override the format specifications (justification length, output conversion, and column heading) defined in the dictionary for a column. The capability to change the format of a column temporarily can save you the trouble of making changes in the dictionary before creating TCL.

The keyword and override specifications follow the name of the column in the TCL command to be displayed. Anytime text appears in the override specification, the specification must appear m quotes.

The keywords available to specify a format override are:

JUSTLEN        Override justification and/or length

OCONV                Override output conversion

COLHEAD        Override column heading

For example, the following TCL command includes a format override specification for the column COMPANY:

LIST CUSTOMER COMPANY JUSTLEN "L40"

In this example, the COMPANY column will be displayed with a left justification, 40 characters wide. The justification and length specified for the COMPANY column in the dictionary of the CUSTOMER table are ignored in this report.

Each format override keyword is listed separately later in this chapter.

TCL throwaway words

To make TCL commands easier to construct and to read, you can include "throwaway" words in the command. These are words that do not affect execution of the command in any way, but that make the TCL command into a more natural, English-like sentence.

Following are the throwaway words that are available in TCL and examples of how they can be used:

Word                        Example

A, AN, ANY                LIST COMPANY WITH ANY NAME

ARE                        ROWS ARE GREATER THAN

DOES                        LIST PAYROLL IF IT DOES CONTAIN

FILE                        SORT CUSTOMERS FILE BY CITY

FOR                        …FOR THE COMPANY

HAS, IS                        SELECT PAYROLL IF IT HAS

IT                                SORT PAYROLL IF IT HAS

ITEMS                        SORT CUSTOMER ITEMS

OF                                LIST CUSTOMERS WITH STATE OF

SHOW,                        LIST CUSTOMERS SHOWING COMPANY PHONE

SHOWING

THAN                        IF IT IS GREATER THAN

THE                        LIST CUSTOMERS IF THE ST CONTAINS

TCL keywords

The rest of this chapter is an alphabetical listing of the keywords used in TCL commands. Each keyword entry includes a description, a diagram of the proper syntax for the keyword, and examples of the correct use of the word.

AVERAGE

AVERAGE is used to generate the average of a numeric column and display it at the end of the report. If a BREAK-ON has been specified, AVERAGE will also display subaverages at each break point.

AVERAGE column

Using AVERAGE

If BREAK-ON has been specified, AVERAGE will also output subaverages at each BREAK-ON. Note in the example that the BREAK-ON and AVERAGE columns can be different.

The final average appears at the end of the report, and is identified by three asterisks  (***) in the leftmost column of the report. To substitute an option for the three asterisks, see GRAND-TOTAL.

Any number of columns can be averaged in the report. Note that AVERAGE can be used with the modifier DET-SUPP (detail suppress) to create reports with only average values.

Correct use of AVERAGE keyword

LIST CUSTOMERS BY ST BREAK-ON ST AVERAGE INVOICE_BALANCE

This command will list all CUSTOMERS by order of state, putting a break between each state. At each break, the average invoice balance for all customers in that state will be displayed. An average of all invoice balances will be displayed at the end of the report.

BREAK-ON

BREAK-ON causes R,/LIST to put a physical break in the report as the value of a column changes.

BREAK-ON column  [options]

Using BREAK-ON

Use BREAK-ON with columns that have been sorted. For example:

LIST CUSTOMERS BY CUST_ST BREAK-ON CUST_ST

Multiple BREAK-ON columns are permissible. If more than one BREAK-ON is indicated, the report will break when the value of any one BREAK-ON column changes.

BREAK-ON is frequently used in conjunction with the TOTAL keyword. If TOTAL is specified. subtotals will be printed at each breakpoint in the report (see TOTAL for details).

The specific action that BREAK-ON will take when a column value changes is dependent on the options specified by the user. Options can be included with BREAK-ON by specifying them after the column.

Text options are enclosed within double quotes. Control options ('B' or 'UU') are single-quoted within double quotes. For example:

BREAK-ON EMPLOYEE NO "Year to date: 'UU'"...

 The following options are available:

Note The BREAK-ON 'L' option suppresses a line, while the HEADING 'L' option causes a blank line to be inserted.

'P'                PAGE: Prints a new page each time the column breaks.

'U'                UNDERLINE: Prints a line over all subtotals.

BY and BY-DSND

Use the filter keyword BY with TCL commands to sort lists or reports.

BY column

or

BY-DSND columnUsing BY and BY-DSNDBY arranges the list or report according to the order of the contents of a column. BY does not sort the data in the table. Instead, it generates a list of keys that represent how the data appears if sorted by a certain column.

To create a list or report sorted in descending (reverse) order, use the keyword BY-DSND

Sorting by more than one column

Multiple BY clauses can be specified in an TCL command. If so, the leftmost BY clause becomes the primary sort column. The second BY clause is used to sort data within the groups created by the primary sort column, etc. For example, the command:

LIST CUSTOMERS COMPANY CITY ST BY ST BY CITY

would produce a report that looks like this:        COMPANY…………        CITY……………        ST        BYTES,   INC.                GLENDALE                CA        ARROW CORP                LOS ANGELES                CA        ACME                        BELLEVUE                WA

 XXX CO.                EVERETT                WA

 SMITH CORP.                TACOMA                WA

In this report, all like states (the primary sort column) are grouped together. Within the state groupings, the rows appear in city order (the secondary sort column).

Sorting multivalued columns

If the column being sorted is multivalued, the BY keyword will cause an "exploding sort". In an exploding sort, each individual value in a multivalued column is treated as if it were in a row by itself. The result is a list or report with an entry for each value in the multivalued column of each row being processed.

Lists or reports created with an exploding sort will have a two-part key for each row being processed. The first part of the key will be the row's original key. The second part of the key, separated from the first part by a value mark, will be the value number within the multivalued column of the sort data.

For example, a CUSTOMER row might have several contact names. If a report were created that is sorted by contact name, a portion of the report might look like this:

 CUST.NO.        COMPANY...        CONTACT  ....

 C100                ACME                DAPHNE        C100                ACME                ROXANNE        C100                ACME                SABRINA        C100                ACME                ZACHARYHere, the original customer row with four contact names has been "exploded" into four entries on the report.

Correct use of BY keyword

LIST CUSTOMERS BY ST

This commands lists rows from the CUSTOMERS table sorted into state order.

SELECT CUSTOMERS BY ST BY ZIP BY COMPANY

This command creates an active list o/keys out of rows in the CUSTOMERS table. The list is sorted by state and by ZIP code. Within each ZIP code, the list is sorted by COMPANY.

SSELECT CUSTOMERS BY-DSND INVOICE AMT

This command creates from the CUSTOMERS table an active list of keys sorted by invoice amount, with the largest invoice amount at the top of the list. Within each like invoice amount, the list is sorted by key.

CASESENS and CASEINSENS

The CASESENS keyword can be used in TCL queries to force a case-sensitive search of data. The CASEINSENS keyword can be used in queries to force a case-insensitive search of data. These keywords can be used against indexed or non-indexed tables and columns to override the existing case sensitivity for queries defined in the environment.

WITH [EVERY]  [CASESENS | CASEINSENS]  column comparison [value]

Or

LIMIT [CASESENS] column comparison  [CASEINSENS]  range

Using CASESENS and CASEINSENSUse the CASESENS keyword with any one of the selection keywords below to override the query case insensitivity in the user environment (Options-Configuration-Environment-Query-Query Processes from the Opening menu). This keyword will have no effect if the user environment setting is already case sensitive.

Use the CASEINSENS keyword with any one of the selection keywords below to override the case sensitivity setting, if case-sensitive search is defined in the user environment.

Note For information about the general syntax of LIMIT and WITH clauses, see those keywords elsewhere in this chapter.

Correct use of CASESENS keyword

LIST CUSTOMERS WITH CASESENS LAST NAME = "Smith"

Lists all customers in a table with the name "Smith". The customer names  "SMITH" and "smith" will not be included in the report.

Correct use of CASEINSENS keyword

LIST CUSTOMERS WITH CASEINSENS LAST NAME = "Smith"

Lists all customers in a table with the name Smith, regardless of the case of the data. The customer names "Smith", "SMITH", and "smith" will be included in the report.

COLHEAD

Use COLHEAD to create an alternate column heading for a column.

column COLHEAD "col_heading"

Correct use of COLHEAD keyword

LIST INVOICE INV_DT COLHEAD "Inv Date"

In this example, the heading "Inv Date" will appear above the column for the column INV_DT. If the display length of the INV_DT column is less than eight characters, the column heading specified in the command will be truncated appropriately.

FOOTING

The FOOTING keyword will cause the TCL commands LIST and SORT to produce a running page footing (text at the bottom of each page). The footing can contain text, page numbers, dates, and other information.

FOOTING "text [ 'options ' [,format]]

Using FOOTING

The footing clause consists of the keyword FOOTING followed by the footing        specifications in double quotes.

 Note You must use double quotes.

The format of the options for FOOTING are exactly like those of the keyword HEADING. For example:

 FOOTING  "Report produced On 'D'"

For details about the options available in FOOTING, please see the key word        "HEADING" elsewhere in this chapter.

Correct use of FOOTING keyword

SORT INVOICES BY INV_NO FOOTING "Invoice Listing"

_

This is an example of a command using a footing clause. Below are more examples of footing clauses and their output if used with a similar command.

FOOTING "Invoice Listing for  'D'"

Invoice Listing for 01 JAN 1991

FOOTING  "Invoice  Listing  'S10' Page 'PP'"

Invoice Listing      Page  1

GRAND-TOTAL

GRAND-TOTAL changes the text that accompanies the final total generated by the TOTAL keyword.

GRAND-TOTAL  "text['options']"

         Using GRAND-TOTAL

Use the TOTAL keyword to display a total at the bottom of the report (in addition to any subtotals generated). The final total at the end of the report will be marked by the display of three asterisks (***) in the leftmost column of the report. Use the GRAND-TOTAL keyword to change the output from three asterisks to some other text.

options        The following options enclosed within double quotes are available:

Note GRAND-TOTAL 'L' suppresses a line, while the HEADING 'L' option causes a blank line to be inserted.

'P'        PAGE: Prints the grand total on a new page.

'U'        UNDERLINE: Prints a row of equal signs ( === ) under the grand total.

Correct use of GRAND-TOTAL keyword

LIST CUSTOMERS TOTAL INVOICE_AMOUNT GRAND-TOTAL "Invoice TOTAL"

The phrase "Invoice TOTAL" will be printed in the leftmost column of the report at the end, across from the total value for the INVOICE_AMOUNT column. If the column of the report is narrower than 13 columns, the phrase will be truncated.

HEADING

The HEADING keyword will cause the TCL commands LIST and SORT to produce a running page heading. The heading can contain text page numbers. dates, and other information.

HEADING "text  [ 'options ' [, format ] ] ',

Using HEADING

The heading clause consists of the keyword HEADING followed by the heading specifications in double quotes.

Note You must use double quotes.

For example:

HEADING "Invoices sorted by date 'L' Page  'PP'"

‘D’        DATE: Inserts the current date (see below for formatting output).

‘L’        NEW LINE: Specifies a line feed.

‘LL’        BLANK LINE: inserts a blank line (i.e., two line feeds).

‘N’        NOPAGE: Suppresses automatic paging.

‘P’        PAGE: Inserts the current page number.

‘Sn’        SPACES: Embeds n number of spaces into the footing.

‘T’        TIME: Inserts the current time (see below for output formatting).

Time and data output formatting

You can format the output of heading and footing time and date information in the same way you can specify time and date display in a window. by including a output format specification. Use any format code that you would use at a window prompt (or in the R/BASIC OCONV statement).

The general syntax for formatting the time and date display is:

HEADING "text  'option[,format] '"

where format is any format specification such as "D2/", "MT", etc. For derails on what formats you can use, see the statement OCONV in the R/BASIC manual.

For example, the format "D4." produces output in the format "mm.dd.yyyy" So, to specify a footing that contains a date in that format, you would include that argument after a comma that immediately follows the T, D, or TD option value.

HEADING  “Output date:   'D, D4  ‘”

This would produce the heading

Output date:  09.30.1992

Correct use of HEADING keyword

SORT INVOICES BY INV.NO HEADING "Invoices Sorted by INV_NO"

This is an example of a command using a heading clause. Below are examples of report headings created by using a heading clause.

 HEADING “Invoices Sorted by INV_NO ‘L’ AS of  ‘DL’”

Invoices Sorted by INV NO

As of 01 JAN 1991  '

HEADING "Invoices Sorted by INV_NO ‘SI0’ page  ‘PL’ As of ‘T’”

Invoices Sorted by INV_NO  Page 1

As of 09:00:00 01 JAN1991

HEAD/NG “Today's report for table ‘FL’ Page  ‘PP’”

Today’s report for table INVOICES

Page I

JUSLEN

Use the keyword JUSTLEN to override the justification or display length of a column as stored in the dictionary. You can override the justification or display length separately, or can combine the override specification in one command.

column JUSTLEN "just"  | length  |  "justlen"

Using JUSTLEN

The override specification for JUSTLEN can be an integer value, alpha characters, or a combination of these.

L        Left justification

R        Right justification

T        Text (paragraph) justification

C        Center justification

Correct use of JUSTLEN keyword

LIST CUSTOMER COMPANY JUSTLEN 40

This displays the COMPANY column with a length of 40 characters.

LIST CUSTOMER COMPANY JUSTLEN "R"

Displays the COMPANY column right-justified.

LIST CUSTOMER COMPANY JUSTLEN "L40"

In this example, the COMPANY column is displayed left-justified, 40 characters wide.

LIMIT

The LIMIT keyword restricts the output of data from a multivalued column to those values that meet specified criteria.

LIMIT  [CASESENS | CASEINSENS] Column comparison  [value compare]

or

LIMIT column range

Using LIMIT

Limiting ("print limiting") is useful when you want to display only a portion of a multivalued column. If the column is displayed normally, the entire contents of the multivalued column will be displayed for each row. By specifying LIMIT criteria, however, you can instruct the report to output only those values in the multivalued column that meet criteria you establish.

LIMIT works like a WITH clause, but applies only to the display of values for one column. WITH selects rows out of the entire table for processing; LIMIT selects values out of one column for display.

CASESENS        Use the CASESENS keyword to force a case-sensitive search of data. Use the

CASE-        CASEINSENS keyword to force a case-insensitive search of data.

INSENS

         LIST CUSTOMERS LIMIT INV_DATE  >  “1-1-91”

LIST CUSTOMERS LIMIT PAID_DATE > DUE DATE

FROM value TO value

Or

BETWEEN value AND value

 Examples might be:

LIST CUSTOMERS LIMIT INVOICE DATE FROM “1-1-91” TO “11-31-91”

LIST CUSTOMERS LIMIT INVOICE_AMOUNT BETWEEN 1000 AND 5000

Each value in the multivalued column w 11 be compared to the range If the value in        the column falls within the range specified by the range (inclusive), it will be displayed.

Correct use of LIMIT

LIST CUSTOMERS COMPANY LIMIT INVOICE_AMOUNT  >=  "1000"

This command lists the company name of all customers. In addition, all invoice amounts of $1,000.00 or greater in the multivalued column INVOICE_AMOUNT are displayed. Invoice amounts of less than $1,000 are not displayed, even though they may exist.

LIST CUSTOMERS COMPANY LIMIT INVOICE_PAID_DT GT INVOICE DUE_DT

This command lists the company name of all customers, and lists all values from the multivalued column INVOICE_PAID_DT in which the date is greater than the contents of the column INVOICE_DUE_DT.

LIST CUSTOMERS COMPANY LIMIT INVOICE.DATE FROM "1-1-91" TO "12-31-91"

This command lists the company name of all customers, and displays all of the values from the multivalued column INVOICE_DATE in which the value falls into the range of 1-1-91 to 12-31-91 (inclusive).

OCONV

Use the keyword OCONV to specify a new or alternate output conversion (output format) for a column. The override specification can be any valid output conversion or formatting specification. Output conversions are most typically used to format columns containing data for money, date, time or logical (Boolean) values (example: "Y" and N").

column OCONV "Output format"

Correct use of OCONV keyword

LIST  INVOICE  INV  DT  OCONV  "D4/"

In this example, the column lNV_DT will be displayed with a date conversion that prints four digits for the year, and delimits month, day and year with ,/, (slash).

LIST CUSTOMER PHONE OCONV

The phone number will be displayed right justified, with the area code enclosed in parentheses.

ONLY

The ONLY keyword may be used with the LIST or SORT command to cause the report to list only the keys of the rows being displayed. The TCL command LO is a synonym for "LIST ONLY", but is faster.

LO tablenameLIST ONLY tablenameSORT ONLY tablename

Using ONLY

Use ONLY when you want to guarantee that the report will list only keys. ONLY suppresses the default column headings specified in @CRT and @LPTR. Even if display columns are listed in the command, ONLY will suppress the output of all columns except the key.

ONLY is useful when listing the contents of tables that contain data that will not display normally, such as R/BASIC object code rows. '

To change the width and column heading of the ONLY column output, edit the row in the dictionary of tablename.

Note ONLY can either precede or follow tablename.

Correct use of ONLY keyword

LIST ONLY VOC

Displays only the keys of rows in the VOC table.

SORT ONLY CUSTOMERS

Displays only the keys of rows in the CUSTOMERS table after sorting the table by key.

LIST DICT CUSTOMERS ONLY  (p)

Displays only the column names of rows in the dictionary of the CUSTOMERS table, and prints the report to the printer.

TOTAL

TOTAL is used to generate the sum of a numeric column and display it at the end of the report. If a BEAK-ON has been specified, TOTAL will also display subtotals at each break point.

TOTAL column

Using TOTAL

TOTAL will accumulate the value of each column being totaled, and display the result at the end of the report.

If a BREAK-ON has been specified, TOTAL will also output subtotals at each BREAK-ON. Note in the examples that the BREAK-ON and TOTAL columns can be different.

The final total appears at the end of the report, and is identified by three asterisks  (***) in the leftmost column Of the report. To substitute an option for the three asterisks, see GRAND-TOTAL.

Any number of columns can be totaled in the report. Note that TOTAL can be used with the modifier DET-SUPP (detail suppress) to create reports with only total values.

Correct use of TOTAL keyword

SORT CUSTOMERS BY ST COMPANY ADDRESS TOTAL BALANCE

This command will generate a report in which the total of the BALANCE column will appear at the bottom of the report.

LIST INVOICES BY COMPANY BREAK-ON COMPANY TOTAL OUTSTANDING BAL

In this report, a total of the column OUTSTANDING_BAL appears at the bottom. In addition, a subtotal for the column will be generated for each company.

USING

The USING keyword causes the report to use a dictionary other than the dictionary of the table being listed or selected.

LIST table USING alt table

The USING keyword permits you to maintain one master dictionary for several tables. This is useful in situations where tables have identical layouts, such as master and history tables. The master table can have an associated dictionary, and the history table can simply refer to that dictionary when reports are generated -- no separate dictionary is required for the history table. This minimizes both maintenance and storage requirements.

Note TCL will always look first to the dictionary of table. If redirected with the USING keyword, TCL will look into the alternate dictionary. In either case, however, TCL will always also look in the dictionary of the VOC table for column and keyword definitions. The dictionary of the VOC table can thus serve as a global dictionary for all tables.

Correct use of USING keyword

LIST INVOICES_HISTORY USING INVOICESThe dictionary of the table INVOICES is substituted for the dictionary of the table INVOICES_HISTORY.

WITH

The filter keyword WITH is used with R,/LIST commands to generate a subset of rows to be processed.

with_keyword  [EVERY] [CASESENS  |  CASEINSENS]  column comparison  [value  | MATCHES pattern  |  range]

Using WITH

Use WITH in an TCL command when you want to define which rows are to be selected for processing. The WITH clause will cause the TCL command to examine each row in the table and match it against the criteria following the WITH keyword. If the row matches the criteria, it will be added to the list of rows to be processed. If the row does not meet the selection criteria, it will be ignored.

WITH does not actually create a table of the subset of rows selected. Instead, it creates a list of keys that represent the rows meeting specific criteria.

with_keyword        The with_keyword may be WITH, WITHOUT or any of the following keywords:

WITH

BECAUSE [IT HAS], BECAUSE [ITS]

THAT [HAS]

WHEN [IT HAS], WEN [ITS]

IF [IT HAS], IF [ITS]

WHENEVER [IT HAS], WHENEVER [ITS]

INCLUDE, INCLUDING

WHICH [HAS]

WITHOUT

EXCEPT IF [IT HAS], EXCEPT IF [ITS]

UNLESS FIT HAS], UNLESS [ITS]

EXCLUDE, EXCLUDING

WITH NO

INSENS

=                EQ, EQUAL (TO), THAT IS, LIKE, ARE

<                LT, LESS THAN, BEFORE, UNDER

<=                LE, LESS THAN (OR) EQUAL TO

<>, #                NE, NOT, NOT EQUAL (TO), DOESNT, ARENT, ISNT

>=                GE, GREATER THAN (OR) EQUAL TO, FROM

>                GT, GREATER THAN, LATER THAN, AFTER, OVER

]                STARTING (IN)

[                ENDING (IN)

[ ]                CONTAINING MATCH, MATCHES

The comparison operators "[ ]" (for CONTAINING)," ] "(for STARTING WITH), and" [ "(for ENDING WITH) can be used in a number 0f ways. For example, these two commands are equivalent:

LIST CUSTOMERS WITH ANY CONTACTS CONTAINING "SMITH"

LIST CUSTOMERS WITH CONTACTS [] "SMITH"

The following example list invoices with INV_NO beginning with "C":

LIST INVOICES WITH INV_NO ] "C"

This next example reports the number of customers with company name ending in "INC.":

COUNTROWS CUSTOMERS WITH COMPANY [ "INC."

These next three commands use comparison words as an actual part of the value. They

are equivalent to the previous three examples:

LIST CUSTOMERS WITH CONTACTS "[SMITH]"

SORT INVOICES WITH INV_NO "C]"

COUNTROWS CUSTOMERS WITH COMPANY "[INC."

Some examples of patterns follow:

nA                Use nA to test for n numbers of alphabetical characters.

NN                Use nN to test for n numbers of numerical characters.

nX                Use nX to test for n number of any characters.

NZ                Use nZ to test for up to n number of characters.

"string"                Use "string" to test for exact text.

Note Pattern matching is also discussed in the chapter "Creating windows with Paint" in the User's Guide.

Ranges for the data values are specified by using the keywords:

FROM…T0

or

BETWEEN...AND

The starting comparison value follows FROM or BETWEEN. The ending comparison value follows TO or AND. Examples of ranges:

LIST INVOICES WITH INV_TOTAL FROM '1000' TO '10000'

LIST INVOICES WITH INV_TOTAL BETWEEN '1000' AND '10000'

Multiple WITH clauses

Multiple WITH clauses can be specified ln a command. The clauses are linked together using the operators AND or OR (an OR. is implied if no operator is provided).

Using AND

The effect of using AND between clauses is to narrow the range of rows selected. If clauses are connected with AND, the rows to be selected must meet the criteria specified in every clause. For example, a command might contain logic like this:

command tablename WITH clause AND WITH clause

For example:

LIST CUSTOMERS WITH ST = 'WA' AND WITH CITY = 'SEATTLE'

Rows to be selected must meet the conditions specified in both WITH clauses.

Using OR

The effect of using OR between clauses is to expand the range of rows being selected. Rows will be selected if they meet criteria specified in any one of the multiple clauses.

The syntax is:

command tablename WITH clause OR WITH clause

For example:

LIST CUSTOMERS WITH ST = 'WA' OR WITH ST = 'CA'

Rows to be selected can meet the conditions specified in either WITH clause.

If the command contains multiple WITH clauses connected with OR, the system can assume certain defaults. If the WITH clauses are all searching the same column and using the same comparison, the WITH clauses can simply "stack up" the comparison values. Advanced Revelation will assume the default OR and fill in the redundant "WITH column" portions of the command.

For example, these two commands are equivalent:

LIST CUSTOMERS WITH ST = "OR" OR WITH ST = "WA"

LIST CUSTOMERS WITH ST = "OR" "WA"

Using both AND and OR

Multiple WITH clauses can also be connected together with a combination of AND and OR. In this case, the command processes the WITH clauses from left to right, tying together the clauses connected with AND, and separating them from those connected by an OR. In other words, AND has a higher priority than OR.

For example, the command:

LIST CUSTOMERS WITH STATUS = 'A' OR WITH STATUS 'P' AND WITH CITY = 'LOS ANGELES'

will find all customers whose status is "active" or those whose status is "potential" and who also live in Los Angeles. Customers whose status is "potential" but who do not live in Los Angeles will be ignored.

Each clause separated by an OR is treated as a completely different selection criterion. In the above command, customers will be selected either if they are active, or if they are potential and live in Los Angeles. The clauses linked with an AND are treated as a single unit -- each individual clause within the unit connected by AND must be true for the linked clauses as a whole to be true.

Using parentheses

To change the priority of processing, parentheses can be used to link multiple WITH clauses. For example, the command:

LIST CUSTOMERS (WITH STATUS = 'A' OR WITH STATUS 'P') AND WITH CITY = 'LOS ANGELES'

will find customers who are either active or potential, and additionally live in Los Angeles. Customers outside Los Angeles, or those who are in Los Angeles but not active or potential, are ignored.

Correct use of WITH keyword

LIST CUSTOMERS WITHOUT ZIP = 98523

List all customers except those in which the ZIP code is 98121

LIST CUSTOMERS WITH ST = 'WA'

List all customers in the Mate of Washington.

SELECT CUSTOMERS WITH INV_TOT GE 7000

Create an active list of keys from those customers whose invoice total is greater than or equal to $7,000.

SORT VOC WITH @ID MATCHES "4A"

Create a report (sorted by key) of all rows in the VOC table whose key consists of four alphabetic characters for example, LIST, SORT, EDIT, etc.).

LIST INVOICES WITH CITY BETWEEN "A" AND "D"

List invoices for customers whose city falls within the range of "A" to "D"  (inclusive- the cities Atlanta and Detroit would be included in the report).

SUMROWS INVOICES PRICE WITHOUT CITY FROM "A" TO "H"

Report the sum total of the contents of the PRICE column for those invoices in which the city column does not fall in the range "A" to "H" (inclusive).

COUNTROWS DICT VOC WHEN @ID CONTAINS  "A"

Report the total number o/items in the dictionary of the VOC table in which the row key (@ID) column has an "A" anywhere within it.

LIST CUSTOMERS (WITH ST MATCHES '2A')

List all customers in which the state column contains two alphabetic characters.

SELECT

The TCL command SELECT creates and activates a list of keys that meet certain criteria. The list of active keys is then available to the next Advanced Revelation command.

SELECT [n] [DICT] table [USING alt_table] [selection] [sort] [keywords] [ (option) ]] Using SELECT

According to specified criteria, SELECT places keys to rows in a table into a key list, temporarily, in memory. The program offers these selected rows to other commands for processing.

When SELECT selects a group of rows, it stores only the keys in the list. Other commands, like LIST or COPYROW, access the data from this list of keys, using the keys us addresses for the data.

Once the program selects and orders the rows, the status line will indicate that a filter is active. At this point the next command can access the list.

To use a list more than once, save and retrieve it by using the SAVELIST and GETLIST commands. Use the CLEARLIST command to deactivate an unwanted list

Once an active list has been used, it is cleared.

Correct use of SELECT command

SELECT PARTS WITH DESC CONTAINING "MOTOR" BY PART NO

RUN PGMPART.ENTRY

SELECT finds the rows in the PARTS table that contain the word 'MOTOR' in the description and sorts them by PART_NO. The R/BASIC program

PGMPART_ENTRY processes only these rows.