|
Autumn 1998
|
AN(GOODS)*(IF N(VAT.RATE)="0" THEN "0" ELSE "175")
Also, to compute the absolute value for the contents of attribute 13:
AIF 13<"0" THEN 13*"-1" ELSE 13
When using conditional expressions all parts of the expression (i.e. both the THEN and the ELSE clauses) must be specified. See example 4 of the F-correlative section for an example of nested conditional expressions.
Functions: The following three functions are available:
R(expression,expression) - The remainder function returns the remainder when the result of the first expression is divided by the result of the second expression. For example, to get the remainder of dividing the contents of attribute 5 by 2:
R(5,"2")
S(expression) - The summation function adds up all the values in the attribute number which is the result of the expression. For example, to add up all the values in attribute 9:
S(9)
[expression,expression] - The substring function returns a string of characters beginning at the result of the first expression for the length of the result of the second expression. For example, to return the first 10 characters of attribute 3:
3["1","10"]
Other Correlatives: Any other PICK correlative can be used with
A-correlatives et vv when each correlative is a separate value in attribute
8.
>LIST EMPLOYEES WITH SURNAME = "SMITH" AND WITH FORENAME
= "JOHN"
B;subroutinename
or
B:subroutinename
The subroutine must contain the following three lines:
001 SUBROUTINE subroutinename
002 INCLUDE BASLIB* ACCESS.COMMON
003 RETURN CURRENT$
* - BASICLIB to ADDS users.
The processing code is inserted between lines two and three. The ACCESS
common block contains the following variables:
| DICT$ | The file variable of the current dictionary file. |
| FILE$ | The file variable of the current data file. |
| FILENAME$ | The name of the current data file. |
| CURRENT$ | The current datum. If the "B;" form is used then the datum is as processed by all previous ACCESS functions. If the "B:" form is used then the datum contains the entire contents of the current attribute (i.e. all values and subvalues). |
| ID$ | The current item-id from the data file. |
| ITEM$ | The current item from the data file. |
| NI$ | The item count (i.e. the sequential number of the current item from the data file). |
| ND$ | The detail-line count. |
| NB$ | The break level. -1 Indicates ACCESS is at the sorting stage of processing (not yet outputting). 0 Indicates a detail-line. 127 Indicates a grand-total-line. Any other value is the break level. |
| NA$ | The current attribute number in the data file. |
| NV$ | The current value number in the data file. |
| NS$ | The current subvalue number in the data file. |
| MAX$ | The column width as defined in attribute ten of the A-pointer which contains the B-correlative. |
See example 2, of the F-correlative section, for an example of a B-correlative and its subroutine.
>SORT EMPLOYEES BY DEPARTMENT BREAK-ON DEPARTMENT TOTAL PAY
Thus all employees will be listed by department and every time the department changes a subtotal of the total pay for that department will be printed.
The full format of the break-on modifier is:
>BREAK-ON word "text 'options' text"
The text within the double quotes is optional and replaces the three asterisks
for every break-line (subtotal). Options specified within single quotes
are:
| B | Outputs the value at which the break occurs in either the header or the footer. Use in conjunction with the "B" option of headers/footers. |
| D | Suppresses the break-line if only one detail-line has been output since the previous break-line. |
| L | Suppresses the blank-line between the last detail-line and the break-line. |
| N | Resets the current page number back to one. |
| P | Forces a new page after the break-line. |
| R | Supposedly forces all detail-lines on to the same page by suppressing the page-break until the next break-line. |
| U | Underlines all of the specified "TOTAL" fields. |
| V | Prints the datum at which the break occurred in the break-line. |
For example, to print the text "Department" followed by the actual department code within the break-line and then begin the next department on a new page:
>SORT EMPLOYEES BY DEPARTMENT BREAK-ON DEPARTMENT "Department
'VP'" TOTAL PAY
N.B. To completely suppress the break-column within a listing, set the width (attribute 10 in the A-pointer) to zero AND the column header (attribute 3) to "". This method should work on all versions of PICK!
>SORT EMPLOYEES BY DEPARTMENT BY ID
>SORT FILENAME BY-DSND SIZE SIZE
>SORT CUSTOMERS BY-EXP INVOICE.NUMBER GE "1000" AND
LE "2000" INVOICE.NUMBER ID-SUPP
Note that if your data is not normalized, e.g. employees' pay components and amounts are stored as multivalues on each employee, then the "BY-EXP" modifier will have to be used, with A-pointers defined as controlling/dependent attributes, to replace the "WITH" modifier in selection criteria e.g.:
>SORT PAY BY-EXP COMP = "10" OR = "90" BY-EXP
AMT >= "1000.00" AND <= "1500.00" BREAK-ON COMP
TOTAL AMT
This statement will print all employees paid between 1000.00 and 1500.00 through either components 10 or 90, sorted by component, the amount per component per employee, with a subtotal per component. COMP is the controlling attribute for the dependent attribute AMT. See also controlling and dependent attributes.
>SORT CUSTOMERS BY-EXP-DSND INVOICE.DATE GE "01/01/94"
AND LE "31/03/94" INVOICE.DATE
C{x}element
where x is a single character to be inserted between each attribute. This can be any character other than the attribute, value and subvalue marks. The semicolon ";" means no separation character. The element can be either an attribute number or a literal string of text enclosed in single or double quotes. If an asterisk is used as the element then the result of any previous correlatives is used.
For example, if the customer file holds the customer's name in attribute 2, their address in attributes 3, 4, 5, and 6, and the postcode in attribute 7, then to build up the complete address as one string, each line separated by a space use:
C2 3 4 5 6 7
Similarly to build up the complete address, each line separated by a comma:
C2,3,4,5,6,7
To build up the complete address line, each line separated by a comma and a space however, you must use:
C2;", ";3;", ";4;", ";5;", ";",
";6;", ";7
To build up the complete address line as a single, multivalued attribute when the address lines are stored in separate attributes use:
C2;"]";3;"]";4;"]";5;"]";6;"]";7
where ] is a value mark, CHAR(253), i.e. <CTRL ]>.
Generates a hexadecimal (base 16) check-sum for: all items in a file; or specified items; or specified attributes in specified items (should you ever need to).
Ca;b;c;...;z
where a, b, c, etc are the numbers of the dependent attributes.
When criteria are applied to a controlling attribute A-pointer in an ACCESS statement, the only values printed are from the associated values in the dependent attribute(s) (specified in the outputlist) if the value in the controlling attribute meets the criteria specified. Thus controlling and dependent attributes are use in conjunction with the BY-EXP modifier to interrogate non-normalised data (data stored in multivalues within a single item).
Allows lists produced by the SELECT/SSELECT and SAVE-LIST or EDIT-LIST
verbs to be either copied within the pointer-file or copied to other files.
The various options available are:
| D | Delete source lists after copying. |
| N | Prevents pausing at the bottom of every full screen. |
| O | Overwrites existing target lists. |
| P | Copies lists to the printer. |
| T | Copies lists to the terminal's screen. |
| X | Outputs lists in hexadecimal format. |
Counts the number of items within the file which meet any criteria specified. The only output is the number of items.
An internal date converted on output gives the external date. The format of the D-conversion is:
Dnxym
| n | A single digit in the range 0 to 4 which specifies the number of digits to use in the year. If not specified the default is 4. | |
| x and y | Any single character followed by a single digit (0-9) which specify the delimiter and occurrence for a group extraction of the internal date. | |
| m | Any single non-numeric, non-alphabetic character
used as the data delimiter. Alphabetic characters form the following subcodes: |
|
| D | Day of month | |
| I | Internal date (i.e. convert stored external date). | |
| J | Day of year (i.e. from the first of January). | |
| M | Numeric month (1-12). | |
| MA | Alphabetic month (JANUARY, FEBRUARY etc). | |
| Q | Numeric quarter (1-4) | |
| W | Numeric weekday (Monday=1 etc). | |
| WA | Alphabetic weekday (MONDAY, TUESDAY etc). | |
| Y | Numeric year. See "n" above for the digits in year. | |
N.B. Be careful when using two digit year numbers since 30-99 are input
and stored as 1930-1999 but 00-29 are input and stored as 2000-2029. For
example, 15.02.94 is internal day 9543. This internal day can be output
in the following formats:
| D-conversion | External Date |
| D | 15 FEB 1994 |
| D/ | 15/02/1994 |
| D- | 15-02-1994 |
| D0 | 15 FEB |
| D0/ | 15/02 |
| D2 | 15 FEB 94 |
| D2/ | 15/02/94 |
| DY | 1994 (4 digit year) |
| D2Y | 94 (2 digit year) |
| DQ | 1 (quarter) |
| DD | 15 (day of month) |
| DM | 2 (month) |
| DMA | FEBRUARY |
| DJ | 46 (day of year) |
| DW | 2 (day of the week) |
| DWA | TUESDAY |
Note: D-conversions are so called because they should be used in attribute 7 of the A-pointer (word). This has the advantage that in ACCESS selections, when specifying a range of dates, the actual format used for the external dates in the statement is independent of the format of the D-conversion used to output the date.
For example, consider an A-pointer "DATE" which uses the conversion "D2/" (i.e. output 15/02/94). Then all of the following statements will produce the same result:
>COUNT CUSTOMERS WITH DATE GE "1 JAN 1994"
>COUNT CUSTOMERS WITH DATE GE "1/1/94"
If "D2/" was used as a correlative (in attribute 8 of the A-pointer) then the format of the external date in the ACCESS statement must correspond precisely with the output format used. Thus only the statement:
>COUNT CUSTOMERS WITH DATE GE "01/01/94"
will produce the correct result.
Also note that in all cases, using invalid external dates such as 31/02/1994 in ACCESS statements will produce erratic results.
>SELECT ORDERS WITH YEAR = "1994"
it is necessary to use the D-correlative DY in attribute 8 of the A-pointer YEAR and not a D-conversion in attribute 7.
Deletes lists from the pointer-file previously created by COPY-LIST, EDIT-LIST, or SELECT/SSELECT and SAVE-LIST.
Da
where a is the number of the controlling attribute.
Thus when criteria are applied to a controlling attribute A-pointer in an ACCESS statement, the only values printed are from the associated values in the dependent attribute(s) (specified in the outputlist) if the value in the controlling attribute meets the criteria specified.
>SORT-ITEM DICT CUSTOMERS WITH *A1 = "A" OR WITH *A1
= "S" OR WITH *A1 = "X"
Used to invoke the line editor to either create a list in the pointer-file, or to amend such a list created by COPY-LIST, or SELECT/SSELECT and SAVE-LIST.
>COUNT EMPLOYEES WITH SURNAME EQ "BLOGGS"
>COUNT EMPLOYEES WITH SURNAME = "BLOGGS"
>COUNT EMPLOYEES WITH SURNAME "BLOGGS"
Fexpression;expression etc
F-correlatives use a seven position stack where position 1 holds the most recent addition to the stack. When a quantity is added to the stack it occupies position one, and the seventh quantity (at the bottom of stack) is lost.
F-correlative expressions take the form of any number of operators in Reverse Polish Notation separated by semicolons. When an operand (attribute number, number, or string) is encountered it is put into position 1. When an operator is encountered that operation is carried out on the contents of the first (and second if applicable to the operator) stack positions. The result of the operation is then put into position 1. When the entire F-correlative has been processed the quantity printed is that in stack position 1.
Operands
| n | An attribute number. |
| Cn | where n is any number. |
| D | to use the internal system date (days from 31.12.67). |
| T | to use the internal system time (seconds from midnight). |
Special Operands
| NI | Item count. |
| ND | Detail line count. |
| NV | Value count. |
| NS | Subvalue count. |
| NB | Break-level count. |
| LPV | Loads previous value (result of the last correlative) on to the stack. |
Operators
| + | Adds stack2 to stack1. |
| - | Subtracts stack2 from stack1. |
| * | Multiplies stack1 by stack2. |
| / | Divides stack1 by stack2. |
| : | Stack1 becomes the result of concatenating stack1 on to the end of stack2. |
| [ ] | Extracts the string from stack3 beginning at stack2 for the length of stack1. |
| = | Returns boolean true if stack1 equals stack2. |
| # | Returns boolean true of stack1 does not equal stack2. |
| > | Returns boolean true of stack1 is greater than stack2. |
| < | Returns boolean true of stack1 is less than stack2. |
| ] | Returns boolean true of stack1 is greater than or equal to stack2. |
| [ | Returns boolean true if stack1 is less than or equal to stack2. |
| S | Sums all multivalues in stack1. |
| _ | Swaps stack1 and stack2. |
| P | Duplicates stack1. |
| Any other ACCESS conversion/correlative surrounded by parentheses e.g. "(D2/)". |
Example 1
ACCESS only does integer mathematics i.e. it tends to ignore pence in money amounts if the amount is stored with the decimal point e.g. 199.99. This problem can be circumvented using an F-correlative which converts the decimal to an integer as follows:
BALANCE
001 A
002 0
003 Balance
004
005
006
007 MR2
008 Fn(G0.1);n;".0";:;(G1.1);(ML%2);:
009 R
010 15
where n is the attribute number containing the amount to be converted. To convert decimals stored in a particular value, replace every n with a file-translation correlative (see the T-correlative section) e.g.:
0;(Tfilename;Xvmc;;amc)
where amc and vmc specify the attribute and value position of the decimal in filename.
MV Basics: The Access Query Language - Part 3
Last Updated: 31 October 1998