TCL Command Format

Top  Previous  Next

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)]

 

CommandCommand may be any of the following TCL commands:

 

LIST

COUNT

SELECT

 

ONLYThe word ONLY instructs TCL to display only the row keys in a table.
DICTIf DICT is specified, the report will be generated against the dictionary portion of a table.

 

TableAny attached Advanced Revelation table. Table is the only argument that is required, other than the command itself. This is more test

 

USINGA USING clause specifies that the operation is to use the dictionary of the alternative table alt table, rather than the table dictionary.
SelectionIf selection is specified, the command will select a subset of rows from table for processing. The selection criteria are either a list of keys or a WITH clause (see the keyword WITH in this chapter).
SORTIf sort are specified, the rows are displayed in a particular order (see the keyword BY in this chapter).
ControlA key word or words that affects the flow of control for the command. If specified, these may be any of the following:

 

AVERAGEProduce a running average for a numeric value.
BREAK-ONProduce a "break" in the report when the value of a column changes.
LIMITDisplay only particular values out of a multivalued column.
ONLYList only the keys in a report.
TOTALProduce subtotals and a grand total for a numeric column.

 

Each of these keywords is documented later in this chapter

 

DisplayIf you are using the commands LIST or SORT, display is the column whose contents will appear in the report. You can display any number of columns in your report. Columns are displayed in the report according the order in which their names appear in the LIST or SORT command.

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.

OverrideIf specified, these keywords will override the display specifications for a column (as defined in the dictionary). The format override keywords are:

 

JUSTLENOverride the justification and display length of a column.
OCONVOverride the conversion (output format) of a column.
COLHEADSpecify a new column heading for a column.

 

Each of these keywords is documented later in this chapter.

 

FormatA key word in format adds formatting features to a report or form. Possible keywords are:

 

FOOTINGDisplay text at the bottom of each page of the report.
HEADINGDisplay text at the top of each page of the report
GRAND-TOTALSpecify text that accompanies a grand total.

 

Modifier.Use a value for modifier to alter the default format or output specifications. Format modifier keywords are discussed in detail under the topic "Format modifier keywords" following.

 

optionsThe additional display or output options that you may include vary according to the command specified. For example, the option "(P)" sends output to a printer. See the documentation for the particular TCL command in "TCL command reference".

 

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

 

COL-HDR-SUPPCOL-HDR-SUPP suppresses the column headings (column titles) that are automatically printed at the top of each page for each column being displayed. It also suppresses the default heading and "rows processed" message (see HDR-SUPP, below).

 

DBL-SPCThe modifier DBL-SPC causes a blank line to be printed between each row on the report. Double spacing a report can make it easier to read, especially if there are many multivalued or text columns being displayed.
DET-SUPPUsed with TOTAL and AVERAGE, DET-SUPP causes a value to be printed at the bottom of the report, and a subtotal or average to be printed at each control break.

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

 

HDR-SUPP or SUPPIf no heading has been specified, TCL reports will include a default heading of the date, time, and page number, and will include the message "xxx row(s) processed" at the bottom of the report. HDR-SUPP and SUPP suppress the default heading and row count.

 

ID-SUPPLIST and SORT will always print the key column in the leftmost column of the report. ID-SUPP causes this column not to be printed.

 

LPTRThe keyword LPTR causes a report to be printed on the current printer device. If PDISK has been executed before the report is run, the output will be routed to an operating system table.

 

NOPAGEThe keyword NOPAGE allows a report displayed on the screen to continue from star to finish without interruption It suppresses the default break at the end of each screen that ordinarily would prompt the user to "press a key to continue". NOPAGE has no effect on reports sent to the printer.

 

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

 

columnEach time the value of the column represented by column changes, the report will indicate (with spacing, special characters, etc.) that a new value is being printed.

 

optionsOptions can be specified that cause BREAK-ON to take specific action when a column changes value. The default action for BREAK-ON is to print a blank line, then three asterisks (***) in the column for the BREAK-ON column, then another blank line. The report then continues.

 

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:

 

TextUse text to substitute for the three asterisks normally printed. The text will be truncated to fit into the column width of the BREAK-ON column.

 

'B'BREAK: Denotes the column whose breaking value is to be inserted into the heading. Must be used in conjunction with the 'B' option in HEADING.

 

'D'DATA: Suppresses redundant totals by suppressing the break data line entirely if the preceding data column contains only one line of data. Useful in condensing a report.

 

'L’LINE: Suppresses the blank line that usually precedes the break data line. Use this when you want to condense a report. This option does not override the 'U' option.

 

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.

 

'UU'UNDERLINE/OVERLINE: Prints a line over and under the subtotals.

 

‘V'VALUE: Prints the value of the breaking column at the break line in place of the three asterisks.

 

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"

col_headingThe format override specification col_heading contains the alternate text to be used in the column heading. If the column heading created in the format override is longer than the column width, the column heading is truncated to fit the column width.

 

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.

 

textSubstitutes text for the three asterisks normally printed. The text will be truncated to fit into the column width of the leftmost column.

 

options        The following options enclosed within double quotes are available:

 

‘L’LINE: Suppresses the blank line that usually precedes the grand total line. This is useful when it is desirable to condense a report. This option does not override the 'U' option.

 

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.

 

'UU'OVERLINE/UNDERLINE: Prints a row of equal signs ( == ) over and 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'"

 

textAny string of characters, including alpha, numeric and punctuation except double and single quotes.
optionsOptions can be embedded into the heading text to include page numbers, date, time, and more. Options consists of one or two characters within single quotes. The options available are:

 

‘’QUOTE: Use two single quotation marks to embed a single quote into the text of the footing.
‘B’BREAK: Inserts the value of a breaking column into the heading. It must be the first of the footing options given (after any text) and it must be used in conjunction with the BREAK-ON 'B' option.

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

‘F’TABLENAME (filename): Inserts the name of the table that is being listed.

‘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.

‘PP’PAGE JUSTIFY: Right justifies the current page number in a column of four spaces.

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

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

‘TD’TIMEDATE: Inserts current time and date (see below for output formatting).
‘{column]'Insert the current value of the column column.

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.

 

lengthAn integer alone is interpreted as an override for the display length of the column on. The justification remains the same as that specified in the dictionary definition for the column.

 

justAlpha characters alone are interpreted as a justification override. The justification specification must be enclosed in quote marks. The display length remains the same as that specified in the dictionary definition for the column. Possible values for justification override are:

 

L        Left justification

R        Right justification

T        Text (paragraph) justification

C        Center justification

JustlenThe combination of text and integer is interpreted as an override for both justification and length. The justification and length specification must be enclosed in quote marks.

 

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

 

comparisonThe comparison operation that is to take place. The same comparison operators (EQ, =, GT, >, etc.) are available as in WITH clauses. The default is EQ. For details on possible values for the compare word, see WITH.

 

valuevalue represents the value against which each value in the multivalued column is to be compared. The value should be enclosed in quotes if it is non-numeric. An example of a value comparison is:

 

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

 

compare        You can also compare column with another column in the table. Each value of the        multivalued column will be compared against the value in the compare column. For        example:

 

LIST CUSTOMERS LIMIT PAID_DATE > DUE DATE

 

RangeThe range specifies a set of acceptable values that can be met for the data displayed. The range takes the form:

 

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"

 

output_formatSpecify a valid output format for output_format. For information about available formats, see OCONV in "R/BASIC Command Reference" in R/BASIC.

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.

 

LOUse the command LO for the fastest listing of keys to the screen. LO takes no sort or selection criteria.

 

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

 

USINGUSING follows the table name in the report command. It is followed by the name of the table whose dictionary is to be substituted.

 

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

 

EVERYUse EVERY when column is multivalued. Only those rows are selected that meet the specified conditions for all values in column.

 

CASESENSUse 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

columnThe column is the name of a column in the table being processed. If column is not found in the dictionary of the table being processed, Advanced Revelation will look in the dictionary of the VOC table.
comparisonUse comparison to define how WITH compares each row. If. comparison is not specified, the default value is "=" (equal). Below is a listing 0œ the comparison operators:

 

=                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."

 

valueValue is the value that will be compared against the data in the column represented by column. The comparison value can be a literal value (text or numeric), or can be the name of another column in the row. If value is a literal text value, it should be enclosed in quotes.

 

MATCHESIf the comparison word MATCHES is used, WITH will not do a direct comparison against the comparison value. Instead, it will determine whether the data in the column represented by column follows the pattern specified in pattern.

 

patternWhen using MATCHES. WITH matches the data in the column against the mask in pattern. The pattern should always be in quotes. If the pattern contains literal data, the literal clam should be in single quotes, and the entire pattern in double quotes.

 

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.

 

rangeUse range to specify that the data in the column represented by column does not have meet a specific value. Instead, the data is to fall within a specified set of values (inclusive).

 

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.

nUse n to specify the number of rows to be selected from table. This number designates a random selection of the first n rows found on table.

 

DICTUse DICT to specify that only the dictionary rows be selected from table.
tableAny attached Advanced Revelation table.

 

USINGA USING clause specifies that the SELECT operation is to use the dictionary of an
alt_tablealternative table, rather than the table dictionary. alt_table is the name of the alternate table (not the dictionary name).

 

SelectionSelection enables you to indicate a subset of rows to process by including filtering specifications

 

SortSort enables you to specify the order by which rows are to be displayed.

 

keywordsThe R/LlST keywords include flow of control, formatting, and output specifications.
optionUse the S option enclosed in parentheses to suppress message output

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.