![]() |
Copyright © TIBCO Software Inc. All Rights Reserved |
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)
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 selection criteria. Its syntax can be UN (Unicode) or V with length of 4096. A string specifying the order in which to return the occurrences during selection. Its syntax is V, with a length of 512.
If the format of the arguments for @FORALLA is incorrect a fatal error can occur with no indication of the cause of the error.The name of the table. For example, to access the table EMPLOYEE, pass ‘EMPLOYEE’ as the value for the table argument.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).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:
• A two‑character descriptor consisting of a space followed by an R (' R').
• A 16-character field name. If the name is not 16 characters long, it must be padded to 16 characters with spaces.
• 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
− V – syntax UN or V. @FORALLA uses, or coerces the value to, syntax V; if the coercion to syntax V fails, it uses syntax UN
• A four-character value length descriptor (prefix a length shorter than four digits with zeroes as needed)
The relational operators must be two characters in length. Prefix a space before an operator that consists of only one character.
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 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):If VAL is of syntax V, SEL_STR preserves its syntax; if VAL is of syntax UN, SEL_STR is coerced to syntax UN.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:Even if SEL_STR is of syntax UN prior to the latter action, FORALLA coerces correctly the value to RD.In these examples, the “ß” represents a mandatory space.
• The following represents the selection CITY='TORONTO':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: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.
• ß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 on a table is not required, you can pass an empty value ('') for the selection parameter.
• A 16-character field name. If the field name is less than 16 characters long, it must be padded to 16 characters.The ORDERED clause ORDERED DESCENDING LNAME AND ORDERED ASCENDING EMPNO would translate to an ordering parameter like this:LNAMEßßßßßßßßßßDEMPNOßßßßßßßßßßßAIf 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.
• If the value of every table parameter and of the selection criterion is 99 or fewer characters long, use FORALLA rather than @FORALLA.
• 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.
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.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.
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:
![]() |
Copyright © TIBCO Software Inc. All Rights Reserved |