MV Basics

The Access Query Language, Part 5

SUPP (modifier)

A wordier synonym for the “H” option and also a synonym for HDR-SUPP i.e. suppresses the default page heading (page number, time and date) and the “x ITEMS LISTED” message at the end of the listing.

 

T-DUMP (verb)

> T-DUMP filename itemlist criteria modifiers HEADING “text” (options

 

Dumps the entire items specified to the previously attached tape device in the order they are stored on file. 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

T-LOAD (verb)

>T-LOAD filename itemlist criteria modifiers (options

 

Loads the items specified from the previously attached tape device in the order they are stored on the tape. The options are:

 

I	Suppress display of item-ids being loaded (doesn’t suppress the display of item-ids not being loaded!)
O	Overwrite existing items on file

T-correlative (text extraction)

The first form of the T-correlative, text extraction, allows a length of text to be extracted from a string. It takes the following format:

 

Tn,m

 

where:

 

n	Start position for text extraction. If not specified, then text extraction begins from either the left or right-hand side of the string according to the justification of the A-pointer.
m	Length of text to be extracted. For example:
	T1,10	extracts the first 10 characters from the string;
	T10	in conjunction with a right-justified A-pointer extracts the last 10 characters from the string.

 

 

T-correlative (file translation)

The second form of the T-correlative, a file translation, allows data in the current file to cross-reference data in a second file. It takes the following format:

 

Tfilename;xv;i;o;b

 

where:

 

x	Translate code:
	C	Prints current quantity if this quantity is not found in the remote file.
	X	Prints blanks if the current quantity is not found in the remote file.
	V	Prints an error message if the current quantity is not found in the remote file.
	I	Verify input only. This performs the equivalent of a “V” on the input attribute (see “i” below) and “C” on the output attribute (see “o” below).
	O	Verify output only. This performs “C” on “i” and “V” on “o”.
v	Optional value number. The attribute number in the A-pointer specifies which attribute to extract the quantity to be translated from. The quantity can also be extracted from any one value within that attribute as specified by “v”.
i	This parameter must always be blank for ACCESS (the DATABASIC keyword ICONV uses this parameter as an attribute number; the contents of which as the item-id in the remote file. ICONV is sonly supplied with an item-id of, not an A-pointer pointing to an attribute in, the current file).
o	The number of the attribute in the remote file the current quantity is translated into.
b	The number of an attribute in the remote file whose contents is used in break-lines and total-lines.

For example, if the employee’s department-id is stored in attribute 2 of the employee item, and the department’s title is stored in attribute 1 of the department file, then the following A-pointer will print the department title for an employee:

 

DEPT.TITLE
001 A
002 2
003 Department Title
004
005
006
007
008 TDEPARTMENTS;C;;1
009 L
010 30

 

So to print the employee-ids, surnames, and department titles, in aplhabetical order by surname:

 

>SORT EMPLOYEES BY SURNAME SURNAME DEPT.TITLE

 

TAPE (modifier)

The TAPE modifier allows ACCESS statements to be performed on a file previously dumped to tape, assuming that the tape is positioned at that file. For example, consider the employee file which was archived to tape prior to performing the year-end update which deleted all leavers. This archived data on the tape can be queried by ACCESS as follows:

 

>SORT EMPLOYEES SURNAME INITIALS TAPE

 

This will produce a listing to the screen of the employees’ surnames and intials, sorted by employee-id, from the tape. Note that since the data is being read from the tape the filename specified in the ACCESS statement is academic. Thus any valid filename can be used.

 

THE (throwaway connective)

The word “THE” can be used anywhere in an ACCESS statement to increase its readbility. Such words are actually ignored by ACCESS itself.

 

TOTAL (modifier)

The TOTAL modifier allows a named A-pointer to be totalled and can be used to apply selection criteria to that A-pointer’s data. For example, to list all employees paid more than 1000.00, their employee-ids and surnames, by department, subtotalling pay by department:

 

>SORT EMPLOYEES BY DEPARTMENT BREAK-ON DEPARTMENT SURNAME TOTAL PAY WITH PAY GT “1000.00”

 

To only print the department titles, totals and grand total for all employees:

 

>SORT EMPLOYEES BY DEPT.TITLE BREAK-ON DEPT.TITLE TOTAL PAY DET-SUPP

 

U-correlative (user exits)

The U-correlative allows user-written assembly language routines to be called. N.B. Under no circumstances whatsoever make any attempt to use U-correlatives. The consequences will be catastrophic and probably fatal!

 

USING (modifier)

The USING modifier allows the A-pointers in a remote dictionary file to be used on the current file. For example, consider the employees archive file which is a copy of the live employees file immediately prior to the year-end. Obviously the dictionary of this archive could also hold a copy of the A-pointers within the dictionary of the employees file. It is preferable however to only have one dictionary per file-set (group of files of identical layout) so that once an A-pointer is available to one such file, it is available to all. This can be achieved via the USING modifier. Hence the employees dictionary file can be used on the employees archive file as follows:

 

>SORT EMPLOYEES-ARCHIVE USING DICT EMPLOYEES SURNAME INITIALS

 

V-correlative (within-sublist)

The V-correlative identifies attributes that hold multivalues which are usually ids of items within the same file. It takes the form:

 

V;;n

 

where n is the number of the attribute holding the multivalues. Only used with the WITHIN connective.

 

WITH (connective)

Specifies that the data must be present to be included on the listing. A synonym for the IF connective; they are interchangeable. For example, when listing the surnames and pay of all employees with the surname “Smith”, the following two statements are equivalent:

 

>LIST EMPLOYEES WITH SURNAME = “SMITH” SURNAME PAY
>LIST EMPLOYEES IF SURNAME = “SMITH” SURNAME PAY

 

WITHIN (connective)

This connective allows criteria to be applied to multivalued attributes which contain item-ids of items which are within the same file. The D-pointer of the file must contain the appropriate V-correlative.

 

WITHOUT (connective)

The WITHOUT connective can be used instead of “WITH NO” or “WITH NOT” connectives to detect null (empty) attributes since they are all synonymous. Thus all of the following statements give the same result:

 

>COUNT EMPLOYEES WITHOUT PAY

>COUNT EMPLOYEES WITH NO PAY

>COUNT EMPLOYEES WITH NOT PAY

 

 

Simon Patterson
Beauchamp Computer Services Ltd

Last Updated: 22 July 1999

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