NAME
expression – UNIBASE aritmetic expressions
DESCRIPTION
An expression is a calculation of some sort, which is placed in a field type E, T, G, J, or A. To specify an Expression the type of expression is followed by the number of places after the decimal point and then the expression of the value.
As Expression fields aren’t stored they may be placed anywhere in the field list. They may also be removed from the Table Definition at any time. If an Expression depends on another expression, the order in which the Expressions are declared is not important.
Any Field Type may be used in an Expression and UNIBASE will try to make use of it in the context of the Expression. For example if you try to add a text field to a number field, UNIBASE will try to interpret the text as a number. UNIBASE will change days to dates, subtract dates to get days, etc.
UNIBASE expressions are made up of numbers, strings, dates, commands, fields, and operators.
Numbers are as you would expect, eg. 1, 2.5, 1000.423.
Strings are text enclosed in “double quotes”, eg “HELLO”, “015” or ‘single quotes’, eg ‘HELLO’, ‘015’.
Field definitions are surrounded by [square brackets], eg [stock no][date][price].
Dates are indicated by a leading “@” (for DD.MM.CCYY format) or “~” (DD.MM.YY format).
Commands are bash shell script commands surrounded by “`” characters. eg `ls -l | wc -l`.
Operators
Operators maybe arithmetic, comparative, logical or unary (one argument only).
| Operator | Meaning | Notes |
|---|---|---|
| + | Addition | |
| – | Subtraction | |
| * | Multiplication | |
| / | Division | |
| MOD | Modulo | a MOD b works for all values of a and b, but the result is always an integer |
| ^ | Power | Powers do not have to be integral |
| Operator | Meaning | Notes |
|---|---|---|
| & | And | |
| | | Or |
The result of a comparison or logical operation is 0 (false) or 1 (true). In general 0 means false and nonzero means true. The result of a logical and (&) is true if both operands (the things being added) are true, otherwise it is false. The result of a logical or (|) is true if either or both its operands are true, and false if neither is true. The result of comparative and logical operators can be used by the arithmetic operators.
| Operator | Meaning | Notes |
|---|---|---|
| < | Less than | |
| <= | Less than or equal to | |
| >= | Greater than or equal to | |
| > | Greater than | |
| = | Equal to | |
| != | Not equal to | |
| S | Substring | The S operator is the substring operator. Its value is true (1) if its left operand contains its right operand.
Example of Substring Operator “ABCXYZ” S “CXY” is true If [areas [is “R1,R2,C1” then [areas] S “R2” is true (1) while [areas] S “X1” is false (0) |
| Operator | Meaning | Notes |
|---|---|---|
| SQRT | Square root | |
| EXP | Exponential (e) | |
| LOG | Logarithm base e | |
| LOG10 | Logarithm base 10 | |
| SIN | ||
| COS | ||
| TAN | ||
| ASIN | ||
| ACOS | ||
| ATAN | ||
| SINH | ||
| COSH | ||
| TANH | ||
| DAY | Day of week | Returns the day of the week for a date. 0 = Sunday, 6 = Saturday Date must include the century. |
SIN, COS, etc are Unary operators rather than functions. This means that SQRT 4 is 2 and SQRT [mean] is also valid. If a more complex expression is to be operated on then it should be inclosed in ‘(‘,’)’
Example expressions
SQRT ([value] - [mean]) [qty] * [price] ([amount] >= 0) * [amount] ([afield] > 50.7) + ([anotherfield] > 123.87) [date] + 7 > [system.date] DAY [system.century]
The value of comparative and logical operators is 0 or 1.
This property can be used to separate values (ie this replaces the more traditional if-then-else language construction).
eg ([amount]>=0)*[amount], will have the value of [amount], if [amount] is >= 0, or 0 if [amount] is < 0.
Operators have precedence, ie some are more important than others and will therefore be evaluated first.
Because the multiplication operator * takes precedence over >= it is necessary to put (parentheses) around the comparison (just as you would in any mathematical equation).
If two operators have the same precedence they will be evaluated from left to right.
eg 1+2*3 is 7 not 9.
To change the order of evaluation brackets can be used eg. (1+2)*3 is 9
Groups of Operators in Order of Precedence
Unary operators ^ *, /, MOD +, - <, <=, >=, >, =, !=, S &, |
A trick with 0
Often in commercial applications we want to assume that a zero value is really 1.
To do this we can write an expression “[value] + ([value] = 0)”
Set operations
The comparative operators (<, <=, >=, >, =, !=) can also be used on arrays.
If the field definition is:
area, 3*5 PX
“AAA” < [area] is true if AAA is less than all the elements of area ie. [area 1],[area 2],[area 3],[area 4] and [area 5]
Similarly for <=, >=, >, =, and !=
“AAA”=[area] is true if “AAA” is equal to one of the elements of area while “AAA”!=[area] is true if “AAA” is not equal to any of the elements of [area]
Secondary files and comparative operators
When the comparative operators are used on fields from secondary files they will work on the sum of the field (for expressions and numbers).
To get a similar result to the set operations requires a trick.
Define an expression field in the secondary file whose width can accommodate the maximum number of secondary records you are expecting (typically 2 or 3 characters).
The expression should be the comparison required.
eg from an order file, if [ordline.qty] > 10 is the set operation required, then define a field:
ordqty, 2 E0 [qty] > 10
in the record “ordline”. This field will add on for every line where [qty] (quantity) > 10.
From the order file:
[ordline.ordqty] > 0
will be true if any line of ordline have qty >10.
Also useful are [ordline.ordqty] = 0 (no records) and [ordline.ordqty] = [ordline.no of recs] (all records)
Calculation optimisation
With some operators, the result of an operation can be known from the value of one of its operands, eg a&b is always false if a is false.
In this case UNIBASE will not try to evaluate the right operand (even if it is another expression or involves a join).
The operators that take these shortcuts are &,|,*,/
| a&b | Will skip b if a is false |
|---|---|
| a|b | Will skip b if a is true |
| a*b | Will skip b if a is 0 |
| a/b | Will skip b if a is 0 |
Accumulators (A)
Accumulators are expression fields that add their current value to the expression at every record.
They are reset to zero at every subtotal. This gives a running total column.
They are defined in the same way as the E and T types above, but using an A as the field type.
Example of an Accumulator Type Field
balance, 15 A2 [amount]
Julian date (J)
Julian is a type of expression for doing calculations involving dates. The field width should always be 6 or 8 (for JC).
When calculating dates you need to keep in mind the rules for combining dates and days to come up with a date as a result – certain types of expressions will be meaningless if you are not careful – for instance – a date eg. (next Monday) plus another date (next Tuesday) does not make sense, but a date (next Monday) + 1 day does make sense = Tuesday.
NB: UNIBASE will stop you doing something meaningless with dates as it doesn’t try to interpret the result as a date until after the calculation has been completed.
Here are the rules:
| + | A date plus a number of days gives a date. A date plus a date is meaningless |
|---|---|
| – | A date minus a number of days gives a date. A date minus a date gives days |
| * | A date times 0 or 1 gives a date. A date times anything else is meaningless |
| / | A date divided by 1 gives a date. A date divided by anything else is meaningless |
Example of Julian Dates
datequoted, 6 D validfor, 3 N0 dateexpired, 6 J [datequoted] +[validfor]
Graphic (G)
Graphic fields are expressions where the result is represented as ‘*’s. To specify a graph field use “G” followed by the units per star, eg is the field width is 20 and the unites per star is 50 then 400 would be represented by 8 ‘*’s followed by 12 spaces.
Example of Graphic Expression
staffsales, 20 G100 [weeklysales]
Zero Suppression (Z)
Expressions may sometimes be required as ‘zero suppression’ is if the value is 0, nothing (or SPACE to be precise) is printed instead of 0 or 0.00 which would normally be printed. This is particularly helpful when a number must be placed in one of several columns in a report. To make a field zero suppressed the field type (“E”, “T” or “J”) should be immediately followed by a “Z”.
Julian expressions that are zero suppressed will be printed as a blank date ” . . “