TCL Reference and Training Manual by qza17959

VIEWS: 14 PAGES: 52

									Terminal Control Language (TCL)

Reference and Training Manual
                                                           Table of Contents

Introduction .................................................................................................................................................................... 4
   Overview .................................................................................................................................................................... 4
     Introduction Overview .......................................................................................................................................... 4
   Document Typographical Conventions...................................................................................................................... 4
     Keyboard Conventions ........................................................................................................................................... 4
     General Conventions .............................................................................................................................................. 5
     Formatting Conventions ......................................................................................................................................... 5
   Overview of Screens .................................................................................................................................................. 5
     Header .................................................................................................................................................................... 5
     Body ....................................................................................................................................................................... 5
     Footer ..................................................................................................................................................................... 5
System Access ................................................................................................................................................................ 7
TCL Basic Operations .................................................................................................................................................... 8
Glossary.......................................................................................................................................................................... 9
   Languages................................................................................................................................................................... 9
   Commands.................................................................................................................................................................. 9
   Data Storage Components ........................................................................................................................................ 10
Common Files .............................................................................................................................................................. 12
   Dictionary ................................................................................................................................................................. 12
TCL Command Descriptions ....................................................................................................................................... 14
   Overview .................................................................................................................................................................. 14
   Command Structure.................................................................................................................................................. 14
   Relational Operators ................................................................................................................................................. 15
   Command ................................................................................................................................................................. 15
     Entry ..................................................................................................................................................................... 15
   Break/ Termination Commands ............................................................................................................................... 15
     <CTRL-X> ........................................................................................................................................................... 15
     OFF....................................................................................................................................................................... 15
     END...................................................................................................................................................................... 16
     N ........................................................................................................................................................................... 16
     < CTRL BREAK >............................................................................................................................................... 16
   List/Sort Commands................................................................................................................................................. 16
     LIST ..................................................................................................................................................................... 17
     SORT -{BY/BY-DSND} ..................................................................................................................................... 18
     BY-EXP ............................................................................................................................................................... 20
     QSELECT ............................................................................................................................................................ 22
     NSELECT ............................................................................................................................................................ 23
     LIST-DIFF ........................................................................................................................................................... 24
     LIST-INTER ........................................................................................................................................................ 24
     LIST-UNION ....................................................................................................................................................... 25
     EDIT-LIST ........................................................................................................................................................... 25
     GET-LIST ............................................................................................................................................................ 26
   IMPORTANT........................................................................................................................................................... 26
   SELECT Commands ................................................................................................................................................ 27
    WITH/IF ............................................................................................................................................................... 27
    Print Limiting ....................................................................................................................................................... 30
    SELECT ............................................................................................................................................................... 31
    LIST.HANDLER ................................................................................................................................................. 31
  SELECT Modifiers .................................................................................................................................................. 34
    HEADING/FOOTING ......................................................................................................................................... 34
    BREAK-ON/GRAND-TOTAL ........................................................................................................................... 35
  Spooling/ Printing Commands ................................................................................................................................. 36
  Keywords and Modifiers .......................................................................................................................................... 36
    ASSOC "association", syn. ASSOCIATION ....................................................................................................... 36
    ASSOC-WITH { field name | temp.name } , syn. ASSOCIATED ..................................................................... 37
    AVERAGE or AVG field.expression [NO-NULLS]........................................................................................... 37
    BREAK-ON/GRAND-TOTAL ........................................................................................................................... 37
    BY-EXP ............................................................................................................................................................... 37
    CONV or CONVERSION "conversion".............................................................................................................. 37
    ENUMERATE or ENUM field.expression [NO.NULLS] .................................................................................. 40
    EVALUATE or EVAL "i.type.expr" [ qualifiers ] .............................................................................................. 40
    FORMAT or FMT "format" ................................................................................................................................. 40
    HEADING/FOOTING ......................................................................................................................................... 40
    INQUIRING ......................................................................................................................................................... 40
    MATCHES or MATCHING or LIKE pattern ..................................................................................................... 40
    MAX field.expression [NO-NULLS] .................................................................................................................. 41
    MIN field.expression [NO-NULLS] .................................................................................................................... 41
    NOT-MATCHING pattern................................................................................................................................... 41
    PERCENT [n] field .............................................................................................................................................. 41
    SAMPLE [n] ........................................................................................................................................................ 41
    SAMPLED [n]...................................................................................................................................................... 41
    SAVING [UNIQUE] field ................................................................................................................................... 41
    TOTAL field ........................................................................................................................................................ 42
    USING [DICT] filename ...................................................................................................................................... 42
    WHEN mvfield operator ...................................................................................................................................... 42
Paragraphs .................................................................................................................................................................... 43
  Overview .................................................................................................................................................................. 43
  Create (Save) a Paragraph in a MD File .................................................................................................................. 43
  Name a Paragraph .................................................................................................................................................... 45
  List a Paragraph Stored in an MD File ..................................................................................................................... 46
  Recall a Paragraph Stored in an MD File to the TCL Stacker ................................................................................. 46
  Delete a Paragraph Stored in a MD File .................................................................................................................. 47
  Advanced Paragraph Topics..................................................................................................................................... 48
    Comments (*) ....................................................................................................................................................... 48
    Prompting (<<...>>) ............................................................................................................................................. 48
    IF - THEN Statement ........................................................................................................................................... 50
    DATA Statement .................................................................................................................................................. 51
                                               Introduction
Overview



Introduction Overview
The Terminal Control Language (TCL) has been developed to serve as the command language for UniVerse
database management system accounts in the Pick operating environment. TCL is a system-level command
language with defined statements (commands) that may be executed individually or sequentially.

TCL has been one of the most widely used and influential command languages, predating SQL (which may have
been modeled after it). TCL is a very powerful language. If you use Pick, you will need to use TCL. It provides a
parameter- (dictionary-) driven query and report language as well as the command line interface to all commands.

This TCL Reference Manual provides a reference source for use in TCL training classes. It is intended for TCL
users with little or no experience in command-line programming, although previous experience with BASIC,
PICK, UniVerse or SQL would be helpful. The document covers

        Logon procedures.
        Terminology.
        Common TCL commands.
        Keywords and modifiers.
        •       Paragraphs.
        For additional information on TCL, consult:
        UniVerse User Reference, VMARK Software, Inc., 1995 (includes chapters on TCL-related commands and
keywords).
        UniVerse Quick Reference Guide, VMARK Software, Inc., 1995 (includes a chapter on TCL).
        TCL’s online HELP command, which may be accessed at a TCL prompt by typing HELP {topic}, then
pressing [Enter] (or simply HELP [Enter]).
        Courses available through Discovery Computer Systems, Inc. (www.discoverycomputersystems.com), which
specializes in training on Multivalue, Pick and Unix systems.
        Courses available through JES, Inc. (www.jes.com), which specializes in operating systems and
multidimensional database training.


Document Typographical Conventions
Typographical conventions is a term that describes how italics, bold, fonts and other such effects are used. This
section provides a listing of typographical conventions used within this manual.

Keyboard Conventions
Keys are frequently used in combinations or sequences as shortcut or function keys. For example, Shift + F3 means
to hold down the Shift key while pressing F3, and Alt, F, A means to press and release each of these keys in order.
A reference to a key is preceded with the word “press,” and is boldfaced and in brackets. For example: press
[Enter] or press [Ctrl +J].
General Conventions
Data a user must enter is capitalized and boldfaced. For example, “Type the Date; then, press [Enter].”
User actions are enumerated. Even if there is only one step, the action is enumerated.
Hyperlink references are generally in quotations and are identical to the screen hyperlink. Both the quotation marks
and the hyperlink reference are boldfaced. For example: ‘Click on the “Logon” hyperlink’ or ‘Click on the “Group
Services” hyperlink.’ If the reference to the hyperlink is not identical to the hyperlink as it appears on the screen, it
will not be in quotation marks. For example, ‘Click on the desired Member Number hyperlink.’ The hyper-link
actually appears on the screen as “1234567,” “2345678,” “3456789,” etc.

Formatting Conventions
                        Italic type References to screen names, user manual titles, screen headings,
                                    screen column headings, dialog boxes, windows and field names

                        Bold type Data a user must enter, screen buttons, keyboard key references and
                                  references to hyperlinks
                         Blue font Web addresses, cross references, user manual section headings and
                                   field names in Field Description section

                   All are built similarly; they generally consist of three sections: a header, body and
Overview           footer. The lines allocated to each screen section vary.
of Screens
                   Header
                   The screen header is the top section of the screen and generally displays the program
                   name, the User ID of who is logged into the system, and the current date.




                   Body
                   The screen body is the section below the screen header. This screen section is used
                   for data entry and display fields. Data entry fields are those in which you may enter
                   or edit data. Display fields are fields that cannot be edited; they display data entered
                   in another field; from system processing and calculations or static data such as
                   screen, section or column headings.



                   Footer
                   The screen footer is the bottom section of the screen and usually consists of the last
                   two or three lines. A list of screen functions and informational and error messages
                   display in this section. Screen functions provide the indicated action when the corre-
                   sponding function character(s) is/are entered. For example, entering “X” exits the
                   screen or entering “P” prints the specific document.
TCL Reference Manual System Access Access Rights
                                           System Access
Access TCL users require access rights on Purveyor which can be granted by those personnel having AUTH
 update permissions. If your company has questions about your access level, contact Racine Enterprises Inc.


    TCL access is limited to those users who have had their security access on the main system updated.
                                    TCL Basic Operations

This section is intended to describe the logon procedure for using TCL, followed by screen features and terms
commonly used when working in TCL.



     Logon Perform the following steps to access the TCL command prompt:
     Procedure
1       From the BAR menu, type R for Report Writer, then B for Build. From the Pull Down menu, pull down the
Utilities Menu, select Report Writer, then B for Build.

                 An example of one screen with an executed TCL command and resulting output is shown in
                 Figure 1 below.
                                         Command Prompt (“@tcl” is symbolic; your command prompt will
                                         display differently, depending on your stacker and/or your custom
                                         configuration)




                                    Figure 1: Screen with a TCL Command
                                                 Glossary
The following terms are commonly used in TCL programming.


Languages
                The following programming languages are used with TCL.

                ACCESS — A dictionary-driven, information management and data retrieval language that allows
                you to query your database without writing complex programs. Sometimes called RetrieVe or
                Info/Access, Access uses TCL commands as verbs and displays the results on terminals or printers.


Note
              In the TCL context, Access is not to be confused with Microsoft (MS) Access, a database
              management system application for use with MS Windows operating systems.

PROC — Procedural job control language.

TCL — Terminal Control Language, the interface between the user and the database system functions.




Commands
The following terms describe TCL commands.

Statement — List of words which comprise the detailed instructions for the system to implement an action; same as

command.


Modifier — Type of command token (word) that changes the output specifications.


Verb — “Actionable” system-level TCL command in a sentence.Sentence — Single line containing a TCL

command.


Paragraph or Stack — List of TCL commands executed in succession (i.e., displayed in


multiple consecutive lines).



Spooler — Commands that allow you to control how data is output to the printer.
Data Storage Components

The following terms describe groupings of data, from lowest level of detail to highest.Subvalue — An individual element of a

value.



Subvalue Mark — Data separator in an item within an attribute, within a value mark,



indicated by a “u” with two dots above it.

Value — An individual element of an attribute.


Value Mark — Data separator in an item within an attribute, indicated by a “y” with two dots above it.

Attribute (Field) — An object or collection of logically related objects (like an address or list of addresses) within an
item. Attributes may contain one, multiple or no values.

Attribute Mark — Attribute separator in an item (line), indicated by a triangle.

Multivalued — Characteristic of an attribute with more than one value or subvalue.

Variable — Symbol into which data can be stored. The contents of a variable may change during program
execution. Variables must begin with an alphabetical character and may include alphabetical and/or punctuation
characters (string variables) and dollar signs.

Item (Record) — Collection of logically related attributes in a file, represented as a string of ASCII characters.

Item ID (Record Key) — Unique item-identifier (also known as a "key") to identify an item or record in a file. An
item-ID may consist of any combination of alphabetic, numeric or (most) punctuation characters.

File — Collection of “like” data (i.e., logically related items); may include a dictionary.

Dictionary — Level of the file system used to contain attribute-defining items for use in ACCESS sentences, and to
define the actual location of the data section for a file via its data definition item. A dictionary may have multiple
data files associated with it.

Editor — Tool that allows users to create, maintain, and/or delete most elements within the database.

MD (VOC) — File that may contain a variety of elements, including file pointers; verbs that allow users to
configure, create and delete files; cataloged programs; special verbs; and keywords that allow users to report on the
data files using dictionaries.

Account — Collection of logically related files.


Note            The hierarchy of data storage components in TCL (in descending order) may be visualized as
follows: ACCOUNT [= MD] (Ex: DS) FILE (Ex: ORDER) RECORD {ID} (Ex: H47601) – the key is unique
ATTRIBUTE ('?') {lines} (Ex: Attr. 20 in ORDER H47601 is the ORDER ship date) VALUE ('y' with 2 dots)
separates data within an attribute SUB VALUE ('u' with 2 dots) separates data within a value
                                                Common Files
The list below shows the most common files used with TCL commands: ORDER, INVOICE, PO, PO-REC, A/R,

A/P, BILLTO, PAYTO, PROD, INV, WEIGHT, APWEIGHT, PINV, WORDER, WOHIST2, WOHIST



Several files are used indirectly (for descriptions): CATNAME, CATNAME2, C-CODES, NC-CODES



Dictionary
Each file has a dictionary associated with it. The dictionary defines the information           within the
                                                                                        Items

file or data it can retrieve from other files. You may use the dictionaries in TCL statements to enable data within

files to be processed.

Dictionary items are used to:

        Display attributes of the file.
        Read attributes (translate code) from other files
        Perform mathematical computations between attributes or attributes and other values Convert data to user
friendly form: -Change dates from numbers to a date format (Conversion 'D]'). -Add decimal points, commas, dollar
signs, etc. (Conversion 'M]'). -Modify attributes to a specified format (for example, G*l - Group extracts the
Subscriber Number from the Member Number).
.
Two types of dictionaries are available: 1) original Pick-style dictionaries, 2) Prime-style dictionaries. A dictionary
consists of the following elements (columns):

Pick-style Dictionaries

1.     "S" or "A"

Note   Use “A” to describe actual data. Use “S” for synonyms (all derivations).




1      Attribute number (within same file)
2      Dictionary item name (Column Header)
3      Report Header
4      Controlling/Dependent codes
5      (Security-reserved)
6      (Security-reserved)
7      Conversion (D = Date; M = Numeric)
8      Correlative process to retrieve data (manipulate)
9       Left or Right justification (indicated by “L” or “R”, respectively)

Note All numbers and date dictionaries should be "R"ight-justified.


10. Column width (this is overridden by the length of the Report Header if that is larger)

OR

Prime-style Dictionaries
1       ‘D’ or 'I' or ‘V’
2       Attribute or Process to retrieve data
3       Conversion (D = DATE; M = Numeric)
4       Dictionary item name (Column Header)
5       Format (Column Width : Justification)
6       "S"ingle or "M”ultivalued
7       Association (if “M”ultivalued)


To list dictionaries for a file, enter the following commands:

@tcl>LD ORDER.S
(for the ORDER file. The .S is necessary to pull from the standard Purveyor Dictionary. Omit the .S only
if your company has created custom dictionaries for itself.)

@tcl>LD ORDERS = "N]"

Lists all the dictionary definitions beginning with the letter "N". (Pick syntax)

@tcl>SEARCH DICT MEMBERS *

       STRING: NAME

       STRING: [Enter]

Lists the dictionaries with the word 'NAME' anywhere in the dictionary. Appending /S to the SEARCH statement

will yield a TCL list.



    @tcl>LDD MEMBERS 2



Lists the dictionaries associated with the second attribute.
                                   TCL Command Descriptions
Overview
A variety of TCL commands is available to perform common database management-functions. You
may use TCL commands to list a specified subset of records in the database, sort data in a list, retrieve
a file, store and retrieve a list of record IDs, create labels based on user-defined dictionaries, change the
size of a field, add header or footer information, and set printing/spooling options, among other
functions.


Note
Only the most common or useful TCL commands are described in this

reference manual. A comprehensive list and description of TCL commands is beyond the scope of this document.




Command Structure
In general, the structure of a TCL statement (command) may consist of the following elements:

Verb File {Criteria - BY/BY-DSND Dict.items, WITH Dict.items Operator "Value", BREAK-ON Dict.items,
TOTAL Dict-items} {Return Data -Dict.items} {Operator “Values”} {Output Specifications- HEADING, display
format}

Where Verb is the “actionable” element within the command. File is the file on which the command will be

executed. Dict refers to a dictionary (see Dictionary Items on page 9).

{...} contains a variety of optional information (criteria by which records are selected,


relational operators, dictionary items, display format, etc.) which sets limits on the scope of the command’s
execution within a file.


Note
• You may specify commands, keywords and modifiers with a dash

(-) or dot (.); both are used in the TCL statement structure shown above. Although dots are used only on some Pick systems,
dashes are used in all systems, as well as in the descriptions of commands, keywords and modifiers included in this
document.

Because the structure of a specific TCL statement may vary according to the action you desire, not all of these
elements may be included in the statement.
Example verbs: SELECT, SSELECT, LIST, SORT, LIST-LABEL ...

Example dictionary items: DESCR, PACK, NAME, CITY, STATE, ZIP



Relational Operators
                   Relational operators are six arithmetic symbols used in a command line to compare

                   values of multiple variables or of a variable and a constant.The following list displays the six
                   relational operator symbols with their abbreviations and descriptions:

                   = EQ equal# NE not equal> GT greater than< LT less than<= (or =<) LE less than or equal to>=

                   (or =>) GE greater than or equal to




Command
To enter a TCL command:


Entry
1       At the @TCL> prompt, type the desired Command.
2       Press the [Enter] key. The system processes your command.

Note
If you need to use stack commands, use the STACKER utility (refer to

the STACKER Utility Quick Reference Guide, available on http://www.purveyorsoftware.com).




Break/ Termination Commands
You may enter the following commands at any time to break or terminate an operation, execution or your session.

<CTRL-X>
Stops execution of a listing, then returns user to the TCL prompt.

OFF
Terminates session and logs off terminal.
END
Returns user to Main Menu processor.

N
Stops paging and continually scrolls the data to the screen (after you press any key to continue).

< CTRL BREAK >
Breaks execution. After you enter this command, you may enter one of the following Break Options:

    END — Aborts the current process (usually recommended).
    G — For “GO”, continues the current process (usually recommended after the program debugger prompt ::
    displays).

    OFF — Logs off




List/Sort Commands
You may use a variety of TCL commands to list or sort desired attributes, record IDs and/or files:

LIST-ITEM — Displays attributes of a specified file (see LIST-ITEM on page 16).

LIST — Displays/lists unsorted data on the screen or in a report (see LIST on page 17).

SORT — Displays/lists data on the screen or in a report in a sorted order (see SORT - {BY/BY-DSND} on page
18).

LIST-LABEL or SORT-LABEL — Creates labels based on user-defined dictionaries (see LIST-LABEL or SORT-
LABEL (BY/BY-DSND ZIP code) on page 21).

QSELECT — Creates a list from one or all attributes of a file, converting a list of IDs from one file to another (see
QSELECT on page 23).

NSELECT — Extracts all records that are not on file from a list (see NSELECT on page 26).

LIST-DIFF — Subtracts one list from another list (see LIST-DIFF on page 27).

LIST-INTER — Displays all IDs that may be found in more than one list (see LISTINTER on page 27).

LIST-UNION — Combines two lists, omitting duplicate record IDs (see LISTUNION on page 27).

EDIT-LIST — Allows you to enters a list of record IDs or to modify an existing list (see EDIT-LIST on page 28).

GET-LIST — Retrieves a list of record IDs stored under a listname into active memory (see GET-LIST on page 29).
The SAVE-LIST (see SAVE-LIST on page 44) and SELECT (see SELECT on page 36) commands are closely
related topics.
LIST-ITEM
The LIST-ITEM command displays attributes (with data) of a specified file, using the following format:

LIST-ITEM filename {"ID"}


Note

               •   The ID is optional. If used, it requires quotes.

               •   If an attribute is blank, the line is NOT displayed.

For example:

1      At the TCL prompt, enter the command @tcl>LIST-ITEM ORDER “H47601”
2      Press the [Enter] key. The following information displays: LIST-ITEM ORDER “H47601” 08:33:34pm 07
Apr l997 PAGE 1 ORDER “H47601”
3              KEY to the FILE

Note


Attribute numbers do not correspond to the numbers on the screen!!!!

LIST
The LIST statement is used to display/list unsorted data on-screen or in a printout (unsorted report). The LIST
verb uses the following format:

LIST Filename {Selection Criteria} {Return Data} {Output Specifications}

where

Selection Criteria specifies conditions to be met before the command is executed (seeWITH/IF on page 32).

Return Data is the "dictionaries" to be viewed on-screen or in a printout.

Output Specifications consist of "special" features to display the data (e.g.,

HEADING, FOOTING, Printer Option.)




Examples of the LIST verb:


@tcl>LIST ORDER SHIP.DT NAME USING DICT ORDER.S
Lists the member number (ID of file), name, age and group number for each member


in the list or file.




@tcl>LIST ORDER IF SHIP.DT = “04/01/08” NAME USING DICT ORDER.S

Lists the member number (ID of file), name, age and group number for each member who is more than 65 years of
age.



@tcl>LIST ORDER IF SHIP.DT = “04/01/08” NAME HEADING " Orders for Ship Date 04/01/08” LPTR

Creates a spool entry number to be sent to a printer with a heading of " Orders for Ship Date 04/01/08" on each
page.


SORT -{BY/BY-DSND}
The SORT statement is used to display/list information data to the screen or printout in a sorted order. By default,
data is sorted by ID if a sort order is not specified by the user.

The SORT verb format is very similar to the LIST verb format, but the user is allowed to choose the display format:

SORT Filename {BY/BY-DSND dictionary} {Selection Criteria} {BREAK-ON dictionary} {Return Data}
{TOTAL dictionary} {Output Specifications}

where

BY/BY-DSND defines the order in which to display the data. There can be multiple sorts — primary, secondary,
etc. The data will be sorted in ascending order (A before B, 1 before 2, etc.) unless you use the BY-DSND command
to sort by descending order. If numeric/date data is sorted, the dictionary must be right-justified to be used
correctly.

Selection Criteria specifies conditions to be met before the command is executed (see WITH/IF on page 32).

BREAK-ON allows the report to make a noticeable stop between different data. The stop could be user text or a
subtotal break point. The default text string is "***" which usually is displayed.


Note
                       For each BREAK-ON command, there should be a BY command.
Return Data is the "dictionaries" to be viewed on-screen or in a printout.

TOTAL (or GRAND-TOTAL) allows numeric columns to be summed. The resulting total usually is denoted in the
report by "***".

Output Specifications consist of "special" features to display the data (e.g., HEADING, FOOTING,
Printer Option.)



Examples of the SORT verb:

@tcl>SORT ORDER BY SHIP.DT NAME USING DICT ORDER.S

Sorts the member in ascending order of SHIP.DT and display number (ID of file), name for each member in the list
or file.
@tcl>SORT ORDER IF SHIP.DT = “04/01/08” BY NAME NAME USING DICT ORDER.S (I

Sorts ORDERs if the ORDER record has a SHIP.DT = “04/01/08” by NAME and displays the NAME only.


Note
(I denotes ID Suppress (ID-SUPP).




@tcl>SORT ORDER BY SHIP.DT BY NAME IF SHIP.DT >= “01/01/08” BREAK-ON SHIP.DT BREAK-
ON NAME TOTAL AMT (DI

Sums the order dollars since the beginning of 2008.


Note
(DI denotes Detail Suppress (DET-SUPP) and ID-SUPP).




You also may use a SORT (or LIST) verb to locate a file:

1      At the command prompt, enter SORT MD = “[part of the filename]” IF *A1 = “Q]”“F]”
2      Press the [Enter] key to display a list of files that match the criteria specified in the SORT statement.


Other examples of file retrieval (using LIST):

@tcl>LIST MD = “PPO]” (Pick syntax)
@tcl>LIST MD = “[PPO]” (Pick syntax)

@tcl>LIST ORDER = “M]” (Pick syntax)

(this last one yields a list of “MEMO” orders.”




BY-EXP
Allows you to “explode” and sort data in multivalued fields in a sort expression. The BY-EXP keyword (also
known as “by exploding” or an “exploding sort”) treats related, "parallel" multivalued sets of values within a record
as individual records. For example, it may retrieve Value 1 in Attribute 5 with Value 1 in Attribute 7, or Value 3
with Value 3, etc. All of the single-valued data (including the ID) in each "pseudo-record" then may be replicated,
and each psuedo-record may be sorted independently of its mother record.

In addition to performing an exploding sort, you may select only those "pseudo-records" that pass the criteria
specified for inclusion in a report or select-list.

Special select-lists are generated when you enter the SSELECT FILE BY-EXP Dict-Name sentence. These lists
contain the ID and specify the value# contained within the real record of the selected "pseudo-record."


Note
FOR PROGRAMMERS — The lists can be processed by BASIC, eliminating much coding and ensuring the correct value is
pulled when several values in an attribute have the same contents.

                  Rather than entering the sentence
                  SELECT FILE WITH DATE >= "01-01-01" AND <= "01-01-03"
                  (where DATE is multivalued), BY-EXP must be used to obtain accurate results when using a date field as a
                  criterion. (Without BY-EXP, SELECT statements do not handle multivalued date fields as well as other
                  types of fields.) Use the following format with multivalued date fields:




                  SSELECT FILE BY *A0 BY-EXP DATE >= "01-01-01" AND <= "01-01-03"
IMPORTANT


                  The above case is also a useful example of general syntax for using BY-EXP on a single criterion. See
                  also the SAVING [UNIQUE] field (Prime syntax) on page 62.




LIST-LABEL or SORT-LABEL (BY/BY-DSND ZIP code)
The LIST-LABEL and SORT-LABEL statements are essentially equivalent commands used to create labels based
on user-defined dictionaries. Use LIST-LABEL to create a list of unsorted labels; use SORT-LABEL to create a list
of sorted labels.


Note
Mailrooms generally prefer labels to be in ZIP code order.

The LABEL verb uses the following format:

LIST-LABEL filename dictionaryl {dictionary2} {dictionary3} {dictionary4} {dictionary5} (CIP @
c,r,sk,i.w,sp{,C} ?

SORT-LABEL filename {BY zipcode.dictionary} dictionaryl {dictionary2} {dictionary3} {dictionary4}
(CIP @ c,r,sk,i,w,sp{,C} ?

Where @ sign signifies a prompt from the system for more information. Numbers to the right signify examples of inputs.CIP

represents the column header Supp. and ID Supp., with P directing output to the



printer.c is the count (number) of labels across the page.r is the number of rows (lines or fields) displayed or

printed per label.sk is the number of blank rows (in which text is skipped) between labels.i is number of indented

spaces from the left margin to the first column of labels.w is the maximum number of characters (width) in a

display field.sp is the number of blank spaces horizontally between labels.C specifies that the labels are

compressed (i.e., empty fields are not printed). If C is
not specified, empty fields are printed as a series of blanks. The LABEL verb requires further input from the user
concerning the type of labels to be used. For example, if you append 3,5,1,0,38,2,C to either of the commands
described above, you would specify 3 labels across, 5 lines on a label (with a maximum of 5 dictionaries used), 1
line between labels, 0 spaces indented, 38 character-width of label, 2 spaces between labels, and compressed labels
(i.e., null dictionaries are not printed).

The following example sentences show the use of the LABEL verb: @tcl>LIST-LABEL ORDER NAME

ADDR1 ADDR2 CITY STATE ZIP USING DICT ORDER.S (CIP



Mailing to Customer Address


@tcl>SORT-LABEL ORDER BY NAME NAME ADDR1 ADDR2 CITY STATE ZIP USING DICT ORDER.S
(CIP
Mailing to Customer Address, printed in sorted order


QSELECT
The QSELECT statement allows you to create a list from one or all attributes of a file (i.e., it converts a list of IDs
from one file to another.) This verb uses the following format:

SELECT filenamel {Selection Criteria}



QSELECT filenamel SAVING { UNIQUE } attribute (Prime syntax)



QSELECT filename (attribute# (Pick syntax)



SELECT filename2


Note
The data stored on the attribute must be the ID for items in the second

file.



OR

A regular SELECT may be used as follows:

SELECT filenamel {Selection Criteria} {dictionary.for.attribute} (Pick syntax)

SELECT filenamel {Selection Criteria} SAVING {dictionary.for.attribute} {UNIQUE} (Prime

syntax)

SELECT filename2

where

Selection Criteria specifies conditions to be met before the command is executed (see WITH/IF on page 32).


Note
               • The dictionary.for.attribute returns the ID (from foreign keys) for the items (primary keys) in the second file.
               •   Some files have the same IDs and do not require any conversions (for example, ORDER=INVOICE,
                   WORDER=WOHIST2).

(If a conversion from one file to another file is to be executed, duplicate IDs could be included. To remove

duplicates, add the 'UNIQUE' clause to the statement.)


For example:


@tcl>QSELECT INV * (16


OR


@tcl>SELECT INV PO USING DICT INV.S


Returns a list of incoming purchases (and production).


NSELECT
The NSELECT statement allows you to extract all of the record IDs that would be marked <NOT ON FILE> from
a list. This verb uses the following format:

SELECT filename 1 {Selection Criteria}



NSELECT filename2


Note
The IDs returned are in filename1 and not in filename2.



where



Selection Criteria specifies conditions to be met before the command is executed (seeWITH/IF on page 32).For

example:
@tcl>SELECT PROD



@tcl>NSELECT OUTGOING



Returns a list of products who do not have inventory updates pending.




LIST-DIFF (Prime syntax)

The LIST-DIFF command is used to manipulate lists by subtracting one list from another list. This verb uses the

following format (ending with a carriage return):



LIST-DIFF listnamel <cr> (listnamel = big list)



WITH: listname2 <cr> (listname2 = small list)



TO: listname3 <cr> (listname3 = new list)



LIST-INTER (Prime syntax)
The LIST.INTER command is used to manipulate lists by finding all record IDs present in both lists (i.e., an
Intersect function). This verb uses the following format (ending with a carriage return):

LIST-INTER listnamel <cr> (listnamel = list1)



WITH: listname2 <cr> (listname2 = list2)



TO: listname3 <cr> (listname3 = list3)
LIST-UNION (Prime syntax)

The LIST-UNION command is used to combine two lists, omitting duplicate IDs. This verb uses the following

format (ending with a carriage return):

LIST-UNION listnamel <cr> (listnamel = big list)

WITH: listname2 <cr> (listname2 = small list)

TO: listname3 <cr> (listname3 = new list)

COMPARELIST (BB4GL syntax) – see BB4GL Main manual

EDIT-LIST
The EDIT-LIST statement allows you to enter a list of IDs or to modify an existing list. This statement is useful if
the IDs are known by the user and are limited. This verb uses the following format:

EDIT-LIST listname

where

listname is user-defined.

After you enter the EDIT-LIST command (to invoke the editor program), the prompt (normally @tcl>) will display
as a dot (.) until you enter either the 'FI’ (file) or 'EX' (exit) command (which returns you to TCL).


Note

•         You may enter B at the ---: prompt to move the cursor to the bottom

of the list.

•         You may enter I at the ---: prompt to change the prompt to 000# (where # is the line number).



At the prompt:

1. To input IDs into the list:
          a.      Type I, then press the [Enter] key.
          b.      Enter the first ID, then press the [Enter] key.
          c.      Repeat Step b for as many IDs as you want to input.
          d.      After you have entered the last ID, press the [Enter] key to return to the (.) prompt.

    OR

1         To delete the line above the cursor, enter a DE command. OR
2     To return the cursor to the top of the list, enter a T command. OR
To page through the list, enter a P command.

This command may be used rather than entering a SELECT command for a list of known IDs. For example,
rather than entering the command

SELECT ORDER "H12345" "H23456" "H34567" "H12345" "H15926"

you could enter these IDs into a list.

GET-LIST
The GET-LIST command is used to retrieve a list of record IDs stored under a listname into active memory. This
verb uses the following format:

GET-LIST listname

where listname is user-defined.




          Lists are purged routinely. If you need a list to be saved for the following week, ask your manager to ensure that the
list is saved.
You must edit a list or save a list (using the SAVE-LIST command, described under Storage Commands on page 44) before
you may retrieve it


IMPORTANT

using the GET-LIST command.

For example:@tcl>SELECT ORDER IF SHIP.DT >= "01/01/08" USING DICT ORDER.S

@tcl>SAVE-LIST THIS.YEARS.ORDERS

Saves a list of ORDERs open since the beginning of the year

OR

@tcl>GET-LIST THIS.YEARS.ORDERS

@tcl>SSELECT ORDER BY NAME

@tcl>SAVE-LIST THIS.YEARS.ORDERS

Saves a list of ORDERs sorted by Name so that future processing does not have to sort it.
Note
SORT and SSELECT statements take longer to run than LIST and SELECT statements. You should enter a SSELECT
command and save the IDs in a sorted list (SAVE-LIST) before working on the report format (using LIST verb). This method is
faster than saving the IDs in an unsorted order (i.e., SAVE-LIST after a SELECT statement) and making several attempts to
create the exact format of the report including sorted order (using SORT verb).




To find a list created on the system, enter the following sentence:

@tcl>SORT ONLY POINTER-FILE

(Lists are stored in a file named POINTER-FILE with IDS = listname.)



SELECT Commands


You may use a variety of TCL commands to select specified record IDs or files for display in a list, or to index a
set of record IDs:

WITH/IF — Specifies record ID selection criteria with the LIST, SORT, SELECT or SSELECT verbs before
processing data in a report (see WITH/IF on page 32).

SELECT — Creates a list of unsorted record IDs that meet specified conditions, or converts record IDs of one file
to those of another file (see SELECT on page 36).

LIST.HANDLER — Facilitates queries of Purveyor when using the WITH/IF statements with a long list of
criteria contents, or when criteria contents can be selected from another file into a list (see LIST.HANDLER on
page 38).

SSELECT (SORT-SELECT) — Creates a sorted list of record IDs that meet specified conditions (see SSELECT -
{BY/BY-DSND} on page 40).


WITH/IF
The WITH/IF command is used to specify selection criteria with the following verbs: LIST, SORT, SELECT and
SSELECT. You may use this command to choose what conditions must be met before processing data in a report.

This statement uses the format:

WITH or IF { NO or NOT } {EVERY } dictionary {relational-operator } {valuelist}


Note
EVERY indicates that all instances of a multivalue attribute must have
those contents in order to be selected.

NO or NOT means that only those items having one or more values in the attribute are selected if they do not meet the criteria.

You should use EVERY and NO or NOT together when you want to select those items that have a value of an attribute
having no occurrences of the specified contents.




                       Multiple selection criteria may be connected by AND or OR, but use parentheses with OR to ensure that
selection criteria are specified in the way in which you intend.


IMPORTANT




The usual selection option is IF.

Relational-operator symbols are:

    = EQ equal

    # NE not equal

    > GT greater than

    < LT less than

    <= LE less than or equal to

    >= GE greater than or equal to


Note
If no operator symbol is used, an equal sign is assumed.




The value can be a number or a string of characters. Values must be enclosed in double quotes (“ ”).

Within the value, wildcard characters may be used: [ any number of characters to the left ] any number of characters to the

right ^ single character unknown ... any number of characters before, after or within a sentence (see also MATCHES or

MATCHING topic)

Example (single equation)
@tcl>LIST ORDERS IF DEPT = "S" AND IF SHIP.DT = “04/01/08” USING DICT

ORDER.S



Lists ORDERs which are restricted to seafood and will be shipped on the first of April

2008.

Example (single equation using the NO feature)


@tcl>LIST ORDER WITH NO DEPT USING DICT ORDER.S

Lists all ORDERs having no DEPT code (they all should have one, though).

OR



@tcl>LIST MEMBERS IF DEPT = “”



(same result as above)



Example (single equation with value-list)
@tcl>LIST PO IF VEND = "100" "130" NAME USING DICT PO.S

Lists all open purchase orders for vendors 100 and 130.

@tcl>LIST PROD IF CCODE > = "70" AND <= "90" USING DICT PROD.S




Lists all products having category codes from 70 through 90.




@tcl>LIST PO IF VEND # “130” USING DICT PO.S


Note
# denotes “not equal to.”
Example (multiple selection criteria)
@tcl>LIST ORDER IF SHIP.DT = “04/01/08” AND IF CUST = "2265"

Lists ORDERs having a ship date of April 1st 2008 and customer # 2265.




@tcl>LIST ORDER IF DEPT =”S” OR WITH SHIP.DT = “04/01/08” AND IF CUST = "2265"

Lists all ORDERs having a department code of S or having both a ship date of 04/01/08 and a customer number of
2265.




                    This sentence could be confusing to some users because ORs and ANDs are mixed in the same
expression without parentheses. The OR and AND operators have the same logical and arithmetical hierarchical precedence,
so they should resolved with parentheses. Without using parentheses, the ANDs resolve first, then the ORs.




Print Limiting
Print limiting (see example in Figure 3 below) is a technique used to limit the output of multivalued columns on
reports to only those containing specified strings. It may seem similar to using a WITH and/or IF statement to limit
the items selected or listed to those meeting specified criteria, and it even looks similar to those statements in
syntax; however, print limiting serves a very different purpose, as shown in the example below. (Print limiting may
be used with WITH/IF statements in the same statement.)



Print Limiting Example


@tcl>LIST ORDER CODE# = “004001” CASES BOXES USING DICT ORDER.S (Pick syntax)

@tcl>LIST ORDER WHEN CODE# = “004001” CASES BOXES USING DICT ORDER.S (Prime syntax)

The example statemen above shows print limiting … CODE# = "004001" … This statement limits the listing to
display only those multivalues sets (of CODE# CASES BOXES) having a product CODE# = 004001, thus
shortening the listing and enabling more manageable querying and reporting functions. Only those product line
items having a product code of 004001 will appear.
The following example statement show approaches without using print limiting.

LIST ORDER IF CODE# = “004001" CASES BOXES USING DICT ORDER.S



(this will show all products on all orders which have any product code # = 004001 and will show all product line

items)


SELECT
The SELECT statement is used to

         Find record IDs that meet specified conditions.
         Convert record IDs of one file to record IDs of another file.

This verb uses the following format:

SELECT filename {Selection Criteria} {Dictionary}


Note
After entering a SELECT verb, the STACKER utility causes the prompt

to display as “S”, reminding the user that a selected list is active.




where



Selection Criteria specifies conditions to be met before the command is executed (see WITH/IF on page 32).

The list of IDs (returned from the SELECT statement) is stored in memory for execution of the next statement
which uses the list. You may eliminate the list by entering @tcl>WHO




LIST.HANDLER (BB4GL syntax and Pick syntax)
The LIST.HANDLER is a TCL facility that can sometimes facilitate queries of the Purveyor database. It provides a
mechanism for using the TCL WITH/IF statements where the list of criteria contents is long and/or can be selected
from another file into a list. The list then can be referenced by its list name rather than having to type a very large
number of contents for which you want to search.
For example, type the following command:

SELECT FILE WITH LIST.HANDLER = "dict.name""save.list.name" AND WITH DICT2 = "ABC"

where

dict.name is a valid dictionary name in the DICT FILE or in the Master Dictionary (MD). save.list.name is a
valid list in the local POINTER-FILE file (SAVE-LIST


save.list.name). The dictionary cannot contain an F or A correlative, nor can it be an I-descriptor (i.e.,

Line 1 of the dictionary you are using cannot begin with an I, nor can Line 8 begin with an F or an A —

otherwise, an error message will display). Some other examples: >COPY HCON UAD1.VISIT1.CODES


TO: (&SAVEDLISTS&

   1 UAD1.VISIT1.CODES copied to UAD1.VISIT1.CODES in file “POINTER-FILE”.

1 record copied>SELECT ORDER WITH LIST.HANDLER = “CODE#” “UAD1.VISIT1.CODES” USING DICT
ORDER.S

14008 record(s) selected to SELECT list #0.>>SAVE-LIST JPR.FOUND14008 record(s) SAVED to SELECT list

“JPR.FOUND”.>



At any time, you may type WITH DICT.NAME = "A""B""C"".... (where the list is extremely long or can be
obtained from or inserted into a saved list), you can then type WITH LIST.NAME =
"DICT.NAME""SAVE.LIST.NAME" where SAVELIST SAVE.LIST.NAME includes A, B, C, ...


Note
Only one LIST.HANDLER clause may be used within any given TCL

sentence.

To resolve more than one LIST.HANDLER clause:
1       Put them in separate statements.
2       Save the resultant lists.
3       Use the LIST-UNION (for an OR relationship) or LIST-INTER (for an AND relationship) command.

              One also can use LIST FILE WITHOUT LIST.HANDLER = “DICT.NAME” “SAVE.LIST.NAME”.
This facility can be found in most major files already — MEMBERS, ENC.HIST, MASTER.CLAIM, PHYSICIAN,
PHYS.ADD and AUTHORIZATION. It can easily be added to any file by copying DICT ENC.HIST
LIST.HANDLER to DICT other.file. No modification per file is required.


Note
Because the system will not process an I-descriptor in the

Master Dictionary (MD), this dictionary item must be copied into each file on UniVerse systems.



SSELECT - {BY/BY-DSND}
Unlike the SELECT statement, the SSELECT statement (also known as SORTSELECT) creates a list of sorted
record IDs.

This verb uses the following format:

SSELECT filename {BY/BY-DSND dictionary} {Selection Criteria} {Dictionary (ID of another file) }


Note
After entering a SELECT verb, the prompt changes (depending on your

TCL stacker), reminding the user that a selected-list is active.




where



Selection Criteria specifies conditions to be met before the command is executed (seeWITH/IF on page 32).The list
of IDs (returned from the SSELECT statement) is stored in memory only until

the execution of the next statement. For example, if you type an SSELECT statement and the next statement is
‘WHO’, the list is lost. For example, to find records meeting specified conditions, using the SSELECT verb:

@tcl>SSELECT ORDER IF SHIP.DT = “04/01/08” USING DICT ORDER.S



@tcl>SSELECT ORDER IF DEPT = “S” BY NAME USING DICT ORDER.S

Returns a list of sales ORDERs sorted by customer name for those ORDERs having a ship date of April 1st, 2008
and a DEPT code of S.


Note
•       If you know the ID of the file, select the file “ABC” by entering the

command SELECT FILE “ABC”; the ID is “hashed” and the record is found immediately.

•   If you do not know the ID of the file and must use a wildcard character to search for it, enter the command SELECT FILE
    WITH @ID = “ABC]”; the entire file is searched for the desired record.




SELECT Modifiers
A modifier is a type of command that changes the output specifications. Three of the most commonly used
modifiers are described in this section:

HEADING/FOOTING — Inserts header/footer text on the pages of a report (see HEADING/FOOTING on page
50).

BREAK-ON/GRAND-TOTAL — Inserts a break in the display of data in a report (see BREAK-ON/GRAND-
TOTAL on page 52).

A more complete list of modifiers is included in the Keywords and Modifiers section (beginning on page 57).


HEADING/FOOTING
The HEADING/FOOTING modifier allows you to include a user-defined text within the report at the top (heading)
or bottom (footer) of the page.

Enclose the text in double quotes in the TCL statement as shown in the examples below:

HEADING "text 'modifier options' text" FOOTING "text 'modifier options' text"

Where text is the header (footer) you want to include at the top (bottom) of the page.modifier options must be within

single quotes and include:

    C    Center text (located to the left of the 'C')

    T    Time Stamp (includes Date Stamp)

    D    Date Stamp

    L    Line Feed (used in creating multiple line headings)

    P    Page Number (e.g., if you want the word 'PAGE' to display, you must
         include it in text')

For example:

@tcl>LIST ORDER NAME ADDR1 HEADING " Page 'P' ‘TL’ List of Orders, Name, and Street Address 'CLL' "
USING DICT ORDER.S

              will print as shown below: Page l 15:42:27 22 OCT1996 List of

              Orders, Name, and Street Address



ORDER... NAME........................................ ADDR1……………………………………………
Other display formats are shown in the following table:

Long Version                  Short Version         Description
ID-SUPP                              I              ID Suppress (does not display ID)
DET-SUPP                             D              Detail Suppress (summary version
                                                    displays only subtotal lines of a report)
LPTR                                 P              Printer Option (creates spool entry #)
HDR-SUPP                             H              Suppress the Heading of the report
                                                    (not necessary to use in UV download)
NOPAGE                               N              No Automatic Paging (use in
                                                    download)
COL-HDR-SUPP                         C              Suppress the Column Heading &
                                                    Header printing (use in download)


@tcl>LIST ORDER NAME ADDR USING DICT ORDER.S (CIP

OR @tcl>LIST ORDER NAME ADDR1 COL-HDR-SUPP ID-SUPP USING DICT ORDER.S LPTR

Prints ORDER information (Name, Street Address) but does not print ORDER# or Column Heading.


Note
Use the ‘(CN’ display options (shown in the last two rows in the “Short

Version” column in the table above) for download captures.


BREAK-ON/GRAND-TOTAL
The BREAK-ON/GRAND-TOTAL modifier allows you to include a user-defined stop in the report by entering
user-defined text/totaling points (subtotals).

Enclose the text in double quotes in the TCL statement following the modifier, as shown in the examples below:

BREAK-ON dictionary " text 'modifier options' text" GRAND-TOTAL " text 'modifier options' text"

where text is the user-defined text that indicates where to break (stop) the report.modifier options must be within

single quotes and include:
  U      Underlines the total fields

  L      Suppresses line

  P      Ejects page upon break point

  N      Resets page counter (use with 'P' option)

  V      Includes Break Value in description

Note
Break Value refers to the contents of the attribute or derived expression

that pertain to all data in the section of the report up to the breakpoint, where another section starts. It is the value that defines
the breaks in a report.




Spooling/ Printing Commands
Spooling is the process of loading print jobs into a holding area which contains files storing output to be printed; the
printer then retrieves them for printing at its own rate. Because the documents are in a spooler where they can be
accessed by the printer, you can perform other operations on the computer while the printing takes place in the
background. Spooling also lets you place a number of print jobs on a queue instead of waiting for each one to finish
before specifying the next one.

Use the Spooler menus provided in the Utilities menu in the Pull Down menus and at the S option on the Bar
menus.




Keywords and Modifiers
                         TCL sentences may include keywords and modifiers. A keyword is an element in a
                         UniVerse/TCL sentence that allows you to modify the action of the command. A modifier
                         is a type of command that changes the output specifications.

                         The following keywords and modifiers are commonly used in TCL :

ASSOC "association", syn. ASSOCIATION (Prime syntax)
                         Associates a field expression with an existing association of multivalued fields, temporarily
                         overriding the file dictionary for the duration of the query. Association is the record ID of the
                      entry in the file dictionary that defines the association. This keyword is often useful with BY-
                      EXP modifiers.



ASSOC-WITH { field name | temp.name } , syn. ASSOCIATED (Prime syntax)
                      Associates a field expression with another multivalued field expression, temporarily
                      overriding the file dictionary for the duration of the query. Field name is the name of a field
                      defined in the file dictionary. Temp.name is the name of a field expression specified earlier in
                      the same RetrieVe (i.e., Access) query by an AS clause. This keyword is often useful with
                      BY-EXP modifiers and with print limiting.



AVERAGE or AVG field.expression [NO-NULLS] (Prime syntax)
                      Calculates the average for a numeric field. Field.expression specifies a field name or an
                      EVAL expression for which you want to calculate the average value. RetrieVe (i.e., Access)
                      treats non-numerics as zero values. NO-NULLS tells RetrieVe to ignore empty string values.



BREAK-ON/GRAND-TOTAL
                      Allows users to include a user-defined stop in the report by entering user-defined text/
                      totaling points (locations). You may enclose the text in double quotes in the TCL statement
                      following the modifier (for example, BREAK-ON dictionary " text 'modifier options' text").
                      For more information, refer to BREAK-ON/GRAND-TOTAL on page 52.



BY-EXP
                      Allows you to “explode” and sort data in multivalued fields in a sort expression. The BY-
                      EXP keyword (also known as “by exploding” or an “exploding sort”) treats related, "parallel"
                      multivalued sets of values within a record as individual records. For example, it may retrieve
                      value 1 in attribute 5 with value 1 in attribute 7, or value 3 with value 3, etc., and replicate all
                      of the single-valued data (including the ID) in each "pseudo-record". Each psuedo-record can
                      then be sorted independently of its mother record. For more information, refer to BY-EXP on
                      page 20.

In addition to performing an exploding sort, you may select only those "pseudo-records" that pass criteria
specified for inclusion in a report or select-list.



CONV or CONVERSION "conversion" (Prime syntax)

   Defines a conversion for a field name or EVAL expression, temporarily

   overriding the file dictionary for the duration of the query. For example:
  D4- Date with a 4-digit year, separated by –




  D0/ Date with no year, separated by /




  D2- Date with a 2-digit year, separated by –




  D Date in English




  DM Numeric month




  D4Y 4-digit numeric year.



   Gn*m Group extraction that retrieves one or more groups contained in specified fields, where:

      n is the number of fields to skip before the first desired field.

      * is the symbol indicating a separator between fields.
                                                             th
      m is the number of fields to retrieve following the n field.

   This keyword is very useful with multi-part keys and fields. For example, you may apply G0*1 or G*1 in the
   CONV keyword to Auth id AOXX1234567*P to return AOXX1234567, or you may apply G1*1 to return P.



   MCA Extracts all alphabetic characters from a field.MC/A Extracts all non-alphabetic characters from a

   field.MCN Extracts all numeric characters from a field.MC/N Extracts all non-numeric characters from a

   field.MCDX Converts decimal to hexadecimal equivalents.MCXD Converts hexadecimal to decimal

  equivalents.
MCL Converts field to lowercase.

MCU    Converts field to uppercase.
MCP        Converts unprintable chars to periods (.).

MCT        Converts the first letter of every word to uppercase.


MR3 3-decimal place numeric MR2,$ 2-decimal place numeric with commas and dollar signs.MR23 Rounds a 3-

decimal-place figure to 2 places.



MT[H][S][c] Converts times to internal format or back to external format, where: H outputs time in 12-hour format

   with suffixes AM or PM. S outputs seconds; default value omits seconds. c separates hours, minutes and

   seconds; default value is a colon (:).


                                               th
Tn,m Extracts m characters starting at the n character.



T[DICT] filename;c[vloc];[iloc];[oloc]        Known as a "file translation," this keyword converts values from a file to
the listing by translating through another file, where:

DICT specifies the dictionary portion of the file.filename is the name of the lookup file.c is the translate subcode

option:

X (the most common option) returns the empty string if the conversion

fails. V indicates if a conversion item must exist on the file, and the specifiedfield must have a value (otherwise it
returns an error message).

C returns the original value-to-be-translated, if the conversion fails.
            N returns the original value-to-be-translated if null (i.e., the empty string) is found).

             I verifies input.

             O verifies output.

                       C, N and X are the only translate subcode options normally used.

Note

          Vloc is the number of the value to return from a multivalued field — it is usually blank, sometimes 1 or
          occasionally another number.
       Iloc is the decimal field number for input conversion.

       Oloc is the decimal field number for output conversion.



ENUMERATE or ENUM field.expression [NO.NULLS] (Prime syntax)
Counts the number of values that occur in a specified field in a set of records. Field.expression is the name of the
field or an EVAL expression for which you want to calculate the count. Non-numerics are treated as zero values.
NO.NULLS causes RetrieVe/Access to ignore empty string values.



EVALUATE or EVAL "i.type.expr" [ qualifiers ] (Prime syntax)
Introduces an in-line I-descriptor expression, which defines a new virtual field that exists only for the
duration of the current query. I.type.expr is any expression that can be used in an I-descriptor, enclosed in
single or double quotation marks. Qualifiers can be one or more field qualifier clauses beginning with one
of the following keywords: AS, ASSOC, ASSOC-WITH, COL.HDG, CONV, DISPLAY-LIKE, FMT, MULTI-
VALUE and SINGLE-VALUE.


Note
These qualifiers require some knowledge of Pick Basic.


FORMAT or FMT "format" (Prime syntax)
Defines a format for a field name or EVAL expression. FMT temporarily overrides the file dictionary in a RetrieVe
(i.e., Access) query. Format specifies the width of the display column, the character used to pad the display field,
the type of justification, the format of numeric data, and a format mask. If you specify format as an empty string, a
default format of 10L is used. For more information, refer to FMT on page 49.

HEADING/FOOTING
Allows users to include a user-defined text within the report at the top (heading) or bottom (footer) of the page. You
may enclose the text in double quotes in the TCL statement (for example, HEADING "text 'modifier options' text").
For more information, refer to HEADING/FOOTING on page 50.



INQUIRING (Prime syntax)
Prompts for record IDs and uses them as a select list.



MATCHES or MATCHING or LIKE pattern (Prime syntax)
Compares a field value or prompt response to a pattern specified in SELECT and WITH/IF statements; if the
value matches the pattern, the match is successful. Use one of the following pattern codes with this keyword:

Pattern Code Description … Any number of characters, including none. 0X Any number of characters, including
none. nX n number of characters 0A Any number of alphabetic characters. nA n number of alphabetic characters 0N

Any number of numeric characters nN n number of numeric characters 'text' or "text" Exact text; any literal string

(quotation marks required).


MAX field.expression [NO-NULLS] (Prime syntax)
Calculates the maximum value of a field in a set of records. Field.expression specifies the name of a field or an
EVAL expression for which you want to display the maximum value. NO-NULLS causes RetrieVe (i.e., Access) to
ignore empty string values.



MIN field.expression [NO-NULLS] (Prime syntax)
Calculates the minimum value of a field in a set of records. Field.expression specifies the name of a field or an
EVAL expression for which want to display the minimum value. NO-NULLS tells RetrieVe (i.e., Access) to ignore
empty string values.

NOT-MATCHING pattern (Prime syntax)
The inverse of MATCHING (contrast with MATCHES or MATCHING or LIKE pattern on page 61).



PERCENT [n] field (Prime syntax)
Calculates the total value of the specified field for all records, then calculates and displays the percent of the total
value of the specified field for each record. ‘n’ is an integer from 0–5 that specifies the number of digits to be
displayed after the decimal point. (The default value is 2 digits.)



SAMPLE [n] (Prime syntax)
Lists or selects the first n records meeting your criteria.



SAMPLED [n] (Prime syntax)
                        th
Lists or selects every n record meeting your criteria.



SAVING [UNIQUE] field (Prime syntax)
Creates a select list containing field values instead of record IDs. The UniVerse Quick Reference (VMark Software,
Inc., 1995) discourages use of SAVING on multivalued fields, but you may want to try it. UNIQUE is optional and
omits duplicates from the saved lists. Use this keyword with SELECT and SSELECT. You also may use it with
QSELECT (for example, QSELECT FILE SAVING 5 would retrieve all values in Attribute 5 of FILE).
TOTAL field
Calculates and displays totals for numeric fields, producing subtotals at breakpoints.



USING [DICT] filename
Specifies an alternative dictionary to be used as the file dictionary.



WHEN mvfield operator (Prime syntax)
{values|field1} [[{AND|OR} mvfield operator] {values|field2}…]
Limits output from multivalued fields to lines containing a specified value. This keyword should be used with
BY-EXP.
                                                Paragraphs
(Prime syntax)


Overview
A paragraph is a list of TCL commands executed in succession. They may be used for
regularly repeating TCL statements. (See your MIS CONTACT for further informa-
tion.)

Paragraphs often are stored in a MD file, which may contain file pointers, verbs, modifiers and/or keywords that
allow users to report on data files using dictionaries.

Paragraph-related TCL functions include:

Creating (saving) a paragraph in a MD file (see Create (Save) a Paragraph in a MD File on page 64)
Naming a paragraph (see Name a Paragraph on page 65)
Storing/restoring a paragraph (see Store a Paragraph (and Copy to another Account) on page 66)
Executing a paragraph (see Execute a Paragraph Stored in a MD File on page 66)
Listing a paragraph (see List a Paragraph Stored in a MD File on page 67)
Recalling a paragraph to the TCL stacker (see Recall a Paragraph Stored in a MD File to the TCL Stacker on page
68)
Deleting a paragraph (see Delete a Paragraph Stored in a MD File on page 69)
Adding comments (see Comments (*) on page 70)
In-line prompting (see Prompting (<<...>>) on page 71)
        Using conditional (if-then) statements (see IF - THEN Statement on page 73)
Inputting data (see DATA Statement on page 75)


Create (Save) a Paragraph in a MD File
You may create a simple paragraph to be stored in either of two stackers:

     For a paragraph in the STACKER utility, enter the .M command. (For more information, refer to the
STACKER Utility Quick Reference Guide, July 18, 2003.)
     For a paragraph in the UniVerse stacker, enter the .S command at the command

   prompt:
   @tcl>.S name start end

where

name is the user-defined name of the paragraph.

              Rules for the paragraph name:

Note
      It cannot include spaces.
      It can be any combination of characters and symbols.
      It cannot be a system-reserved word (a file name like PHYS.ADD or ENC.HIST, or a system verb like LIST, SORT or
SELECT).


start is the stacker line to be placed in the first line of the paragraph. end is the stacker line to be

placed in the last line of the paragraph.



When the paragraph is stored, it is stamped with the time, date and UNIX login on Line 1. For every paragraph, the
letters ‘PA’ must display in the first two character positions of Line 1. (If 'PA' does not display on Line 1, the
computer cannot identify the list of commands as a paragraph.)

For example, to list the stacker, enter the following command:


@tcl>.L



Commands are listed below in the generic format for a mailing label diskette


creation.


006 SELECT ORDER IF DEPT = “S” USING DICT ORDER.S


005 SAVE-LIST JIP.PROV


004 GET-LIST JIP.PROV


003 SSELECT ORDER BY ZIPCODE WITH STATE = "MD" "DE" "VA" "WV" USING DICT ORDER.S


002 SAVE-LIST JIP.PROVPHPZ


001 SORT-LABEL ORDER BY NAME NAME ADDR1 ADDR2 CITY STATE ZIP USING DICT ORDER.S
(CIP
Name a Paragraph

To create a paragraph named PCP.MAIL.DISK, enter the following command:



@tcl>.S PCP.MAIL.DISK 6 1


Note
To create the same paragraph using the STACKER utility, you may

enter the command

@tcl>.C6-1


TO: (MD PCP.MAIL.DISK



The system will return a message:Item "PCP.MAIL.DISK." saved in your MD file.


Note
To get items into the stacker without executing the statement, end the

statement with a &. The & does not appear in the stack; it simply notifies the computer to add the statement to the stacker
without execution.




To make a paragraph identifiable, choose a naming convention to begin each paragraph name with

Login.?????

OR

dept???? Examples of valid names: LUANNN.AUTH1, LMN.AUTH1 or MIS.AUTH1
List a Paragraph Stored in an MD File
For a paragraph in the Prime-style stacker or the STACKER utility, you may view TCL commands stored in the MD
file by entering the following command at the command prompt:

@tcl>CT MD Name



where Name is the user-defined name of the paragraph.


For example (using the paragraph created earlier in Create (Save) a Paragraph in an MD File on page 64):

@tcl>CT MD PCP.MAIL.DISK

The system will return the following:
PCP.MAIL.DISK
001 PA Saved at 13:12:51 09 FEB 2008 by john
002 SELECT ORDER IF DEPT = “S” USING DICT ORDER.S
003 SAVE-LIST JIP.PROV
004 GET-LIST JIP.PROV
005 SSELECT ORDER BY ZIPCODE WITH STATE = "MD" "DE" "VA" "WV" USING DICT ORDER.S
006 SAVE-LIST JIP.PROVPHPZ

007 SORT-LABEL ORDER BY NAME NAME ADDR1 ADDR2 CITY STATE ZIP USING DICT ORDER.S
(CIP


Note
•      When the paragraph is stored, it is stamped with the time, date and
                 UNIX login on Line 1.

•      The letters PA display in the first two character positions on Line 1, which is mandatory for every paragraph.
If PA does not display on Line 1, the computer cannot identify it as a paragraph.


Recall a Paragraph Stored in an MD File to the TCL Stacker
Paragraphs usually are stored in the MD file. If you need to create another paragraph or execute manually
something similar to a paragraph that already exists (for example, after loading a paragraph onto the stack and
modifying it there), you may copy the statements to the list of stacker commands:

•   For a paragraph in the STACKER utility, enter the .P command at the command prompt. (For more
    information, refer to the STACKER Utility Quick Reference Guide, July 18, 2003.)
    @tcl>.P MD name (L

    where

    .P is the Recall MD Paragraph command.

    name is the user-defined name of the paragraph.


Note
For more information, refer to the STACKER Utility Quick Reference

Guide (July 18, 2003).

•   For a paragraph in the Prime-style stacker, enter the following command at the command prompt:
@tcl>.R name
where

    .R is the Recall Paragraph command.

    name is the user-defined name of the paragraph.



Delete a Paragraph Stored in a MD File
You may delete a paragraph stored in the MD file:

•   For a paragraph in the STACKER utility, enter the .DV Name command at the command prompt:
@tcl>.DV name where

    .DV is the Delete command.



    name is the user-defined name of the paragraph.


Note
For more information, refer to the STACKER Utility Quick Reference

Guide (July 18, 2003).

•   For a paragraph in the UniVerse stacker, enter the following command at the command prompt:
@tcl>.D name where

    .D is the Delete command.name is the user-defined name of the paragraph.
Advanced Paragraph Topics
Several advanced features are available when working with paragraphs:

        Adding comments — You may begin a line with a * to denote comments. (The line is not executed.) See
Comments (*) described below.
        In-line prompting — You may add <<...>> (prompts) so that the paragraph displays the prompt on the screen
for the user to enter data. See Prompting (<<...>>) on page 71.
        Setting conditions — You may use an IF - THEN statement to execute an option based on user-specified
conditions (data entry and/or results). See IF - THEN Statement on page 73.
        Inputting data — You may use the DATA statement to allow automatic entry of a computer-prompted
request of input. See DATA Statement on page 75.

Comments (*)
You may insert comments within paragraphs on lines beginning with at least one *. For example:

** This paragraph was written by Test User for the Test Dept.

** The line below has been temporarily removed for annual processing.

** SELECT ORDER IF DEPT # “C” USING DICT ORDER.S

SORT ORDER BY ZIP USING DICT ORDER.S

Prompting (<<...>>)
<<...>> denotes in-line prompting. Use in-line prompting in a paragraph to display a prompt and to request an input
value when the sentence is executed. The command processor executes the sentence as if the input value had been
entered instead of the in-line prompt specification. The input value can be requested from the terminal, extracted
from a file, or supplied by DATA statements in a BASIC program.

Use the following syntax for in-line prompting:

<< [ control, ] ... text [, option] >>

where

control specifies the characteristics of the prompt. Control options include:

    A — Always prompts when the sentence containing the control option is executed. If this option is not
    specified, the input value from a previous execution of this prompt is used.

    Cn — Uses word n from the command line as the input value. (The verb is word
    1.)

    F(fn,m [ ,fm [ ,vm [ .sm ] ] ] ) — Finds the input value from a record (m) in a file (fn). You must specify the file
    and the record; separate them with a comma (i.e., no spaces allowed). The variable vm extracts a value, and sm
    extracts a subvalue from the field (fm); separate values with commas (i.e., no spaces allowed).
    In — Uses word n from the command line, but prompts if word n was not entered.

    P — Saves the input value from an in-line prompt. The input value is used for all in-line prompts with the same
    prompt text, until the input value is overwritten by a prompt with the same prompt text and with a control option
    of A, C, I, or S, or until control returns to the UniVerse prompt. P saves the input value from an in-line prompt
    in the current paragraph or in other paragraphs.

    R — Repeats the prompt until the user presses the [Enter] key.

    R(string) — Repeats the prompt until the user presses the [Enter] key, and inserts
    a string between each entry.

    Sn — Takes word n from the command but uses the most recent command entered at the UniVerse level to
    execute the paragraph, rather than an argument in the paragraph. (This control option is useful when paragraphs
    are nested.)

    @(CLR) — Clears the screen.

    @(BELL) — Rings a bell (i.e., causes most terminals and PCs to “beep”).

    @(TOF) — Issues the top of the form (i.e., clears the screen).
                @(col. row) — Prompts at a specified column and row number on the terminal.

text is the prompt to be displayed.

option is any conversion or pattern match. Conversions are the same as the BASIC ICONV function. Patterns are
the same as those used with the BASIC MATCH operator. A conversion must be in parentheses. The conversion
only verifies the format of the data entered. It does not convert the data. If the data does not match the conversion,
the user is prompted to reenter it.




                             Once a value has been entered for a specific prompt, the prompt continues to display the value
until a CLEARPROMPTS command is executed (if control option A is not specified). The CLEARPROMPTS command clears
all of the valuesthat have been entered for in-line prompts.
          You may enclose prompts within prompts.

IMPORTANT
• You cannot use an in-line prompt as a verb in a sentence.

For example:

1       Enter the value, <<ENTER NAME>>.
2       To verify that a value is a date, enter <<ENTER DATE,(D)>>.
3       Clear the screen and prompt for AMOUNT at column 10, row 5 by entering @(CLR),@(10,5)AMOUNT.

The following paragraph prompts for a new value each time the loop is repeated. If you omit the
CLEARPROMPTS statement, the loop repeats endlessly by using the value for "ENTER MEMBERSHlP.NBR"
entered at the initial prompt.

LIST.MEMBERS

0001: PA

0002: LOOP

0003: CLEARPROMPTS

0004: IF "ENTER MEMBERSHIP.NBR" EQ 'END’ GO END:

0005: LIST SUN.MEMBER WITH @ID EQ <<ENTER MEMBERSHlP.NBR>>

0006: REPEAT 0007: END:

IF - THEN Statement
Use IF in a paragraph to change the operation sequence. The IF statement introduces a condition (i.e., test), results
of which determine the next action. You must use an IF statement in a loop to end the loop (see the example
below). IF statements have no function on the command line.

IF-THEN statements use the following syntax:

IF expression THEN sentence

where expression is the user-specified condition (i.e., test to be performed). sentence is the action to be

executed if the expression evaluates to TRUE.



The example below shows a paragraph as it appears in the MD file:

001: PA

002: IF <<A, End of Month (Y or N)>> = ‘Y’ THEN GO MONTH

003: LIST <<A, Enter filename>> VENDOR AMT.PAlD PYMT.DATE AMT.DUE

004: MONTH: EOM.REPORT

005: END: DISPLAY Done expression The syntax for the test to be performed (expression) is

value1 operator value2

where

valuel is an in-line prompt specification, @variable or constant. In a loop, use control option A in the in-line
prompting expression (see Prompting (<<...>>) on page 71) to issue the prompt every time the loop executes the IF
statement; otherwise the loop issues the prompt only once.

operator is any relational operator. (For a complete list, see QSELECT on page 23.)

value2 must be a constant, an @variable or an in-line prompt.

Example paragraph:

001 PA

002 SELECT ORDER IF DEPT = <<DEPT>> USING DICT ORDER.S

003 IF @SELECTED <= "0" THEN ABORT

004 SELECT ORDER WITH AMT GT "0" USING DICT ORDER.S

005 IF @SELECTED <= "0" THEN ABORT

006 SELECT ORDER WITH SHIP.DT GE "6.1.97" AND LE "9.30.97" USING DICT ORDER.S

007 IF @SELECTED <= "0" THEN ABORT

008 SSELECT ORDER BY NAME BY SHIP.DT USING DICT ORDER.S

009 SAVE-LIST JPR.ORD

010 SP-ASSIGN HS

011 GET-LIST JPR.ORD

012 LIST ORDER BREAK-ON DEPT BREAK-ON NAME
BREAK-ON SHIP.DT CITY STATE ZIP TOTAL AMT ONE HEADING "ORDER <<DEPT>> FOR JUNE
JULY AUG & SEPT" USING DICT ORDER.S (IP




DATA Statement
Use a DATA statement in a paragraph to specify a response to an input request. To use the DATA statement:

1       Enter the Program (or Verb) Name on one line.
2       Enter the DATA Statement on the next line(s).

Use the following syntax:

@tcl>DATA data

where
data is free-form, user-defined.

When a BASIC program/sentence, paragraph or command executed in the paragraph requires input, it uses the data
from the first DATA statement rather than prompting for data from the terminal to be input by the user. The second
time input is required, the data from the second DATA statement is used, etc. When the data from all previously
executed DATA statements has been used, a program, sentence, paragraph or command that requires prompts for a
response to input data from the terminal.


Note
• You may use in-line prompting with a DATA statement.

• You may use DATA statements only in a paragraph. They have no function in the command line.

The example below represents a paragraph as it displays in a MD file: EXAMPLE.LABELS


001 PA


002 LIST-LABEL ORDER NAME ADDR1 ADDR2+ZIP COL-HDR-SUPP ID-SUPP (P 003 DATA 3.5,1,0,38,2 EXAMPLE.LISTS



001 PA



002 LIST-DIFF <<ENTER FIRST.LIST>>



003 DATA <<ENTER SECOND.LIST>>



004 DATA <<ENTER NEW.LIST>>

								
To top