Copyright © TIBCO Software Inc. All Rights Reserved
Copyright © TIBCO Software Inc. All Rights Reserved


Chapter 3 Tools : @FORALLA

@FORALLA
Returns the first table occurrence that satisfies the selection criteria. Use if the value of any table parameter or of any selection criterion is 100 or more characters long. (C)
Invocation
CALL @FORALLA(table, parm, selection, ordering)
 
A string specifying the table from which to select. Its syntax is C (fixed-length character string), with a length of 16.
A string specifying the parameter set for the table. Its syntax is V (variable-length character string) with length of 512.
A string specifying the order in which to return the occurrences during selection. Its syntax is V, with a length of 512.
The following sections describe the format of each of the arguments.
Table
The name of the table. For example, to access the table EMPLOYEE, pass ‘EMPLOYEE’ as the value for the table argument.
Parm
The string specified for parm consists of a list of parameter values. Each parameter value consists of:
The string 006CANADA00580002 contains two parameters. The first is 6 characters long with the value CANADA. The second is 5 characters long with the value 80002.
Both the data parameters and the location parameter can be specified in this manner. If the table has no parameters or the parameters are being specified in the selection parameter of @FORALLA, the value of parm can be null (NULL).
Selection
The string specified for selection is a postfix representation of the rules language WHERE clause. It is comprised of one or more terms joined by logical operators. Each term consists of the following:
These elements are described here.
Field Reference
A field reference consists of the following:
A two‑character descriptor consisting of a space followed by an R (' R').
Expression
An expression consists of the following:
A two‑character value syntax descriptor consisting of a space followed by one of the following (for example, ' V') to denote the syntax of the value:
A – syntax RD (raw data): @FORALLA uses, or coerces the value to, syntax V; and then coerces it to syntax RD (see note below)
E – syntax V (variable-length character string): @FORALLA uses, or coerces the value to, syntax V; if the coercion to V fails, it raises an exception
M – a numeric string: @FORALLA uses, or coerces the string to, syntax V; and then coerces it to syntax B (binary), P (packed), or F (floating point); if either coercion fails, it raises an exception
You must specify the length as returned by the LENGTH shareable tool
The relational operators are: =, ¬= (not equal to), >, >=, <, <=, and L (LIKE).
The relational operators must be two characters in length. Prefix a space before an operator that consists of only one character.
The arithmetic operators are: +, -, *, /, **, and ||.
The arithmetic operators must be two characters in length. Prefix with a space an operator that consists of only one character. For unary minus, place a space after the -.
The logical operators are: &, |, and ¬.
The syntax of selection determines the representation of values in the expression. The usual technique for building selection is to use a series of concatenations. Here are some examples (where the local variable VAL contains the value in the expression to be built):
Regardless of selection's and VAL's syntax, @FORALLA coerces the value to syntax V.
Regardless of selection's and VAL's syntax, @FORALLA coerces the value to syntax B, P, or F, whichever is appropriate.
To include a raw-data value in the expression, use descriptor ' A', keeping in mind that a direct concatenation involving an RD value coerces the result to RD. If, as a result, the selection string is of syntax RD, FORALLA coerces it to syntax V prior to any processing, so some data loss is possible (for example, if the selection contains a value of syntax UN). Accordingly, the following is recommended: assuming VAL is of syntax RD, first cast it to syntax V:
Examples of Selections
In these examples, the “ß” represents a mandatory space.
ßRCITYßßßßßßßßßßßßßV0007TORONTOß=
The pattern is the name of the field, followed by the value, followed by the relational operator.
The following represents the selection CITY='TORONTO' AND SALARY > 600:
ßRCITYßßßßßßßßßßßßßV0007TORONTOß=ßRSALARYßßßßßßßßßßßßßM0003600ß>ß&
The name of the field (CITY) is followed by the value (TORONTO) and relational operator (=), and then another sequence of field (SALARY), value (600), relational operator (>), followed by the logical operator (&) that joins the two expressions.
The following represents the selection CITY='TORONTO' & SALARY > 600 | MGR# = 80002:
ßRCITYßßßßßßßßßßßßßV0007TORONTOß=ßRSALARYßßßßßßßßßßßM0003600ß>ß&ßRMGR#ßßßßßßßßßßßßßM000580002ß=ß|
The first and second expressions are the same, and are followed by a third sequence of field (MGR#), value (80002), relational operator (=), and a logical operator (|) that joins the third expression to the selection string.
If Selection is Not Required
If selection on a table is not required, you can pass an empty value ('') for the selection parameter.
Ordering
The string specified for the ordering parameter is a list of terms consisting of the following:
The ORDERED clause ORDERED DESCENDING LNAME AND ORDERED ASCENDING EMPNO would translate to an ordering parameter like this:
LNAMEßßßßßßßßßßDEMPNOßßßßßßßßßßßA
If Ordering is Not Required
If you do not need ordering, you can pass an empty value ('') for the ordering parameter. In this case, the occurrences are sorted by primary key.
Usage Notes
Using @FORALLA in conjunction with FORALLB and FORALLE, you can dynamically build a selection criterion to retrieve table occurrences.
There are a maximum of 16 tables per transaction that can be actively accessed with @FORALLA. When you finish accessing a table, you should call FORALLE to indicate that you are finished and to free up resources for another table to be accessed.
An @FORALLA within a FORALL on the same table (and with the same parameter values) can result in unexpected behavior because the set of occurrences returned by the outer FORALL is replaced by the occurrence returned by @FORALLA.

Exceptions
 
Example
Using the following rule, QUERY_EMPLOYEES, users can construct a query against the EMPLOYEE table. The rule retrieves employee names based on the selection criteria associated with one field, or with a combination of two fields, and prints them to the message log. Users can execute the rule directly or the rule could be part of a larger application in which input values are entered via a screen.
The rule supports queries such as:
The QUERY_EMPLOYEES Rule
QUERY_EMPLOYEES pads input data to appropriate lengths, constructs a @FORALLA statement using the data from one or two fields, and then uses FORALLA, FORALLE, and MSGLOG to retrieve and display the names of all employees meeting the selection criteria in the @FORALLA statement.

 
RULE EDITOR ===> SCROLL: P
QUERY_EMPLOYEES(REGION, FIELD1, OPER1, VALUE1, ORD1, OPER3, FIELD2, OPER2,
_ VALUE2, ORD2);
_ LOCAL REGLEN, VALLEN1, VALLEN2, FLD1, OP1, FLD2, OP2;
_ ---------------------------------------------------------------------------
_ OPER3 = ''; | Y N
_ ------------------------------------------------------------+--------------
_ REGLEN = PAD(LENGTH(REGION), 3, '0', 'R'); | 1 1
_ VALLEN1 = PAD(LENGTH(VALUE1), 4, '0', 'R'); | 2 2
_ VALLEN2 = PAD(LENGTH(VALUE2), 4, '0', 'R'); | 3
_ FLD1 = PAD(FIELD1, 16, ' ', 'L'); | 3 4
_ FLD2 = PAD(FIELD2, 16, ' ', 'L'); | 5
_ OP1 = PAD(OPER1, 2, ' ', 'R'); | 4 6
_ OP2 = PAD(OPER2, 2, ' ', 'R'); | 7
_ CALL MSGLOG(FIELD1 || ' ' || OPER1 || ' ' || VALUE1 || ':' | 5
_ ); |
_ CALL MSGLOG(FIELD1 || ' ' || OPER1 || ' ' || VALUE1 || ' ' | 8
_ || OPER3 || ' ' || FIELD2 || ' ' || OPER2 || ' ' || |
_ VALUE2 || ':'); |
_ CALL MSGLOG(' '); | 6 9
_ CALL @FORALLA('EMPLOYEE', REGLEN || REGION, ' R' || FLD1 || | 7
_ ' V' || VALLEN1 || VALUE1 || OP1 || ' R' || FLD2 || ' V' |
_ || VALLEN2 || VALUE2 || OP2 || ' ' || OPER3, FLD1 || |
_ ORD1 || FLD2 || ORD2); |
_ UNTIL TABLEEND : | 8 B
_ CALL MSGLOG(EMPLOYEE.LNAME); |
_ CALL FORALLB('EMPLOYEE'); |
_ END; |
_ CALL FORALLE('EMPLOYEE'); | 9 C
_ CALL ENDMSG('PRESS PF2 FOR LIST');   | A D
_ ---------------------------------------------------------------------------
_ ON TABLEEND :
_ CALL FORALLE('EMPLOYEE');
_ CALL ENDMSG('NO MATCHING OCCURRENCES FOUND');
PFKEYS: 1=HELP 3=END 12=CANCEL 13=PRINT 14=EXPAND 2=DOCUMENT 22=DELETE

 
Query
To find those employees who work for manager number 79912 and have a salary of more than $700.00, the following input values are supplied:

 
------------------------ H U R O N RULE EXECUTION -----------------------
ENTER ARGUMENTS FOR RULE QUERY_EMPLOYEES
REGION ===> midwest
FIELD1 ===> mgr#
OPER1 ===> =
VALUE1 ===> 79912
ORD1 ===> a
OPER3 ===> &
FIELD2 ===> salary
OPER2 ===> >
VALUE2 ===> 700
ORD2 ===> a

 
The message log displays the following employee names, arranged in order of ascending salary:

 
----------------------- INFORMATIONAL MESSAGE LOG --------------------
COMMAND ===> SCROLL ===> P
MGR# = 79912 & SALARY > 700:
HRODEK
BOIVIN
SCHULTZ
WONG

 

Copyright © TIBCO Software Inc. All Rights Reserved
Copyright © TIBCO Software Inc. All Rights Reserved