HP 3000 Manuals HP 3000 Manuals
Appendix C View Table Syntax
This appendix provides the Backus-Naur Form of the syntax for view
tables. Braces({}) around an item mean that the item is optional. The |
specifies alternate items.
table_def --> item_clause using_clause
{where_clause} {sort_clause}
item_clause --> * | itemexp_list
itemexp_list --> item_expression | itemexp_list, item_expression
item_expression --> item |
item_name expression |
DEFAULT item IS expression |
aggfunction (expression)
Semantic note: The expression following IS must evaluate to a constant.
using_clause --> table_exp
table_exp --> table_term |
table_exp, table_term
table_term --> table_factor |
table_term joinsymbol table_factor |
table_term <item_exps> joinop table_factor<item_exps>
joinop --> JOIN | LEFTJOIN | LJ
item_exps --> expression{expression{,expression{,expression}}
table_factor --> (table_exp) |
identifier
where_clause --> expression
expression --> logical_term |
expression OR logical_term
logical_term --> logical_factor |
logical_term AND logical_factor
logical_factor --> {NOT}logical_primary
logical_primary --> arith_exp | arith_exp relop arith_exp |
arith_exp matchop constant
matchop --> MATCH | LIKE
MATCH is a pattern match operator. The first operand of the MATCH
operator is compared to the second operand (a string). The result is
TRUE if the first operand matches the pattern, FALSE otherwise.
The following special wildcard characters can be used to form a pattern:
? -- any character is accepted
@ -- any string is accepted
For example,
x MATCH 'P?S@'
is TRUE if x starts with P and the third character is an S.
LIKE is a pattern match operator that is similar to MATCH. However, the
wildcard characters are different.
_ -- any character is accepted
% -- any string is accepted
arith_exp --> unary |
arith_exp addop unary
unary --> {sign} term
term --> factor |
term mulop factor
factor --> primary |
factor ** primary
primary --> constant |
(expression) |
item |
item[expression] |
LOG10(item{[expression]}) |
LOG10 can be applied to simple or array items. If the operand is an
array item, the function is applied to each element in the array. For
example, suppose X is an array item with values x1,x2,...,xN. LOG10(X)
would then produce an array with values LOG10(x1), LOG10(x2),...,
LOG10(xN).
row_function(expression) |
case_expression |
SUB(item, pos, dtype{,decimals{,convtype}}) |
aggfunction (expression)
Semantic note: An itemexp_list cannot have a mixture of expressions
containing both aggfunction and item.
aggfunction --> AVG | SUM | MIN | MAX | COUNT |
SUB is used in the Item Clause to define, as a new item, a substring
extracted from an existing item of type character. (The parameter syntax
is further delineated below.)
The first three parameters are required: item names the original item,
whose data type is always character. =pos gives the starting byte
position for the extraction (leftmost byte is 1). type identifies the
data type and the length of the substring to be extracted.
The last two parameters are optional: decimals is valid only for packed
and zoned data types and gives the number of places to the right of the
decimal point (zero is the default). convtype too is valid only for
packed and zoned data types and indicates how the substring is to be
converted, either C for conversion to a character string (the default for
zoned) or R for conversion to a real (the default for packed).
pos --> constant
dtype --> len | I1 | I2 | I3 | I4 | R2 | R4 | L
| C len | P len | Z len | N len
There is no blank between C, P, Z, N, and len.
len --> constant
decimals --> constant
convtype --> C | R
case_expression -->
CASE condition:expression,
.
.
condition:expression,
ELSE expression
END
where conditions are expressions of type logical. Conditions are
evaluated in order. The expression following the first TRUE condition
becomes the value of the case_expression. If all conditions are FALSE,
the expression following ELSE becomes the value of the case_expression.
For example:
CASE x<10:'A', x<20:'B', ELSE 'C' END
returns A if x<10, B if 10<x<20, C otherwise.
constant --> integer_constant |
real_constant |
character_constant |
A character string enclosed by single
or double quotes. Embedded quotes are
specified by two quotes ('' or"").
logical_constant |
A logical value T{RUE} or F{ALSE}
In the following definition of integer_constant and real_constant, *
means zero or more repetitions, and + means one or more repetitions of
the preceding class. Thus, digit+ means one or more digits.
integer_const --> digit+
integer_const must be in the range
-2147483648 to 2147483647
real_const --> digit+{fractionalpart}{exponent}
fractionalpart --> digit+
exponent --> exp {-|+}digit+
exp --> E|L
digit --> 0|1|2|3|4|5|6|7|8|9
real_const must be in the range
-1.157920892373162L+77 to -8.636168555094445L-78,
0.0
8.636168555094445L-78 to 1.157920892373162L+77
item --> item_name |
table_name.item_name
table_name --> identifier
item_name --> identifier
identifier --> {!}alpha alphanumeric*
alpha a-z|A-Z
alphanumeric a-z|A-Z|0-9|+|-|*|/|?|'|#|%|&|@
The following reserved words cannot be used as table names and item names
in a view table definition unless they are prefixed by a ! (exclamation
point): AND, CASE, DEFAULT, DIV, ELSE, END, F, FALSE, IS, JOIN,
LEFTJOIN, LIKE, LJ, MATCH, MOD, NOT, OR, T, and TRUE.
relop --> =| <> | < | < | > | > =
addop --> + | -
Besides being used to add quantities, the + operator can also be used to
concatenate character strings to define new items in the Item Clause.
Any number of literal strings (enclosed in single quotes) and items of
type character can be combined, up to a maximum of 255 bytes.
sign --> + | -
mulop --> * | / | DIV | MOD
Operators are given the following priority:
**
* / DIV MOD
unary + unary -
+ -
< < > >
MATCH
NOT
AND
OR
Operators are evaluated from left to right.
row_function --> ROWAVG(item) |
returns the average of the element
values of item
ROWSTD(item) |
returns the standard deviation of
the element values of item
ROWSUM(item) |
returns the sum of the element
values of item
ROWMIN(item) |
returns the minimum of the
element values of item
ROWMAX(item) |
returns the maximum of the
element values of item
ROWCOUNT(item) |
returns the number of elements
in item
Row functions can only be applied to compound data items.
sort_clause --> sort_item | sort_clause , sort_item
sort-item --> item_name {ASC|DESC|A|D}
A maximum of l6 items are allows in a sort_clause.