HP 3000 Manuals HP 3000 Manuals
More Advanced Ways to Define View Tables
View tables are created in more advanced ways by taking full advantage of
the view table syntax. (The complete syntax definition appears in
Appendix C.) Some examples of what you can do are given below.
When a PC user tries to access a view table, Access Server constructs the
view table (from the view table definition) as follows:
* An intermediate table is produced using the information in the
Using Clause (a relational "join" operation).
* Duplicate columns (from the first step) and columns not selected
in the Item Clause are eliminated (a relational "project"
operation).
* Excluded records, according to the Where Clause, are eliminated (a
relational "select" operation).
* The remaining records are sorted according to the Sort Clause.
What follows is a more complete discussion of each of the four clauses
and some examples of how they can be used to good advantage.
Item Clause
In general, there are three ways to represent an item in the Item Clause:
* If the item name occurs only once in the tables being combined and
you don't want to rename the item, then the item name alone can be
used (as in TABLE3 above).
* If the item name occurs in more than one of the tables being
combined and you don't want to rename the item, then you must use
the table name, a period, and the item name to specify which table
the item is to be drawn from. (If the table name is not
specified, Access Server will use the item from the first table
specified in the Using Clause.)
If you want to include both items, one of them must be renamed.
In TABLE2 above, for example, our items could have been specified
as:
EMPNUM,EMPLOYEE.NAME,DEPT=DEPARTMENT.NAME
If we had done this, the second column would have been called
NAME, the third, DEPT. Because we used both items called NAME, one
of them had to be renamed.
* If you want to rename the item or arithmetically manipulate it in
some way, then you use the item name followed by an equal sign and
an expression. (One form the expression can take is illustrated
in TABLE1 and TABLE2 above, where the expression qualifies a
non-unique item name.)
The expression to the right of the equal sign can perform
arithmetic or logical manipulations on the item before it goes
into the view table. You might, for example, convert a monthly
salary MONTHPAY into a yearly salary YEARPAY by defining the item
thus:
YEARPAY = MONTHPAY * 12
Or you might combine four quarterly quotas into one quota for the
year, like this:
YEARQUOTA = Q1QUOTA + Q2QUOTA + Q3QUOTA + Q4QUOTA
The characters + - / * can be used in the names of items. If you
use any of them in an arithmetic expression, you must separate
them from their operands with at least one blank.
Four features of the syntax are particularly useful in creating new
items: the SUB function, item concatenation, the CASE statement, and the
ROW functions. Below are discussed some of the ways to define items
using these features.
SUB Function.
The SUB function lets you define a new item by extracting a substring
from an item of any type and giving it a new item name. The new item can
retain its type, or it can be redefined to certain other types.
* Rules for the SUB function in brief. Detailed specification of
the syntax for the SUB function is given in Appendix C. The format
used is:
NEWITEM = SUB(item,pos,dtype,decimals,convtype)
The first three parameters are required: item is the name of the
original item, which can be of any type. pos gives the starting
byte position for the extraction (leftmost byte is 1). type
identifies the data type of the new item, and the length of the
item you want to extract. (For the full range of values type can
take on, see Appendix C.)
The last two parameters are optional: decimals is valid only for
packed and zoned type and gives the number of places to the right
of the decimal point (zero is the default). convtype indicates
data conversion to real, character, or integer. (See the table on
page B-9 for details.)
* The new item can be an item of type character. Suppose, for
example, that the IMAGE table GENL-LEDGER includes the item
ACCTDEPT, which consists of six characters, three for an account
number followed by three for a department number. You could
create the view table GENL-LEDGER-V, in which ACCTDEPT is split
into two new three-character items called ACCTNUM and DEPTNUM, and
the other columns from GENL-LEDGER are used unchanged.
Breaking out substrings in this way not only makes for more
flexible reports, but also allows for greater refinement in record
selection in the Where Clause. If, for example, you define the
item DEPTNUM in the Item Clause of GENL-LEDGER-V like this
DEPTNUM = SUB(ACCTDEPT,4,3)
then the new item DEPTNUM is created by extracting three bytes
from ACCTDEPT starting at the fourth byte. You can then create
another view table that names GENL-LEDGER-V in its Using Clause
and specifies, for example,
DEPTNUM = "525"
in its Where Clause.
The SUB function allows for JOINs that otherwise would be
impossible to perform. Suppose, for example, you have another
table called DEPARTMENT, which includes a DEPTNUM item. By
defining another view table that JOINs the two tables
GENL-LEDGER-V and DEPARTMENT on their DEPTNUM fields, you can draw
information together from both tables in a way you could not have
done with the original GENL-LEDGER table.
NOTE If a zoned item really contains a character field, when the item is
converted to character, it will be converted and displayed with
extra blanks in front of it to allow for the sign digit. For
example, if a DATE is held in a Z6 field, it will be displayed on
the PC with a leading blank.
If you want to eliminate the blank in such a case, use the SUB
function in a view table definition to tell Access Server to
treated the zoned type like a character type. For example, the
expression
DATE = SUB(Z6-DATE,1,C6)
eliminates the extra blanks.
* The new item can be an item of type integer, real, packed, or
zoned, as well as an item of type character. Some application
software stores numeric data within character fields. The SUB
function provides for extraction of this data and assignment of
the correct data type to the newly defined item.
The third parameter, type, can be used to identify not only the
length but also the data type of the substring to be extracted.
(Because the default data type is "character," the expression
SUB(ACCTDEPT,4,C3) is equivalent to SUB(ACCTDEPT,4,3) where
ACCTDEPT is a character field.)
datatype can take on values of I1,I2, and I4 (for integers), R2
and R4 (for real), L (for logicals), C with a length (for
character), P with a length (for packed), N with a length (for
numeric), and Z with a length (for zoned). Note that length is in
words for I and R, in bytes for C, N, and Z, and in half-bytes
(nibbles) for P. The length for packed numbers must be even, for
example, P2, P4, P6....P28.
Here's how you would extract, for example, a one-word integer from
the item BIG-BUF to create the integer item NEW- INT:
NEW-INT = SUB(BIG-BUF,33,I1)
The optional fourth and fifth parameters are valid with packed,
zoned, or integer data. Decimals and conversion type are optional
for integers. Conversion type is required for N type (but a
decimal point is not valid because the data could have floating
point decimal placed). The decimal point is implicit in N types.
The fourth parameter specifies the number of places you want to
the right of the decimal point (the default is zero). The fifth
parameter indicates how the item, if it is packed, zoned, or N
type, is to be converted; either C for conversion to a character
string (the default for zoned), R for conversion to a real (the
default for packed), or I for conversion to an integer.
If you wanted to extract eight digits of packed data (starting at
byte 20 of character item BIG-BUF) and automatically convert the
new item to type real with zero places to the right of the decimal
point, you would define new item NEW-REAL this way:
NEW-REAL = SUB(BIG-BUF,20,P8)
If you wanted to extract eight digits of zoned data (starting at
byte 5 of character item BIG-BUF) and automatically convert the
new item to type character, with two places to the right of the
decimal point, you would define new item NEW-CHAR this way:
NEW-CHAR = SUB(BIG-BUF,5,Z8,2)
And if you wanted to extract and convert eight digits of packed
data (starting at byte 2 of character item BIG-BUF) to an item of
type character, with zero places to the right of the decimal
point, you would define new item NEW-PACKED-CHAR this way:
NEW-PACKED-CHAR = SUB(BIG-BUF,2,P8,0,C)
The following types can be converted:
Type Default Conversion Specify
Conversion Options Decimal?
P (packed) R R, C Y
Z (zoned) C R, C Y
I1 (integer) No convert R, C Y
I2 (integer) No convert R, C Y
I4 (integer) R R, C Y
N (numeric) R R, I N
All types are allowed in the SUB function.
Here are all the possible ways the data types can be specified.
Type Length Decimal Conversion Deault
Options Conversion
C 1 to 256
I 1 0 to 5 R,C None
I 2 0 to 10 R,C None
I 4 0 to 18 R,C R
L blank or 1
R 2
R 4
P 2 to 28 0 to Length R,C R
Z 1 to 256 0 to Length R,C C
N 1 to 26 R,I R
The N type display numeric format lets you define a field that is a
free-format alphanumeric display field. The field can be converted to an
integer (I2) or a real (R4). Here is an example of how the N type can be
specified in a SUB function:
If an IMAGE character field of length 12 contains ASCII numeric data, it
could be defined in a view table as an N type by specifying the
following:
DISPLAY-NUMERIC-R4 = SUB(CHAR,1,N12)
This defines an N type 12 bytes long that would be converted by default
to R4. If a conversion to I2 was desired, the specification would look
like:
DISPLAY-NUMERIC-I2 = SUB(CHAR,1,N12,,I)
N types converted to I2 are rounded to the nearest whole number.
The format of the field is extremely flexible. Access Server ignores
spaces and non-numerals. Some examples are:
Field Value Conversion Numeric Value
$ -34.54 ******* R4 -34.54
**** 7835.51 I2 7836
12083 99999 I2 1,208,399,999
- 45.87 +++ R4 -45.87
+ 1234.5678.1234 R4 1234.5678
$8,324,688.56 R4 8,324,688.56
1 345 856 I2 134,586
$45,863.10 R4 45,863.10
The possibilities are endless, but the basic rules you use to convert N
types to numeric (I2 or R4) are:
* Scan for the first numeric character (0-9) or sign (+, -),
ignoring all other characters.
* Scan for numeric characters and 1 decimal point (ignoring imbedded
blanks).
* Round to the nearest whole number if converting to I2.
* If converting to I2 and the number is larger than will fit in an
I2, the value of the I2 is set to the largest I2 number.
Item Concatenation.
Item concatenation lets you use the + operator to concatenate character
strings and thereby define new items of type character in the Item
Clause. You can use the plus sign (+) to concatenate any combination of
literal strings and items of type character, up to 255 bytes total.
For example, if DIVISION and DEPARTMENT are items of type character, you
would create the new item DIVDEPT, which concatenates the two items, like
this:
DIVDEPT = DIVISION + DEPARTMENT
If you wanted to hyphenate the two strings in your new item, that could
be accomplished like this:
DIVDEPT = DIVISION + '-' + DEPARTMENT
As with the SUB function, concatenate allows for JOINs that would
otherwise be impossible.
CASE Statement.
The CASE statement can be used in a variety of ways to define new items.
One way allows you to expand short character data items into longer, more
intelligible character strings. For example, suppose STATUS is a
two-character item, where ST means "in stock," BA means "backordered," DC
means "discontinued," and so on. If you create a view table with
FULLSTATUS = CASE STATUS = 'ST': 'In stock', STATUS = 'BA':
'Backordered', STATUS = DC: 'Discontinued', ELSE STATUS END
in the Item Clause, then the new item FULLSTATUS equals the longer string
for the three conditions specified, but retains the two-character value
of STATUS for any other condition.
Expressions that appear to the right of the ":" can also be mathematical
expressions.
The CASE statement can also be used to redefine field values so that they
can be sorted in a different order than with their original names.
The CASE statement can be used much like an IF THEN ELSE statement in a
programming language. For example, to report the appropriate action on
the status of a Service Request, the following CASE statement could be
created:
SR-ACTION = CASE
STATUS = 'MR' : 'Needs to be Reviewed',
STATUS = 'LC' : 'Needs to be Classifed',
STATUS = 'KP' : 'Needs to be Fixed',
STATUS = 'SO' OR STATUS = 'CL' OR STATUS = 'AD' :
'No Action Needed' END
Another example is to determine the appropriate action for different
levels of inventory:
INVENTORY-ACTION = CASE
ON-HAND QUANTITY > 100000 : 'More Then WE Need'
ON-HAND QUANTITY > 50000 : 'Enough'
ON-HAND QUANTITY > 10000 : 'Getting Low'
ON-HAND QUANTITY > 1000 : 'Need to Order' END
The possibilities of using the CASE statement are endless. Be creative.
ROW Functions.
The ROW functions can be used to define new items that are the result of
one of six operations performed on compound data items.
Suppose the four sub-items of the compound data item COM (in IMAGE table
DTABLE) are of type integer. If the Item Clause for VTABLE (a view table
derived from DTABLE) includes, for example,
COMMAX = ROWMAX(COM), COMSUM = ROWSUM(COM),
COMAVG = ROWAVG(COM)
then, for each record, COMMAX equals the largest integer in the array,
COMSUM is the sum of the four integers in the array, and COMAVG (which is
of type real) is the average of the four integers. (Note: The remaining
row functions, ROWSTD, ROWMIN, and ROWCOUNT, are described in Appendix
C.)
Using Clause
The basic examples show the use of one table in the Using Clause (to
derive TABLE3), a simple PRODUCTION operation involving two tables (to
derive TABLE1), and a simple JOIN operation involving two tables (to
derive TABLE2).
As many as 16 tables can be used in this clause, in any combination of
PRODUCTION and JOIN. TABLE2, described at the beginning of this appendix
under "JOIN Operation," was the result of a JOIN on one item (column).
Tables can be joined on as many as four columns. Such a JOIN would take
this format:
T1<ITEM1,ITEM2,ITEM3,ITEM4>
JOIN T2<ITEM5,ITEM6,ITEM7,ITEM8>
In this case, only records that match on all four columns would be
included in the view table. Corresponding columns in a JOIN (ITEM1 and
ITEM5 for example) must be of the same data type for the JOIN operation
to be successful.
A JOIN of three tables on one column might look like this:
(T1<ITEM1> JOIN T2<ITEM2>)<ITEM3> JOIN T3<ITEM4>
The parentheses dictate that T1 and T2 will be joined first. The
parentheses also indicate an implied resulting table from this first
JOIN, so the <ITEM3> identifies a column in that implied table. This
column is used in performing the second JOIN.
The second JOIN, by the way, could have been done on more than one
column:
(T1<ITEM1> JOIN T2<ITEM2>)<ITEM3,ITEM4>
JOIN T3<ITEM5,ITEM6>
Here the first JOIN is done on one column, the second JOIN on two. As
long as you specify the same number of join columns for each of the two
tables involved in a JOIN operation, the join should be successful.
A JOIN of four tables shows the effect of nesting parentheses:
((T1<ITEM1> JOIN T2<ITEM2>)<ITEM1>
JOIN T3<ITEM3>)<ITEM1> JOIN T4<ITEM4>
If you JOIN more than two tables, and two of the tables contain a data
item of the same name that is to be used as a JOIN item for an implied
table, it is important to fully qualify the item name to avoid ambiguity.
Assume, for example, that tables T1 and T2 both contain an item named I3
and that I3 is to be used as a JOIN item on the implied table. Also
assume that the value of I3 to be used for the JOIN is to come from T2,
NOT T1. The Using Clause should be:
(T1<I1> JOIN T2<I2>)<T2.I3> JOIN T3<T4>
Where Clause
The Where Clause is used to narrow down the number of records in a view
table.
Items named in the Where Clause must be items from the table(s) specified
in the Using Clause. You cannot use item names newly defined in the Item
Clause. However, you can use an expression in a Where Clause such as:
SUB(ITEM,2,3)<>"PAP"
where ITEM is an item named in a table contained in the Using Clause.
You can use AND, OR, and NOT functions in any combination, the MATCH
operator, and various arithmetic and logical operators in the Where
Clause. A simple example, shown above (TABLE3), uses the greater-than
(>) and less-than (<) operators, brought together with an AND.
Items can be compared not just against specific values, as in TABLE3, but
also against other items in the record. A view table with a Where Clause
that says
SALES < QUOTA
includes only those records for which the value of the item SALES is less
than the value of the item QUOTA.
You can also use the MATCH operator and the wildcard characters (@ and ?)
to specify which records you want. The first wildcard character (@)
matches strings of any length, while the second (?) matches any single
character. For example,
PRODUCT-NBR MATCH "PAP@"
includes only those records in which the item PRODUCT-NBR begins with the
character string "PAP". (Note: If PRODUCT-NBR is a field containing six
characters, then MATCHing on "PAP???" produces the same result, but
"PAP??" does not.)
To be compatible with SQL, the LIKE operator and the wildcard characters
% and _ are also allowed.
Current Newly Added
MATCH LIKE
@ % For ISO-7, * can be used
instead of @. Since % is
not an ISO-7 character, it
can be used as well as *.
? _ This is an underscore (_),
not a hyphen (-).
Several other examples involving the Where Clause are found in Chapter
14, "Performance Tuning." Chapter 14 also discusses how to structure a
Where Clause to improve performance.
Sort Clause
The Sort Clause is used to specify one or more items upon which you want
the records sorted, once they've been determined by the other three
clauses. (Note: In the Where Clause, you cannot use item names newly
defined in the Item Clause. But in the Sort Clause, only item names that
appear in the Item Clause can be used.)
Up to 16 items can be specified for the sort, with the first item as the
primary sort item, the second as the secondary sort item, and so on.
If only the item name is specified, the type of sort is Ascending. By
appending a blank and A or ASC (for Ascending) or D or DESC (for
Descending) to the item name, you can explicitly choose the type of sort
you want.
For example,
DEPTNUM D, EMP A
sorts by department number in descending order. As a secondary sort,
within each department, employee names are sorted in ascending
(alphabetical) order. Omitting the A after EMP, like this:
DEPTNUM D, EMP
produces the identical sort operation.