MV Basics

The Access Query Language, Part 3

F-correlatives continued...

Example 2
For those users whose version of PICK does not support B-correlatives then the following B-correlative:

STATUS
001 A
002 28
003 Status
004
005
006
007
008 B;MESSAGE.STATUS
009 T
010 30

which calls the subroutine:

MESSAGE.STATUS
001 SUBROUTINE MESSAGE.STATUS
002 INCLUDE BASLIB ACCESS.COMMON
003 BEGIN CASE
004 CASE CURRENT$='S'
005 CURRENT$='Sent on '
006 CURRENT$=CURRENT$:OCONV (ITEM$<21>,'D2/')
007 CURRENT$=CURRENT$:' at '
008 CURRENT$=CURRENT$:OCONV (ITEM$<22>,’MTS’)
009 CASE CURRENT$='B'
010 CURRENT$=’Verbally confirm receipt’
011 CASE CURRENT$=’F’
012 CURRENT$=’Failed - ‘:ITEM$<25>
013 CASE 1
014 CURRENT$='Posted on '
015 CURRENT$=CURRENT$:OCONV (ITEM$<5>,’D2/’)
016 CURRENT$=CURRENT$:’ at ‘
017 CURRENT$=CURRENT$:OCONV (ITEM$<6>,’MTS’)
018 END CASE
019 RETURN CURRENT$

which converts the single character status flag S/B/F/P into the following text:

S	Sent on 30/06/94 at 10:43:57
B	Verbally confirm receipt
F	Failed - No response from remote fax machine
P	Posted on 30/06/94 at 10:44:43

can be emulated via F-correlatives as follows:

STATUS
001 A
002 28
003 Status
004
005
006
007
008 AN(SENT):N(CONFIRM):N(FAILED):N(POSTED)
009 T
010 30


SENT
001 A
002 0
003 Sent Status
004
005
006
007
008 F28;“S”;#;“999”;*; “Sent on ”;21;(D2/);:;“ at ”;:;22;(MTS);:;;“999”;[]
090 L
010 30


CONFIRM
001 A
002 0
003 Probable Status
004
005
006
007
008 F28;“B”;#;“999”;*;“Verbally confirm receipt”;;“999”;[]
009 L
010 30


FAILED
001 A
002 0
003 Failed Status
004
005
006
007
008 F28;“F”;#; “999”;*;“Failed - ”;25;:;;“999”;[]
009 L
010 30


POSTED
001 A
002 0
003 Posted Status
004
005
006
007
008 F28;“P”;#;“999”;*;“Posted on “;5;(D2/);:;“ at ”;:;6;(MTS);: ;; “999”;[]
009 L
010 30


It goes without saying that the combination of a B-correlative and subroutine is both more concise and vastly more easily understood than the “pure” ACCESS solution based on A and F correlatives.

Example 3
On output, ACCESS lists multivalued attributes in the same column, each value being on a new line. Thus address-lines stored as multivalues appear on listings in a single, neat column. If you store address lines in individual attributes you might have tried to emulate the above effect via a C-correlative which concatenates the address lines with value-marks. Needless to say this doesn’t work! However, the following F-correlative does work:

COLUMNAR.ADDRESS
001 A
002 0
003 Address
004
005
006
007
008 F8;(ML#30);9;(ML#30);:;10;(ML#30);:;11;(ML#30);:;12;(ML#30);:;14;:
009 T
010 30


Where the maximum address-line length is 30 characters, the 5 address-lines are stored in attributes 8-12 and the postcode is stored in attribute 14. The T in attribute 9 of the A-pointer effectively forces ACCESS to wrap the output at character 31, 62, 93 etc and as each address-line is formatted to 30 characters (by ML#30) the entire address appears in a single column, each address-line on a new line, just as if the address was stored in multivalues.

Example 4
To output a date stored in attribute n in the format:

1st December 1997
2nd December 1997
3rd December 1997
4th December 1997 etc.

use the following combination of F- and A- correlatives:

FULL.DATE
001 A
002 0
003 Full Date
004
005
006
007
008 Fn(DD)]AIF R(LPV,“10")= “1” AND LPV#“11” THEN “st” ELSE IF R(LPV, “10”)= “2” AND LPV# “12” THEN “nd” ELSE IF R(LPV , “10”)=“3” AND LPV#“13” THEN “rd” ELSE “th”]Fn(DD);LPV; :;“ ”;:;n(DMA);(MCT);:; “ ”;:;n(DY);:
009 L
010 20



Example 5
To output the age in years for a date of birth stored in attribute n:

AGE
001 A
002 0
003 Age
004
005
006
007
008 FD(DM);D(DD);:;n(DM);n(DD);:;>;“-1”;*;D(DY);n(DY);-;+
009 R
010 3



FILE (throwaway connective)

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

FOOTING (modifier)

Text line(s) to be printed at the bottom of each page of the listing whether to screen or printer. Literal text in enclosed in double quotes. Special options are enclosed in single quotes and those options are as follows:

B	Used with the “B” option of BREAK-ON to insert the break quantity in the footing.
C	Centres the footing line.
D	Prints current, external date i.e. “16 FEB 1994”.
Fn	Prints current data filename. Optionally a number “n” can be specified so that the filename is printed left-justified in “n” spaces.
L	Linefeed/carriage-return (more than one footing line).
PN	Print page number.
P	Print page number right-justified in 4 spaces.
Pn	Print page number left-justified in “n” spaces.
T	Print current time and date i.e. “12:11:16 16 FEB 1994”.

For example, to print the two-line footing of this manual the following modifier is used:

FOOTING “‘LLC’Copyright 1998 Beauchamp Computer Services Limited ‘LC’- ‘PN’ -”

 

FOR (throwaway connective)

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

G-correlative (group extraction)

The G-correlative is used to extract a quantity from a string where any given character forms the field delimiter. It takes the form:

Gabc

a	An optional number of fields to skip. 0 is assumed if none specified.
b	The character (excluding the minus sign, attribute, value, and subvalue marks) which forms the field delimiter.
c	The number of fields to be extracted (as delimited by b).

For example consider the string “USERNAME*9544*32456” which holds the username, the internal date, and the internal time, separated by asterisks. G-correlatives can do the following:

G*1	outputs USERNAME (extract all characters from the beginning of the string up to the first asterisk).
G1*1	outputs 9544 (extract all characters between the first and second asterisks).
G2*1	outputs 32456 (extract all characters between the second and third asterisks).
G1*2	outputs 9544*32456 (extract all characters between the second and fourth asterisks) etc.

GE / >= (greater-than-or-equal-to operator)

In selection criteria “GE” or “>=” is used to apply an inclusive lower limit to a range. Both the following statements produce the same result.

>COUNT EMPLOYEES WITH PAY GE “1000.00”

>COUNT EMPLOYEES WITH PAY >= “1000.00”

 

GET-LIST (verb)

>GET-LIST filename listname

Retrieves a list from the filename (defaults to the pointer-file if no filename specified) previously created by EDIT-LIST, COPY-LIST, or SAVE-LIST.

GRAND-TOTAL (modifier)

This modifier allows text to be printed on the final total-line of any listing which uses the “TOTAL” modifier. It takes the following format:

GRAND-TOTAL “text ‘options’ text”

Any text is printed from the beginning of the left-hand side of the page and replaces the “***” normally printed for a total. The following options are available:

U	Underlines all totalled fields with “=”.
L	Suppresses the blank after the last detail-line.
P	Prints the final total on a new page (e.g. allows meaningless final totals to be discarded).

 

GT / > (greater-than operator)

In selection criteria “GT” or “>” is used to apply an exclusive lower limit to a range. Both the following statements produce the same result.

COUNT EMPLOYEES WITH PAY GT “999.99”
COUNT EMPLOYEES WITH PAY > “999.99”

 

HASH-TEST (verb)

>HASH-TEST filename itemlist criteria (options

This verb is used when assessing existing files for resizing. In effect it allows you to see how the file would be organized if it was recreated using the test file-size. If no items are specified all items in the file are used.

 

HDR-SUPP (modifier)

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

 

HEADING (modifier)

Text line(s) to be printed at the top of each page of the listing whether to screen or printer. Literal text in enclosed in double quotes. Special options are enclosed in single quotes and those options are as follows:

 

B	Used with the “B” option of BREAK-ON to insert the break quantity in the heading.
C	Centres the heading line.
D	Prints current, external date ie “16 FEB 1994”.
Fn	Prints current data filename. Optionally a number “n” can be specified so that the filename is printed left-justified in “n” spaces.
L	Linefeed/carriage-return (more than one heading line).
PN	Print page number.
P	Print page number right-justified in 4 spaces.
Pn	Print page number left-justified in “n” spaces.
T	Print current time and date i.e. “12:11:16 16 FEB 1994”.

 

For example, to print the heading of this manual the following modifier is used:

HEADING “‘C’ACCESS MANUAL’LLL’”

 

ID-SUPP (modifier)

A wordier synonym for the “I” option ie suppresses the default printing of the item-ids.

 

IF (connective)

Specifies that the data must be present to be included on the listing. A synonym for the WITH 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 IF SURNAME = “SMITH” SURNAME PAY

>LIST EMPLOYEES WITH SURNAME = “SMITH” SURNAME PAY

IN (throwaway connective)

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

ISTAT (verb)

>ISTAT filename itemlist criteria (options

This verb prints information illustrating the current organization of the file in the form of a histogram followed by a variety of statistics. It can be used to determine if a file needs resizing (is too small or too large for the items it currently contains) because it gives the number of bytes per group.

If the number of bytes per group is greater than the frame size i.e. each group is bigger than a frame then the file is too small.

If the number of bytes per group is less than the average item size (i.e. less than one item per group) then the file is (probably) too big.

Most modern versions of PICK have built-in resizing utilities which have rendered the ISTAT verb redundant other than for enquiries on individual files.

ITEMS (throwaway connective)

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

L-correlative (length function)

The L-correlative allows restrictions to be placed on the length of the data listed. It takes the form of:

Ln,m

n	The exact number of characters a datum must match to be listed.
m	This optional parameter is an upper length limit and if present turns “n” into the lower length limit. Thus if the length of a datum falls within this inclusive range then the datum is listed.

LE / <= (less-than-or-equal-to operator)

In selection criteria “LE” or “<=” is used to apply an inclusive upper limit to a range. Both the following statements produce the same result.

>COUNT EMPLOYEES WITH PAY LE “1000.00”

>COUNT EMPLOYEES WITH PAY <= “1000.00”

LIST (verb)

>LIST filename itemlist modifiers outputlist (options

This verb lists items in the order they are stored on file and prints the data specified in the outputlist. No sorting takes place i.e. the “BY” modifier etc cannot be used. Thus LIST is always quicker than SORT.

That being said, some forms of LIST are quicker than others. If all the items to be listed are known then the following statement:

>LIST EMPLOYEES “0001” “0002” “0003” “0004” SURNAME PAY

will list the surname and pay of the first four employees very much more quickly than either:

>LIST EMPLOYEES = “0001” OR = “0002” OR = “0003” OR = “0004” SURNAME PAY

or

>LIST EMPLOYEES GE “0001” AND LE “0004” SURNAME PAY

This because the first statement tells ACCESS precisely which items to use. The second and third statements tell ACCESS to search through all employees for the required items.

LIST-ITEM (verb)

>LIST-ITEM filename itemlist criteria modifiers (options

 

This copies all items in a file in the order they are stored on file, or just the specified items, to the screen or printer.

 

LIST-LABEL (verb)

>LIST-LABEL filename itemlist criteria modifiers outputlist (options

 

This command prints formatted labels on to self-adhesive label stationery in the order that the items are stored in the file. 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:

>LIST-LABEL CUSTOMERS NAME ADDR1 ADDR2 ADDR3 ADDR4 POSTCODE (IC ?3,6,3,0,40,1

LPTR (modifier)

A wordier synonym for the “P” option i.e. directs all output to the currently assigned formqueue (see TCL’s SP-ASSIGN verb).

LT / < (less-than operator)

In selection criteria “LT” or “<“ are used to apply an exclusive upper limit to a range. Both the following statements produce the same result.

 

>COUNT EMPLOYEES WITH PAY LT “1000.00”

>COUNT EMPLOYEES WITH PAY < “1000.00”

 

The MV Basics guide to the Access Query Language will continue in the Spring 1999 edition of MultiValue News

MV Basics: The Access Query Language - Part 4

Last Updated: 20 July 1999

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