Spring 1999   

MV Basics

The Access Query Language, Part 4

MC-conversion (mask character)
The MC-conversion is used to change the case of alphabetic characters within data, or convert decimal data to hexadecimal data, or strip out certain undesirable characters from data.
 
MCU Converts all lower-case letters to upper-case.
MCL Converts all upper-case letters to lower-case.
MCA Extracts all alphabetic characters from data.
MCN Extracts all digits (0-9) from data.
MC/A Deletes all alphabetic characters from data.
MC/N Deletes all digits (0-9) from data.
MCT Converts the first letter of every word in the datum to upper-case and all remaining letters to lower-case.
MCDX Converts decimal data (base 10) to hexadecimal data (base 16).
MCXD Converts hexadecimal data to decimal data.
MCP Converts all unprintable characters (i.e. ASCII codes 0-31 and 127-255) to full-stops.
 
 
MT-conversion (times)
PICK stores times as the number of seconds from midnight (i.e. 00:00:00, 12:00am). This number of seconds in known as the internal time. Thus internal time 1 is one second after midnight (00:00:01), 86399 is one second before midnight tomorrow (23:59:59), 172800 is midnight 48 hours hence etc.
 
This not only simplifies sorts and selections on times (only integers are being compared) and calculations (e.g. 3 hours from now = internal time now + 3*60*60) but also allows the time to be output in a wide variety of formats. An internal time converted on output gives the external time. Valid MT-conversions are as follows:
 
 MT  24-hour hours and minutes.
 MTH  AM/PM hours and minutes.
 MTS  24-hour hours, minutes, and seconds.
 MTHS  AM/PM hours, minutes, and seconds.
 
 
Internal Time Conversion External Time
0 MT 00:00
43200 MT 12:00
0 MTH 12:00AM
43200 MTH 12:00PM
0 MTS 00:00:00
43200 MTS 12:00:00
0 MTHS 12:00:00AM
43200 MTHS 12:00:00PM
9 MTS 00:00:09
99 MTS 00:01:39
999 MTS 00:16:39
9999 MTS 02:46:39
99999 MTS 27:46:39
999999 MTS 277:46:39 (i.e. 277 hours, 46 minutes, 39 seconds since midnight).

 

MX-conversion (ASCII to hexadecimal)
The MX-conversion converts strings of characters, one character at a time, into the two-character hexadecimal representation. Occasionally used to find unprintable characters in data (see also MCP). For example:
 
ABC converts to 414243
123 converts to 313233
 
MY-conversion (hexadecimal to ASCII)
The MY-conversion converts strings of hexadecimal character-pairs, one pair of characters at a time, into the single ASCII character. Thus the MY-conversion is the converse of the MX-conversion. For example:
 
414243 converts to ABC
313233 converts to 123
 
NE / # (not-equal-to operator)
In selection criteria “NE” or “#” is used to exclude data. Both the following statements produce the same result.
 
>COUNT EMPLOYEES WITH PAY NE “1000.00”
>COUNT EMPLOYEES WITH PAY # “1000.00”
 
NO (null operator)
The “NO” operator is used in conjunction with the “WITH” connective to detect null (empty) attributes. “WITH NO”, “WITH NOT”, and “WITHOUT” are all synonymous. Thus all of the following statements give the same result:
 
>COUNT EMPLOYEES WITH NO PAY
>COUNT EMPLOYEES WITH NOT PAY
>COUNT EMPLOYEES WITHOUT PAY
 
NOPAGE (modifier)
A wordier synonym for the “N” option i.e. suppresses the pause (for the <RETURN> key) at the bottom of each screen of output to the terminal.
 
NOT (null operator)
The “NOT” operator is used in conjunction with the “WITH” connective to detect null (empty) attributes. “WITH NOT”, “WITH NO”, and “WITHOUT” are all synonymous. Thus all of the following statements give the same result:
 
>COUNT EMPLOYEES WITH NOT PAY
>COUNT EMPLOYEES WITH NO PAY
>COUNT EMPLOYEES WITHOUT PAY
 
NSELECT (verb)
>NSELECT filename itemlist criteria modifiers
 
This verb functions in exactly the same way as the “SELECT” verb except that it removes the matching items from the currently active itemlist (of item-ids). It is usually used to deselect items from the current file with respect to another file. For example to select all programs in the file BP which did not compile, use the following:
 
>SELECT BP
350 items selected.
 
>NSELECT DICT BP
34 items selected.
 
>SAVE-LIST BAD.BP.PROGS
 
OF (throwaway connective)
The word “OF” can be used anywhere in an ACCESS statement to increase its readability. Such words are actually ignored by ACCESS itself.
 
ONLY (modifier)
The “ONLY” modifier is used to suppress implicit A-pointers (those A-pointers with numeric item-ids which are used for listings if no other A-pointer is specified) when listing or sorting a file. This modifier will only work when placed between the verb and the filename:
 
>LIST ONLY EMPLOYEES
 
OR (connective)
“OR” is the default logical connective used to separate item-lists and selection-criteria clauses in ACCESS statements. It is used when any one clause within the criteria need be true. Since “=” (see “EQ”) is the default operator the following two statements are equivalent:
 
>SORT-ITEM DICT CUSTOMERS WITH *A1 = “A” OR WITH *A1 = “S” OR WITH *A1 = “X” LPTR
>SORT-ITEM DICT CUSTOMERS WITH *A1 = “A” “S” “X” LPTR
 
Note that “AND” takes precedence over “OR” i.e. an “AND” clause ends as soon as the next “OR” (working from left to right) is met.
 
For example, to list all employees with the surname “SMITH” and forenames of either “JOHN” or “PAUL”:
 
>LIST EMPLOYEES WITH SURNAME = “SMITH” AND WITH FORENAME = “JOHN” OR WITH SURNAME = “SMITH” AND WITH FORENAME = “PAUL”
 
Note that because the “OR” in the above statement effectively splits up the two clauses, the surname criterion has to be specified in both clauses to ensure that only John Smiths and Paul Smiths are listed.
 
P-correlative (pattern matching)
The P-correlative allows data to be tested for specfic pattern matching (any combination of digits, letters, mixtures of both, or literals). If the datum does not match the pattern required then blanks are printed. It takes the form:
 
P(m);(m)
 
where m can be either an integer (such that 0 indicates any number of characters, 1 indicates one character, 2 two characters etc) followed by A (letters), N (digits), X (any characters); or a literal string. Thus:
 
P(0A) Any letters.
P(3A) Precisely three letters.
P(1A3N);(1N3A) Either precisely one letter and three digits, or one digit and three letters.
P(2N-2N-2N) Precisely two digits hyphen two digits hyphen two digits.
P(E7N1A) Precisely “E”, seven digits, one letter.
P(1A0X) One letter followed by any letters and/or digits.
P(1N0X) One digit followed by any letters and/or digits.
 
QSELECT (verb)
>QSELECT filename itemlist (attribute number
 
Not a true ACCESS verb (since it does not accept criteria and modifiers etc), QSELECT generates an implicit itemlist from either all attributes if no attribute number is specified, or the specified single (multivalued) attribute, in the requested items. Thus QSELECT works in the same way as SELECT/SSELECT/ GET-LIST but allows the itemlist to be created from within items.
 
Especially useful in relational databases where for example, only the invoice-ids for a given customer are stored on one, multivalued attribute in the customer item; the associated invoice details being in the invoice file. QSELECT can be used to extract these invoice-ids from the customer file, as an implicit itemlist, ready for subsequent use by other ACCESS statements on the invoice file. For example:
 
>SELECT CUSTOMERS WITH INVOICED = “Y”
10 items selected.
 
>QSELECT CUSTOMERS (20
25 items selected.
 
>SUM INVOICES AMOUNT
 
Thus statement one selects all invoiced customers producing an implicit itemlist of customer-ids. Statement two extracts all invoice-ids from those customers producing an implicit itemlist of invoice-ids (attribute 20 being a multivalued list of invoice-ids). Statement three then totals the amounts of those invoices.
 
R-correlative (numeric range testing)
The R-correlative provides for inclusive numeric range testing. Any quantities not within the specified range are printed as blanks. This correlative takes the form:
 
Rn,m;n,m
 
where n is the lower limit of the range and m the upper limit. Multiple ranges can be specified. For example:
 
R1,99 The inclusive range 1 to 99.
R-99,-1;1,99 The inclusive range -99 to 99 excluding 0.

REFORMAT (verb)
>REFORMAT filename outputlist
FILE NAME:filename or TAPE
 
REFORMAT and its sibling SREFORMAT are the only ACCESS commands which can update files. These very powerful commands should be used with caution. The filename and outputlist must always be specified. The data generated by the outputlist is written back into the filename entered at the “FILE NAME” prompt (target file) in the listed sequence. Thus the first A-pointer in the outputlist generates the item-id of the new item in the target file. The second A-pointer in the outputlist generates the first attribute in the new item in the target file etc.

This target file can be:
N.B. Simply by hitting <RETURN> to the “FILE NAME” prompt you will either create new items in the current file or overwrite existing items in the current file. You have been warned!
 
S-DUMP (verb)
>S-DUMP filename itemlist criteria modifiers HEADING “text” (options
 
Sorts and dumps the entire items specified to the previously attached tape device. If a heading is specified it is used as the tape label. The options are:
 
H Suppress tape label.
I Suppress display of item-ids being dumped.

S-correlative (data substitution)
The S-correlative allows substitution of data in two instances i.e. when the data is neither null nor zero, and when the data is either null or zero. It takes the form:
 
S;x;y
 
x

 Can be either an attribute number or a literal string (enclosed in double-quotes, single-quotes or back-slashes) which is substituted for the data if the data is neither null nor zero.
y
 Can be either an attribute number or a literal string which is substituted for the data if the data is either null or zero.
 
For example:
 
S;”Balance outstanding”;”Account settled”
 
prints “Balance outstanding” for non-zero amounts and “Account settled” for zero/null amounts.
 
SAMPLING (modifier)
AP’s “SAMPLING” modifier allows selects to be limited to the number of items specified. For example to select the first ten customers by name:
 
>SSELECT CUSTOMERS BY NAME SAMPLING 10
 
ADDS/MentorPRO/mvBASE users should use either of the following syntaxes:
 
>SSELECT CUSTOMERS BY NAME (L10
>SSAMPLE CUSTOMERS BY NAME (10
 
Icon/Upboard users should use the following syntax:
 
>SSELECT CUSTOMERS BY NAME (10
 
SAVE-LIST (verb)
>SAVE-LIST filename listname
 
Saves the item-ids from an implicit itemlist (i.e. produced by SELECT/SSELECT/GET-LIST/QSELECT) as a list in the specified file. The default file if none specified is the pointer-file. If the name of an existing list is used then the old list will be overwritten.
 
SELECT (verb)
>SELECT filename itemlist criteria modifiers
 
This verb functions in exactly the same way as the “LIST” verb but instead of printing output to the screen or printer it produces an implicit itemlist (of item-ids) which must be used in the very next TCL command. If the next TCL command is mistyped then the implicit itemlist is destroyed and the select must be done again. Implicit itemlists can be saved permanently using the “SAVE-LIST” verb.
 
SORT (verb)
>SORT filename itemlist modifiers outputlist (options
 
This verb sorts items and prints the data specified in the outputlist. By default, the items are sorted with respect to their item-ids. However, the items in the file can be sorted with respect to the output of any and all A-pointers by using the BY/BY-DSND modifiers.

For example, to sort employees by their item-ids, to print their item-ids, surnames and initials:
 
>SORT EMPLOYEES SURNAME INITIALS
 
To sort employees by department, by surname, to print the item-id, surname, initials, and a subtotal of the number of employees in that department, and a grand total of the number of employees:
 
>SORT EMPLOYEES BY DEPARTMENT BY SURNAME BREAK-ON DEPARTMENT SURNAME INITIALS TOTAL ITEM.COUNT
 
where ITEM.COUNT is an A-pointer defined in the MD:
 
ITEM.COUNT
001 A
002 0
003
004
005
006
007
008 A”1"
009 R
010 0
 
SORT-ITEM (verb)
>SORT-ITEM filename itemlist criteria modifiers (options
 
This copies all items in a file in the order specified, or just the requested items, to the screen or printer.
 
SORT-LABEL (verb)
>SORT-LABEL filename itemlist criteria modifiers outputlist (options
 
This command prints formatted labels on to self-adhesive label stationery either sorted with respect to the item-id or sorted with respect to any and all A-pointers using the BY/BY-DNSD modifiers.
 
After pressing <RETURN> the hideously unfriendly prompt “?” appears which requires the following information to be entered, each quantity separated by a comma (or else!):
 
Columns The number of labels across each page.
Rows The number of lines to be printed per label.
Skip The number of lines between labels.
Indent The number of spaces in the left margin.
Size The number of characters per label line.
Space The number of characters between labels.
“C” Optional. Used to compress null values.
 
Once <RETURN> has been hit, if a left margin was specified then further “?”s will appear to the number specified in rows. Enter any comments which will be printed in the left margin for each print line on the label.

N.B. If ((columns * (size + space)) + indent) is wider than the quantity for the printer’s page width (re: TCL’s TERM verb) then the labels will print incorrectly.

For example:
 
>SORT-LABEL CUSTOMERS NAME ADDR1 ADDR2 ADDR3 ADDR4 POSTCODE (IC ?3,6,3,0,40,1
 
SREFORMAT (verb)
>SREFORMAT filename itemlist criteria modifiers outputlist
FILE NAME:filename or TAPE
 
The SREFORMAT verb updates files and this very powerful command should be used with caution. The filename and outputlist must always be specified. The data generated by the outputlist, in the stipulated sorted sequence, is written back into the filename entered at the “FILE NAME” prompt (target file). Thus the first A-pointer in the outputlist generates the item-id of the new item in the target file. The second A-pointer in the outputlist generates the first attribute in the new item in the target file etc.
 
This target file can be:
N.B. Simply by hitting <RETURN> to the “FILE NAME” prompt you will either create new items in the current file or overwrite existing items in the current file. You have been warned!
 
SSELECT (verb)
>SSELECT filename itemlist criteria modifiers
 
This verb functions in exactly the same way as the “SORT” verb but instead of printing output to the screen or printer it produces an implicit itemlist (of item-ids) which must be used in the very next TCL command. If the next TCL command is mistyped then the implicit itemlist is destroyed and the select must be done again. Implicit itemlists can be saved permanently using the “SAVE-LIST” verb.
 
STAT (verb)
>STAT filename itemlist criteria
 
The STAT verb counts, totals the sizes of, and prints an average item size for, the requested items.
 
SUM (verb)
>SUM filename itemlist criteria modifiers A-pointer-id
 
The SUM verb totals the specified A-pointer for the items requested. If an A-pointer is not specified then the item-id is used and the size of each item is totalled. Obviously only numeric quantities can be totalled for attributes other than 0 but no error will be given by an attempt to total mixed, alphanumeric data. Thus:
 
>SUM EMPLOYEES
 
produces the same result as:
 
>LIST EMPLOYEES TOTAL SIZE COL-HDR-SUPP DET-SUPP
 
And:
 
>SUM EMPLOYEES PAY
 
produces the same result as:
 
>LIST EMPLOYEES TOTAL PAY DET-SUPP COL-HDR-SUPP
 
Though SUM does not perform a formfeed prior to printing its total (whereas LIST does) and the SUM statement performs noticeably more quickly than the LIST equivalent. N.B. In R83 Pick and some of its derivatives the total printed by SUM is equal to the total of the quantities plus the number of items processed! This is due to a bug where the quantity summed is always one greater than the quantity on file.

MV Basics guide to the Access Query Language will conclude in the next issue of MultiValue News

MV Basics: The Access Query Language - Part 5

Last Updated: 21 July 1999

| Home | | What's New | | News | | Articles | | Workspace | | Pick of the Web | | InfoCentre | | Parting Shots | | About Us | | Subscribe |