|
Summer 1998
|
The MV Basics series is an occasional series of articles of a generally technical theme, designed for those of you who are new to the MultiValue way of life. It will also be of interest to the old hands who may have forgotten one or two tricks of the trade! This guide to Access has been compiled by Simon Patterson of Beauchamp Computer Services Ltd.
ACCESS is PICK’s query language. It allows users to query and display the data within the database in as much or as little detail as required. The data can be sorted, selected, and generally manipulated in many ways to generate the desired output. Thus ACCESS provides for a range of uses from the simplest queries to the most complex reports. Combined with other TCL commands, it also provides the System Administrator with very powerful tools for managing the database. Finally, ACCESS provides programmers with a quick method of producing reports whatever else their program is doing!
MultiValue systems, i.e. those using the Pick data model, use a 4-level file hierarchy as the structure supporting the relational database. We will be looking in detail at the relationship between a dictionary file and its data files(s).
The data file holds information in the form of items. The dictionary file holds specific information about its data file(s) in two types of items. The first type of item, a file definition item (D-pointer), defines which data files exist for that dictionary. The second type of item, an attribute definition item (known as an A-pointer or word), describes each attribute of the item(s) in the data file i.e. how each item is made up, line by line.
To summarize: data files contain items of the same format such that, for example, the name is always stored in attribute one, the address in attribute two, the telephone number in attribute three etc. The dictionary file (of such a data file) can then contain A-pointer items (or words) defining each of these attributes of the data file. These A-pointers are then used in ACCESS queries to reference those data.
All attribute definition items conform to the following layout:
| Attribute | Description |
| 0 1 2 3 4 5 6 7 8 9 10 |
Item-id Definition Code Attribute Number Column Header Controlling/Dependent Not used Not used Conversion Correlative Type Width |
| A | Primary attribute definition. |
| S |
Synonym attribute definition, that is an alternative definition of an attribute even though multiple A-pointers to any given attribute are also allowed. |
| X |
A protected attribute definition which does not appear as output and usually only in selection criteria. |
| 0 |
is used to reference the item-id or if the output is computed and is not stored on the item. |
| 9998 | outputs the number of items listed so far. |
| 9999 | outputs the size of the current item in bytes. |
| L |
Output is displayed/sorted with left justification. Only use for attributes containing purely alphabetic data. |
| R | Right justification display/sort. Use for all attributes other than pure alphabetics. |
| T |
As “L” but if the output from the attribute is longer than the width (see below) then output is wrapped at the last space character. |
| U |
As “L” but the output from the attribute is allowed to exceed the width specified below. |
ACCESS statements are of the following format. Mandatory parameters are in bold, optional parameters are in italics:
>VERB filename itemlist criteria modifiers outputlist (options)
Thus a statement can be as simple as:
>LIST MD
or as complex as:
>SORT SWANSEA-TIDES WITH DATE GE “31/12/93” AND WITH DATE
LE “31/12/99” BY DATE BREAK-ON DATE TIME HEIGHT WATER ID-SUPP HEADING
“Page ‘P5’ SWANSEA TIDES (KING’S DOCK, OUTER SILL) 1994
‘TL’”
Note how literal strings within the statement must be enclosed in double quotes. Backslashes can also be used. If single quotes are used then ACCESS assumes the quantity enclosed thereby refers to an item-id.
The various components of the ACCESS statement are as follows:
VERB - The TCL command which informs ACCESS as to the type of processing required.
filename - The name of the file to process. Only one data file can be processed directly in an ACCESS statement although a different dictionary file can be used (i.e. not the data file’s dictionary file) by employing the “USING” modifier.
Itemlist - A specified list of item-ids surrounded by double/single quotes or backslashes.
Criteria - Criteria made up of tests (A-pointers, connectives etc) to selectively process a limited set of data. For example, the date range specified in the second statement above.
Modifiers - Special ACCESS commands to format the report to produce, for example subtotalling.
Outputlist - A list of A-pointers (words) whose data is to appear in the report. Note that if the sum of the widths of the A-pointers to be printed exceeds the width defined for the output device (screen or printer - see TERM command) then the report will not appear in columnar format but will use one line per A-pointer.
(Options) - The following options apply to most ACCESS verbs. They must be enclosed in parentheses. They also have wordier alternatives which are not enclosed in parentheses.
| B | Suppress the linefeed after the compile phase. |
| C | Suppress column and default page header (see COL-HDR-SUPP). |
| D | Suppress detail lines (see DET-SUPP). |
| F |
Formfeed before every new item. Only applicable to LIST-ITEM, SORT-ITEM verbs. |
| H | Suppresses default page header (see HDR-SUPP). |
| I | Suppresses default item-ids (see ID-SUPP). |
| N | Suppresses paging to terminal (see NOPAGE). |
| P | Directs output to printer (see LPTR). |
| n |
Limits the number of items processed by the command (i.e. LIST, SORT, SELECT etc) to the quantity n for Icon PICK etc. ADDS users should use the syntax L,n (see also the SAMPLING modifier section). |
To perform wildcard searches in ACCESS the following characters are used:
| ^ | Any single character. |
| [Text | ending with text. |
| Text] | beginning with text. |
| [Text] | containing text. |
ACCESS performs two passes when looking for the A-pointers specified in the outputlist. The first pass looks in the current dictionary file. The second pass looks in the current MD. Thus A-pointers and any other ACCESS operators (such as throwaway connectives) applicable to all files within an account are stored in the MD. When an account is created the A-pointers *A0 to *A10 are created in its MD allowing access to the id and first 10 attributes of any item in any file in that account.
N.B.
1. The use of the terms “conversion” and “correlative” in this section is not arbitrary. Conversions work best in attribute 7, and correlatives best in attribute 8 of A-pointers. Thus T-correlatives are so called because they should be put in attribute 8. D-conversions should be put in attribute 7 etc.
2. ACCESS only performs integer mathematics - it ignores figures after the decimal point. Thus, data stored with the decimal point must be converted to integer format before being used in calculations (see F-correlative example 1). Also, data must not be converted back to decimal format until all calculations have been performed via integer format.
For example when calculating averages, to print the average on the break-line, it is necessary to use an A-conversion and to total the quantity being averaged:
| SALES | ISALES | AVERAGE.SALE | |||
| 001 002 003 004 005 006 007 008 009 010 |
A 31 Sales MR2 R 12 |
001 002 003 004 005 006 007 008 009 010 |
A 31 Integer]Sales R 12 |
001 002 003 004 005 006 007 008 009 010 |
A 0 Average Sales AN(ISALES)/ND]MR2 R 12 |
>SORT CUSTOMERS BY REGION BREAK-ON REGION TOTAL SALES AVERAGE.SALE
(DI
(To print the average sales figure overall on the grand-total line then
use TOTAL AVERAGE.SALE in the statement above.)
An;expression
where the optional parameter n supposedly specifies the number of decimal digits (0-4) to be retained during calculations (doesn’t work on any known version of PICK). The expression is made up of any of the following functions:
Operators: The following arithmetic operators are supported: +,-,*,/. Relational operators <,>,>=,<=,=,# are available to perform Boolean arithmetic.
Attribute Numbers: The data stored within an attribute is retrieved by specifying the attribute number within the A-correlative. For example, to add the contents of attribute 1 to attribute 2:
A1+2
Special attribute numbers for the current item are:
| 0 | for the item-id |
| 9998 | for the number of the current item in the listing |
| 9999 | for the size in bytes. |
Attribute Names: Any attribute name (the item-id of any word (A-pointer) in the current dictionary) can be used in an A-correlative. For example, to compute the invoice amount from the goods and VAT amounts:
AN(GOODS)+N(VAT)
Thus one A-correlative can call another word which itself can be an A-correlative (or any other type of word). The number of calls by words of other words cannot be more than 7 levels deep or an error message such as “RTN STACK FULL” will be given.
Literal Numbers/Strings: Literal number and strings are specified in either double or single quotation marks. For example, to multiply the contents of attribute three by ten:
A3*“10”
To build up the address as a comma delimited string:
AN(ADDR1);", ":N(ADDR2):", ":N(ADDR3):",
":N(ADDR4):", ":N(PC)
Special Operands: The following special operands can be used within A-correlatives exactly like any attribute number, attribute name or literal.
| NI | the item counter |
| NV | the value counter |
| NS | the subvalue counter |
| ND | the detail-line counter |
| NB | the break-level counter |
| LPV | load previous value |
| D | Current system date (days from 31.12.1967) |
| T | Current system time (seconds from midnight) |
MV Basics: The Access Query Language - Part 2
Last Updated: 6 November 1998